Database MCQ Set 1
1. The general term for information about databases and the objects in MySQL is ___
a) datum
b) info
c) record
d) metadata
Answer
Answer: d [Reason:] There are various ways in which MySQL facilitates the methods to obtain information about databases and the objects in them. For example, ‘SHOW’ statements and ‘INFORMATION_SCHEMA’.
2. Which statement is used to determine the storage engine for individual tables?
a) SHOW STATUS OF TABLE
b) SHOW STATUS TABLE
c) SHOW TABLE STATUS
d) SHOW DEFAULT STATUS
Answer
Answer: c [Reason:] MySQL provides a method to determine the storage engine for each table with the ‘SHOW TABLE STATUS’ statement. The output of this statement is the name of the storage engine indicator.
3. INFORMATION_SCHEMA is based on the SQL standard.
a) True
b) False
Answer
Answer: a [Reason:] ‘INFORMATION_SCHEMA’ is another way to access information about the databases and its objects (metadata). It is based on the ‘SQL standard’, although some content is MySQL-specific.
4. Which disk data does FILES table in INFORMATION_SCHEMA store?
a) NDB
b) NBD
c) NBK
d) NCD
Answer
Answer: a [Reason:] The ‘INFORMATION_SCHEMA’ is a method to access information about the databases and its objects known as metadata. It has various tables that can be viewed with ‘SHOW’ statement.
5. The information about table index characteristics is stored in which table of INFORMATION_SCHEMA?
a) FILES
b) STATISTICS
c) SCHEMATA
d) VIEWS
Answer
Answer: b [Reason:] The ‘STATISTICS’ table in the ‘INFORMATION_SCHEMA’ stores information about the table index characteristics. ‘INFORMATION_SCHEMA’ is responsible for storing database metadata.
6. Which table in INFORMATION_SCHEMA stores information about storage engines and server plugins?
a) ENGINES, PLUGINS
b) FILES, PLUGINS
c) ENGINES, FILES
d) PLUGINS, STATISTICS
Answer
Answer: a [Reason:] The ‘INFORMATION_SCHEMA’ is a very useful in MySQL. It has many tables each serving its individual purpose. The storage engine information is stored in ENGINES and plugin information in PLUGINS.
7. INFORMATION_SCHEMA is more portable than SHOW statements.
a) True
b) False
Answer
Answer: a [Reason:] The ‘INFORMATION_SCHEMA’ is a method to access information about the databases and its objects known as metadata. Its content is MySQL-specific which makes it more portable.
8. Which table stores information about the threads executing within the server?
a) PROCESS
b) PROCESSLIST
c) LIST
d) THREADSLIST
Answer
Answer: b [Reason:] The table ‘PROCESSLIST’ is responsible for storing all the information about the threads executing within the server. The other tables are not valid tables in the INFORMATION_SCHEMA.
9. The command which lists databases managed by the server is _______
a) mysqld
b) mysqlshow
c) mysqllist
d) mysqldb
Answer
Answer: b [Reason:] The ‘mysqlshow’ command provides almost the similar information as some ‘SHOW’ statements. It also enables to extract database and table information at the command prompt.
10. The option in mysqlshow to show information about indexes in a table is ___
a) –no-data
b) –indexes
c) –keys
d) –flag
Answer
Answer: c [Reason:] The ‘mysqlshow’ command has various flags to provide a lot of options on displaying tables. The ‘–keys’ flag is specifically used to show information about indexes in a table.
Database MCQ Set 2
1. Which operator is used to return value from JSON columns after evaluating the path and unquoting the result?
a) ->
b) ->>
c) <<
d) >>
Answer
Answer: b [Reason:] The operator ‘–>>’ is used to return values from JSON columns after evaluating the path and unquoting the result. It is equivalent to the function ‘JSON_UNQUOTE(JSON_EXTRACT())’.
2. The right shift operator is _______
a) >>
b) <<
c) <
d) >
Answer
Answer: a [Reason:] The right shift operator is ‘>>’. It is used to shift the bits of a number towards the right. The left shift operator is ‘<<‘. It is used to shift the bits of a number to the left.
3. Which operator compares sounds?
a) MATCH SOUNDS
b) CHECK SOUNDS
c) SOUNDS LIKE
d) SOUNDS SIMILAR
Answer
Answer: c [Reason:] The operator ‘SOUNDS LIKE’ is used to compare sound. The ‘RLIKE’ is a synonym for ‘REGEXP’, which is a pattern matching using regular expressions. There are a lot of other operators for use.
4. MySQL automatically converts a date or time value to a number if the value is used in a numeric context.
a) True
b) False
Answer
Answer: a [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 does RTF refer to?
a) Rich Text Format
b) Right Text Format
c) Rich Text Function
d) Right Text Function
Answer
Answer: a [Reason:] RTF (Rich Text Format) is a format developed by Microsoft that is understood by many word processors. MS-Word is one such program. Many others such as ‘OpenOffice’ understand it too.
6. Which function returns an array of row values?
a) fetchrow_array()
b) fetchrow_arrayref()
c) fetch()
d) fetchrow_hashref()
Answer
Answer: a [Reason:] The function ‘fetchrow_array()’ returns an array of row values. ‘fetchrow_arrayref()’ returns a reference to an array of row values. ‘fetch()’ is the same as fetchrow_arrayref().
7. Which function returns a reference to hash of row values?
a) fetchrow_array()
b) fetchrow_arrayref()
c) fetch()
d) fetchrow_hashref()
Answer
Answer: d [Reason:] The function ‘fetchrow_hashref()’ returns reference to hash of row values. ‘fetchrow_arrayref()’ returns a reference to an array of row values. ‘fetch()’ is the same as fetchrow_arrayref().
8. ‘fetchrow_hashref()’ returns reference to hash of row values keyed by what?
a) row name
b) column name
c) table name
d) database name
Answer
Answer: b [Reason:] The function ‘fetchrow_hashref()’ returns reference to hash of row values, keyed by the column name. The function ‘fetchrow_arrayref()’ returns a reference to an array of row values.
9. Which function returns reference to array of row values?
a) fetchrow_array()
b) fetchrow_arrayref()
c) fetch()
d) fetchrow_hashref()
Answer
Answer: b [Reason:] ‘fetchrow_arrayref()’ returns a reference to an array of row values. The function ‘fetchrow_hashref()’ returns reference to hash of row values. ‘fetch()’ is the same as fetchrow_arrayref().
10. The maximum non zero value for FLOAT is ________
a) 3.402823466E+38
b) 3.402823466E+37
c) 3.402823466E+39
d) 3.402823466E+35
Answer
Answer: a [Reason:] In MySQL, each datatype has its own range. Data types give an idea of the kind of values and the ranges that a variable is allowed to store. The maximum non zero value for FLOAT is 3.402823466E+38.
Database MCQ Set 3
1. SELECT select_list FROM table_list WHERE row_constraint GROUP BY grouping_columns; Which of these is not optional?
a) select_list
b) table_list
c) row_constraint
d) grouping_columns
Answer
Answer: a [Reason:] Given above was a basic syntax of the SELECT statement. Everything in the syntax is optional except the ‘select_list’ option. All the others are free to be omitted, and will work fine.
2. In inner join, result is produced by matching rows in one table with rows in another table.
a) True
b) False
Answer
Answer: a [Reason:] The inner join is a form of join in MySQL that is used to combine the result of concatenating the contents of two tables into a new table. In inner join, result is produced by matching rows in one table with rows in another table.
3. The join where all possible row combinations are produced is called ___
a) INNER JOIN
b) OUTER
c) NATURAL
d) CARTESIAN
Answer
Answer: d [Reason:] In ‘cartesian product’, each row of each table is combined with each row in every other table to produce all possible combination. This produces a very large number of rows since the number is the product of rows.
4. The clause that filters JOIN results is called ___
a) WHERE
b) SORT
c) GROUP
d) GROUP BY
Answer
Answer: a [Reason:] Sometimes the result of a join is very large and is not desirable. In these cases, the results can be filtered with the help of the ‘WHERE’ clause which is followed by a set of condition(s).
5. CROSS JOIN and JOIN are similar to ____
a) INNER JOIN
b) NATURAL JOIN
c) OUTER JOIN
d) CARTESIAN JOIN
Answer
Answer: a [Reason:] The joins ‘CROSS JOIN’ and ‘JOIN’ types are exactly similar to the ‘INNER JOIN’. The statements containing ‘INNER JOIN’ can replace it with ‘CROSS JOIN’ or ‘JOIN’ to get exactly the same result.
6. The comma operator can also be used to join tables.
a) True
b) False
Answer
Answer: a [Reason:] The comma (,) operator can be used to join the tables as well. It joins them in the ‘INNER JOIN’ type. It is not desirable to use the comma operator since it has different precedence rules.
7. The left and right joins are also known as ____
a) INNER JOIN
b) NATURAL JOIN
c) OUTER JOIN
d) CARTESIAN JOIN
Answer
Answer: c [Reason:] The ‘inner join’ only deals with rows where a match can be found in both tables. The ‘LEFT JOIN’ and ‘RIGHT JOIN’ types are ‘OUTER JOIN’ types which differ from inner joins in this sense.
8. What is joining a table to itself called?
a) COMPLETE
b) SELF
c) OBSOLETE
d) CROSS
Answer
Answer: b [Reason:] Joining a table to itself in a database is called ‘self-join’. When a self-join is being performed, the table is being used multiple times within the query and a table name qualifier is unnecessary.
9. In which join all the rows from the left table appear in the output irrespective of the content of the other table?
a) RIGHT JOIN
b) LEFT JOIN
c) INNER JOIN
d) OUTER JOIN
Answer
Answer: b [Reason:] In a ‘LEFT JOIN’, the output is produced for every row of the left table, even if it does not exist in the right table. This is the reason it is called a ‘LEFT JOIN’. ‘LEFT JOIN’ is a kind of OUTER JOIN.
10. The join in which all the rows from the right table appear in the output irrespective of the content of the other table is _____
a) CARTESIAN JOIN
b) CROSS JOIN
c) INNER JOIN
d) RIGHT JOIN
Answer
Answer: d [Reason:] In a ‘RIGHT JOIN’, the output is produced for every row of the right table, even if it does not exist in the other table. This is the reason why it is called a ‘RIGHT JOIN’. ‘RIGHT JOIN’ and ‘LEFT JOIN’ are a kind of OUTER JOIN.
Database MCQ Set 4
1. To combine multiple retrievals, we write several SELECT statements and put the keyword between them. What is the keyword?
a) COMBINE
b) CONCAT
c) JOIN
d) UNION
Answer
Answer: d [Reason:] The ‘UNION’ operator is used for combining the results of various ‘SELECT’ queries into one. For example, ‘SELECT a FROM table1 UNION SELECT a FROM table2;’ produces the results from tables table1 concatenated with that of table2.
2. What is ‘xyz’ in the following statement?
SELECT xyz FROM table1 UNION xyz FROM table2;
a) row name
b) column name
c) table name
d) database name
Answer
Answer: b [Reason:] The ‘SELECT’ queries can be combined together using the ‘UNION’ operator to produce the concatenated results from two or more tables. The data type of the columns is not taken into account.
3. The following statement is invalid.
SELECT abc, xyz FROM table1 UNION abc, def FROM table2;
a) True
b) False
Answer
Answer: b [Reason:] Even if the columns ‘xyz’ and ‘def’ have different data types, the results from these columns are placed into the column ‘xyz’. The data types can be determined from the values in the columns.
4. Which keyword used with UNION does not retain duplicate rows?
a) ALL
b) NARROW
c) STRICT
d) DISTINCT
Answer
Answer: d [Reason:] The keyword ‘DISTINCT’ used along with ‘UNION’ is synonymous with just the ‘UNION’ statement. It produces only the distinct rows from the combination of the two tables in the SELECT query.
5. Which keyword used with UNION retains duplicate rows?
a) ALL
b) NARROW
c) STRICT
d) DISTINCT
Answer
Answer: a [Reason:] The keyword ‘ALL’ used along with ‘UNION’ is not synonymous with just the ‘UNION’ statement. It produces the duplicate rows, if they exist, from the combination of the two tables in the SELECT query.
6. The UNION ALL has a higher precedence than UNION DISTINCT.
a) True
b) False
Answer
Answer: b [Reason:] When there is a mixture of ‘UNION’ or ‘UNION DISTINCT’ statements with the ‘UNION ALL’ statements, any distinct union operation takes precedence over any ‘UNION ALL’ operations to its left.
7. Which clause is used to sort a UNION result as a whole?
a) LIMIT
b) ORDER BY
c) GROUP BY
d) SORT
Answer
Answer: b [Reason:] The ‘ORDER BY’ clause is used along with the ‘UNION’ statement to sort a ‘UNION’ result as a whole. It is placed after the last ‘SELECT’ statement which is kept in parentheses.
8. Suppose it is desired that UNION operation should return not more than 3 rows. Which keyword is used for this?
a) LIMIT
b) RESTRICT
c) COUNT
d) SORT
Answer
Answer: a [Reason:] When there is a need to put a limit to the number of rows returned by the ‘UNION’ operation, the statement ‘LIMIT’ is appended to the ‘SELECT’ queries which are joined by the ‘UNION’ operations.
9. Which table is used to run a UNION-type query on MyISAM tables?
a) TRADITIONAL
b) MERGE
c) SERVELET
d) UNITE
Answer
Answer: b [Reason:] When there is a need to run a ‘UNION type’ operation on a MyISAM table that has the same structures, a ‘MERGE’ table is set up for it. After this, the queries are performed on this table.
10. SELECT on a MERGE table is like _______
a) UNION ALL
b) UNION
c) UNION DISTINCT
d) JOIN
Answer
Answer: a [Reason:] Performing a ‘SELECT’ operation on a ‘MERGE’ table is like performing ‘UNION ALL’. This means that duplicate row results are not removed. ‘SELECT DISTINCT’ is like ‘UNION’ or ‘UNION DISTINCT’.
Database MCQ Set 5
1. What is known as the set of SQL statements that either execute successfully or none of them have an effect.
a) joins
b) transactions
c) filters
d) deletions
Answer
Answer: b [Reason:] In MySQL, a transaction is a set of SQL statements that are run as a single unit. They can be canceled when required, but they can either execute successfully or neither of them execute.
2. What is generally done after the transactions are executed successfully?
a) delete
b) rollback
c) commit
d) update
Answer
Answer: c [Reason:] In a transaction, either all the statements are executed successfully or neither of them are successful. This is facilitated by the commit and rollback capabilities. Commit is made after a transaction.
3. What is generally done if an error occurs during the transaction?
a) delete
b) rollback
c) commit
d) update
Answer
Answer: b [Reason:] Whenever an error occurs during a transaction, it is generally taken to the state prior to the beginning of transaction execution. This is know as rollback. It is a set of undo operations.
4. What does ‘A’ stand for in the ACID property of transactions?
a) Availability
b) Accuracy
c) Adjustability
d) Atomicity
Answer
Answer: d [Reason:] All the transaction systems have an important set of characteristics in common. This is known as the ‘ACID’ property of the transaction. It refers to the four elementary characteristics of a transaction.
5. What does ‘C’ stand for in the ACID property of transactions?
a) Compound
b) Concrete
c) Collision
d) Consistency
Answer
Answer: d [Reason:] The elementary characteristics of a transaction are known as the ‘ACID’ properties. ‘ACID’ is the acronym for the four basic characteristics that a transaction must have for smooth processing.
5. What is the isolation property of transactions?
a) statements form a logic unit
b) database remains consistent
c) one transaction does not affect the other
d) transaction effects are recorded permanently
Answer
Answer: c [Reason:] The ‘isolation’ property of a transaction is one of the four elementary characteristics that are expected for a database transaction to maintain throughout. The others are Atomicity, Consistency and Durability.
6. What is the command to disable autocommit and launch a transaction?
a) INITIATE TRANSACTION
b) START TRANSACTION
c) DISABLE AUTOCOMMIT
d) TRANSACTION
Answer
Answer: b [Reason:] By default, MySQL runs in the autocommit mode. This means that the changes performed by individual statements or operations on tables are automatically committed to the database immediately.
7. Transactional processing provides strong guarantees about the outcome of operations.
a) True
b) False
Answer
Answer: a [Reason:] A transaction can either execute successfully or it can be rolled back to the point of initiation. Once a transaction completes, changes are recorded with commit. If it fails, changes are rolled back.
8. Transactional processing requires lesser overhead due to CPU cycles and memory.
a) True
b) False
Answer
Answer: b [Reason:] Transactional processing is a Compound process. The statements and the state of the database need to be kept track of during the execution of the transaction. This means CPU cycles need to be reserved.
9. What is the durability property of transactions?
a) statements form a logic unit
b) database remains consistent
c) one transaction does not affect the other
d) transaction effects are recorded permanently
Answer
Answer: d [Reason:] The ‘durability’ property of a transaction is one of the four elementary characteristics that are expected for a database transaction to maintain throughout. The others are Isolation, Atomicity and Consistency.
10. How many storage engines among the following are transaction-safe?
InnoDB, Falcon, MyISAM, MEMORY
a) 1
b) 2
c) 3
d) 4
Answer
Answer: b [Reason:] For some applications, transactional properties are essential. For example, financial operations usually require transaction processing. InnoDB and Falcon are transaction-safe while the other two are not.
