This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Working with Sequences”.
1. Which column attribute provides unique numbers for identification?
Explanation: In MySQL, the mechanism for providing unique numbers is through the AUTO_INCREMENT column attribute. It enables the generation of sequential numbers automatically. This facilitates identification.
2. There can be only one column per table with the AUTO_INCREMENT attribute.
Explanation: In MySQL, there can only be one column per table with the AUTO_INCREMENT attribute. It should also have an integer data type. The AUTO_INCREMENT is also allowed for floating point types.
3. The AUTO_INCREMENT sequences normally begin at __________
Explanation: The AUTO_INCREMENT column attribute provides unique numbers for column identification. AUTO_INCREMENT sequences normally begin at 1 and increase monotonically like 1, 2, 3, and so on.
4. The value of recently generated sequence number can be obtained by ____________
Explanation: The value of most recently generated sequence number can be obtained by calling the LAST_INSERT_ID() function. This enables to reference the AUTO_INCREMENT value in the subsequent statement.
5. When no AUTO_INCREMENT value has been generated during the current connection, LAST_INSERT_ID() returns ____________
Explanation: The ‘LAST_INSERT_ID()’ function returns zero when no ‘AUTO_INCREMENT’ value has been generated during the current connection with the server. It is tied to the current connection.
6. The ‘LAST_INSERT_ID()’ is tied only to the ‘AUTO_INCREMENT’ values generated during the current connection to the server.
Explanation: The ‘LAST_INSERT_ID()’ is tied only to AUTO_INCREMENT values that are generated during the current connection to the server. It is not affected by AUTO_INCREMENT tied with the other clients.
7. Suppose the last row has the AUTO_INCREMENT column value 32. Suppose a new row is added by setting AUTO_INCREMENT value equal to 100. The next row added will have value _____________
Explanation: If the new value is larger than the current next sequence number, then the sequence is reset to continue with the next value after that for the following rows. In this way, “bumping up” the counter is done.
8. The number of rows in the table is 10. Suppose all rows are deleted. The new row starts with sequence number _____________
Explanation: When the row containing the largest value in an AUTO_INCREMENT column is deleted, that value is reused the next time a new value is generated. In this case the sequence number is 1.
9. In a MyISAM table, if the maximum value of an AUTO_INCREMENT increment column is 12 and that row is deleted, the next value generated is _____________
Explanation: In the MyISAM tables, AUTO_INCREMENT sequences normally are monotonic. The values in an automatically generated series are strictly increasing. They are not reused when rows are deleted.
10. In MyISAM tables, when a table is emptied with the TRUNCATE TABLE, the counter begins at _____________
Explanation: In the MyISAM tables, whenever a table is truncated with the ‘TRUNCATE TABLE’ clause, the ‘AUTO_INCREMENT’ counter again gets reset. It begins with the value one and increases monotonically.
Sanfoundry Global Education & Learning Series – MySQL Database.
To practice all areas of MySQL Database, here is complete set of 1000+ Multiple Choice Questions and Answers.