Database MCQ Set 1
1. Which classes does spatial data types in MySQL correspond to?
a) OpenGSS
b) OpenGIS
c) ClosedGSS
d) ClosedGIS
Answer
Answer: b [Reason:] MySQL has spatial data types that correspond to OpenGIS classes. Some of them that hold single geometry values are ‘GEOMETRY’, ‘POINT’, ‘LINESTRING’ and ‘POLYGON’. Some can hold collection of values.
2. Which type can store a collection of objects of any type?
a) GEOMETRYCOLLECTION
b) MULTIPOINT
c) MULTILINESTRING
d) MULTIPOLYGON
Answer
Answer: a [Reason:] ‘GEOMETRYCOLLECTION’ can store a collection of objects of any type. The other collection types like ‘MULTIPOINT’, ‘MULTILINESTRING’, ‘MULTIPOLYGON’ and ‘GEOMETRYCOLLECTION’ restrict collection members to those having a particular geometry type.
3. SPATIAL indexes cannot be created on NOT NULL spatial columns.
a) True
b) False
Answer
Answer: b [Reason:] The ‘SPATIAL’ indexes can be created on ‘NOT NULL’ spatial columns. Therefore, if it is planned to index the column, it would need to be declared as NOT NULL. This type can be used in many ways.
4. MySQL does not automatically convert a date or time value to a number if the value is used in a numeric context.
a) True
b) False
Answer
Answer: b [Reason:] MySQL automatically converts a date or time value into a number if the value is used in a numeric context and vice versa. A ‘zero’ value can also be represented in MySQL.
5. What is the generic handle whose meaning depends on context?
a) $dbh
b) $sth
c) $fh
d) $h
Answer
Answer: d [Reason:] The variable named ‘$fh’ is a handle to an open file. ‘$h’ is a generic handle and the meaning depends on context. ‘$dbh’ is a handle to a database object. ‘$sth’ is a handle to a query object.
6. What is the non handle array representing a row of values?
a) $rc
b) $rv
c) $rows
d) $ary
Answer
Answer: d [Reason:] The Perl Non-handle variable ‘$ary’ is an array or list representing a row of values returned by a query. ‘$rc’ returns code from operations that return true or false. ‘$rv’ returns value from operations that return an integer. ‘$rows’ returns value from operations that return a row count.
7. What is the return value from operations returning a row count?
a) $rc
b) $rv
c) $rows
d) $ary
Answer
Answer: c [Reason:] The Perl Non-handle variable ‘$ary’ is an array or list representing a row of values returned by a query. ‘$rc’ returns code from operations that return true or false. ‘$rv’ returns value from operations that return an integer. ‘$rows’ returns value from operations that return a row count.
8. While using DBI API functions are called and pointers to structures are used.
a) True
b) False
Answer
Answer: a [Reason:] While using the DBI API functions are called and pointers to structures are used. The functions are called “methods,” pointers are called “references,” pointer variables are called “handles”.
9. The mode of search is the search string parsed into words and the search looks for rows is ________
a) Boolean mode
b) Natural language
c) Query expansion
d) Cross mode
Answer
Answer: b [Reason:] In MySQL, a full text search capability is provided, which enables to look for words or phrases without using pattern-matching operations. There are three kinds of full text searches.
10. In which mode is the indicator of presence/absence of a word in search used?
a) Natural language
b) Boolean mode
c) Query expansion
d) Cross mode
Answer
Answer: b [Reason:] A full text search capability is provided in MySQL. It facilitates to look for words or phrases without using pattern-matching operations. Boolean search is one of the three modes.
Database MCQ Set 2
1. Which statement upgrades the database directory name encoding?
a) ALTER DATABASE
b) ALTER SERVER
c) ALTER EVENT
d) ALTER FUNCTION
Answer
Answer: a [Reason:] The statement ‘ALTER DATABASE’ changes database attributes or upgrades the database directory name encoding. It requires the ‘ALTER’ privilege for the database to be given.
2. The statement that alters an existing event to have the given definition is _______
a) ALTER EVENT
b) ALTER DATABASE
c) ALTER FUNCTION
d) ALTER DATABASE
Answer
Answer: a [Reason:] The statement ‘ALTER EVENT’ alters an existing event to have the given definition. The ‘RENAME TO’ clause renames the event. The other clauses are described in the entry for ‘CREATE EVENT’.
3. What sets up an association between one or more MyISAM tables and the named key cache?
a) BEGIN
b) CACHE INDEX
c) ALTER DATABASE
d) ALTER EVENT
Answer
Answer: b [Reason:] ‘CACHE INDEX’ sets up an association between one or more MyISAM tables and the named key cache which must already exist. The INDEX privilege is needed for each table named in the statement.
4. Which statement can produce the same output as ‘SHOW COLUMNS’?
a) DESCRIBE
b) DISPLAY
c) SEE
d) GET
Answer
Answer: a [Reason:] The ‘DESCRIBE’ statement with a table name or view name produces the same kind of output as the ‘SHOW COLUMNS’ statement. The ‘SHOW’ entry can be referred to for further information.
5. Converting a client/server application to embedded server is difficult.
a) True
b) False
Answer
Answer: a [Reason:] Writing an application that uses the embedded server is different from writing one that operates in a client/server context. A program that is written as a client/server application can easily be converted to use the embedded server.
6. The option that executes all SQL statements in a SQL script irrespective of the number of errors is ___
a) –run
b) –ensure
c) –force
d) –violent
Answer
Answer: c [Reason:] If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
7. The clause that is used to display information that matches a given pattern is _______
a) WHERE
b) IS
c) SAME
d) LIKE
Answer
Answer: d [Reason:] The ‘LIKE’ clause filters information that match a given pattern. ‘WHERE’ clause selects information that is specified by a condition. ‘IS’ is used to match the exact condition specified.
8. The library file that contains various portability macros and definitions is ________
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: b [Reason:] The ‘my_sys.h’ header file contains a variety of portability macros and definitions required for structures and functions. These structures and functions are used by the client library.
9. The header that should be included first is ____
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: a [Reason:] The file ‘my_global.h’ takes care of including several other header files that are likely to be generally useful, like ‘stdio.h’. It also includes Windows compatibility information.
10. What does mysql_init() return?
a) integer
b) float
c) structure
d) pointer to a structure
Answer
Answer: d [Reason:] When NULL is passed to mysql_init() it automatically allocates a MYSQL structure, initializes it, and returns a pointer to it. The MYSQL data type is a structure containing information about a connection.
Database MCQ Set 3
1. The statement that views status variables by aggregating the values over all connections is _______
a) SHOW SESSION STATUS
b) SHOW LOCAL STATUS
c) SHOW GLOBAL STATUS
d) SHOW STATUS
Answer
Answer: c [Reason:] The MySQL server maintains various status variables that provide information about its operations. These variables and their values can be viewed by using the SHOW [GLOBAL | SESSION] STATUS statement.
2. Which keyword inserted in the SHOW STATUS statement shows the values for the current connection?
a) GLOBAL
b) SESSION
c) LOCAL
d) DEFAULT
Answer
Answer: b [Reason:] The optional ‘GLOBAL’ keyword in the statement SHOW [GLOBAL | SESSION] STATUS statement aggregates the values over all connections and ‘SESSION’ shows the values for the current connection.
3. What is the synonym for last_insert_id session variable?
a) insert_id
b) identity
c) sql_auto_is_null
d) sql_big_selects
Answer
Answer: b [Reason:] The session only system variable ‘identity’ is a synonym for the ‘last_insert_id’ session variable. Setting ‘last_insert_id’ specifies the value to be returned by the function ‘LAST_INSERT_ID()’.
4. The Audit_log_events system variable is of type _______
a) string
b) integer
c) float
d) double
Answer
Answer: b [Reason:] The system variable ‘Audit_log_events’ is of type integer. The variable scope if GLOBAL, that is, it can be viewed by issuing the statement ‘SHOW GLOBAL STATUS’, instead of ‘SESSION’.
5. mysql_next_result() does not return a status.
a) True
b) False
Answer
Answer: b [Reason:] The function ‘mysql_next_result()’ returns a status and initiates retrieval of the next set if more results are available. The status is zero if more results are available and -1 if not.
6. The option that executes all SQL statements in a SQL script irrespective of the number of errors is ______
a) –ensure
b) –force
c) –violent
d) –run
Answer
Answer: b [Reason:] If SQL queries in a file are run using mysql in batch mode, mysql either quits after the first error. If the –force option is specified all the queries are executed indiscriminately.
7. What is the number of attributes in the following table?
CREATE TABLE employee ( emp_name CHAR(30), emp_id INT );
a) 1
b) 2
c) 3
d) 30
Answer
Answer: b [Reason:] The name of the table created is ’employee’. It has two attributes, namely, ’emp_name’ and ’emp_id’. The attributes are the columns in a table. emp_name is of type string and emp_id is of type integer.
8. How many of the following do not return rows?
SELECT, SHOW, DESCRIBE
a) 0
b) 1
c) 2
d) 3
Answer
Answer: a [Reason:] In MySQL, it is important to note that ‘SELECT’ is not the only statement that returns some rows. Statements like ‘SHOW’, ‘DESCRIBE’, ‘EXPLAIN’ and ‘CHECK TABLE’ do so as well.
9. What does mysql_fetch_row() return?
a) integer
b) float
c) structure
d) pointer
Answer
Answer: d [Reason:] ‘mysql_fetch_row()’ returns a MYSQL_ROW value, a pointer to an array of values. If the return value is assigned to a variable named row each value within the row is accessed as row[i].
10. The columns containing binary value that include null bytes will print properly using the %s printf() format specifier.
a) True
b) False
Answer
Answer: b [Reason:] The columns containing binary value including null bytes do not print properly using the %s printf() format specifier. printf() expects a null terminated string. It prints the column value only up to the first null byte.
Database MCQ Set 4
1. For which of these storage engines are configuration options always built?
a) Falcon
b) FEDERATED
c) InnoDB
d) MyISAM
Answer
Answer: d [Reason:] The configuration options for the storage engine ‘MyISAM’ are always built. The runtime options for MyISAM are also always enabled. For the others, runtime options are explicitly enabled.
2. The storage engine for which the runtime options are always enabled is ________
a) Falcon
b) FEDERATED
c) InnoDB
d) MEMORY
Answer
Answer: d [Reason:] The configuration options for the storage engine ‘MEMORY’ are always built. The runtime options for ‘MEMORY’ are also always enabled. For the others, runtime options are explicitly enabled byt specifications.
3. The most important configurable resource for MyISAM is ________
a) key cache
b) memory cache
c) time cache
d) speed cache
Answer
Answer: a [Reason:] For the index processing, ‘MyISAM’ manages its own key cache, which is the most important configurable resource for the MyISAM storage engine. It is used for index based retrievals and sorts.
4. Which system variable enables mysqld to keep more tables open simultaneously?
a) table_cache
b) max_connect
c) delayed_queue_size
d) max_allowed_packet
Answer
Answer: a [Reason:] The ‘table_cache’ stores the size of the table cache. Increasing the value enables ‘mysqld’ to keep more tables open simultaneously by reducing the number of file open and close operations.
5. xyz in the following statement is ____
CREATE VIEW xyz AS SELECT a, b FROM t;
a) table
b) column
c) view
d) database
Answer
Answer: c [Reason:] A ‘View’ is created in MySQL by writing the clause ‘CREATE VIEW’ followed by the view name. Then follows the list of column names of the table that are to be presented as the view.
6. abc in the following statement is ______
CREATE VIEW xyz (abc) AS SELECT a FROM t;
a) row name
b) column name
c) view
d) database
Answer
Answer: b [Reason:] Column names for a view can be explicitly provided in the ‘CREATE VIEW’ clause itself. It presents the table view with the aliased column names instead of the original column names.
7. How can a view refer to multiple tables?
a) UNION
b) JOIN
c) GROUP
d) SELECT
Answer
Answer: b [Reason:] In MySQL, a ‘View’ can refer to multiple tables. This makes it a lot simpler to execute the queries involving joins. When a selection is made from the view, the join is executed producing the results.
8. What is the mantissa in -1.58E5?
a) -1.58
b) 1.58
c) E
d) 5
Answer
Answer: a [Reason:] In MySQL, the scientific notation for real numbers is possible. Some values are represented as floating point numbers in scientific notation consisting of a mantissa and exponent.
9. What is the precision of BIGINT?
a) 32
b) 64
c) 128
d) 16
Answer
Answer: b [Reason:] In MySQL, for the expressions containing only exact values that are all integers, the evaluation uses BIGINT (64 – bit) precision. MySQL evaluates expressions using exact/approximate math.
10. How many digits is the DECIMAL used for expressions containing only exact values with fractional part?
a) 32
b) 64
c) 65
d) 16
Answer
Answer: c [Reason:] In MySQL, for the expressions containing only exact values and where one or more values have a fractional part, the DECIMAL arithmetic is used with digits of precision equal to 65.
Database MCQ Set 5
1. The maximum value that can be specified to the size of VARCHAR is _______
a) 0
b) 127
c) 1023
d) 65535
Answer
Answer: d [Reason:] The values in ‘VARCHAR’ columns are variable length strings. The length can be a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size.
2. The storage in bytes required for VARCHAR(4) type ‘abcd’ is _______
a) 1
b) 3
c) 5
d) 8
Answer
Answer: c [Reason:] The ‘VARCHAR’ values are not padded when they are stored. The trailing spaces are retained when values are stored and retrieved in conformance with standard SQL. The given size is 5 bytes.
3. Which mode does not remove trailing spaces when CHAR values are retrieved?
a) PAD_CHAR_TO_FULL_LENGTH
b) TO_FULL_LENGTH_CHAR_PAD
c) CHAR_PAD_TO_FULL_LENGTH
d) PAD_CHAR_TO_LENGTH
Answer
Answer: a [Reason:] When the CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the SQL mode ‘PAD_CHAR_TO_FULL_LENGTH’ is enabled.
4. What is the minimum value stored by signed TINYINT?
a) -256
b) -128
c) 0
d) 128
Answer
Answer: b [Reason:] MySQL supports the SQL standard integer types INTEGER, or INT, and SMALLINT. As an extension to this standard, MySQL also supports the integer types TINYINT, MEDIUMINT and BIGINT.
5. For InnoDB tables in mysqldump an online backup that takes no locks on tables can be performed by which option?
a) –multiple-transaction
b) –single-transaction
c) –double-transaction
d) –no-transaction
Answer
Answer: b [Reason:] For InnoDB tables it is possible to perform an online backup that takes no locks on tables using the option ‘–single-transaction’ to ‘mysqldump’. The ‘mysqldump’ can make backups.
6. What is used to reload a delimited text data file?
a) mysqldump
b) mysqld
c) mysqlimport
d) mysqlnaive
Answer
Answer: c [Reason:] A way to create text data files along with files containing ‘CREATE TABLE’ statements for the backed up tables is to use ‘mysqldump’ with –tab. To reload a delimited text data file ‘mysqlimport’ is used.
7. Replication does not enable data from one MySQL database server to be copied to one or more MySQL database servers.
a) True
b) False
Answer
Answer: b [Reason:] Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default.
8. What is SBR replication?
a) Statement based
b) Row based
c) Column based
d) Table based
Answer
Answer: a [Reason:] There are two main kinds of replication format: Statement Based Replication (SBR) replicates entire SQL statements and Row Based Replication (RBR) replicates only the changed rows.
9. Which is the library file that contains various portability macros and definitions?
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: b [Reason:] The ‘my_sys.h’ header file contains a variety of portability macros and definitions required for structures and functions. These structures and functions are used by the client library.
10. Which is the header that should be included first?
a) my_global.h
b) my_sys.h
c) mysql.h
d) my_local.h
Answer
Answer: a [Reason:] The file ‘my_global.h’ takes care of including several other header files that are likely to be generally useful, like ‘stdio.h’. It also includes Windows compatibility information.
