This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Working with Sequences”.
1. Which column attribute provides unique numbers for identification?
a) AUTO_INCREMENT
b) UNSIGNED
c) IDENTIFY
d) DESCRIBE
View Answer
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.
a) True
b) False
View Answer
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 __________
a) 0
b) 1
c) -1
d) 2
View Answer
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 ____________
a) LAST_INSERT_ID()
b) LATEST_INSERT_ID()
c) INITIAL_INSERT_ID()
d) INSERT_ID()
View Answer
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 ____________
a) -1
b) 0
c) 1
d) 2
View Answer
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.
a) True
b) False
View Answer
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 _____________
a) 32
b) 33
c) 100
d) 101
View Answer
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 _____________
a) 11
b) 1
c) 100
d) 101
View Answer
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 _____________
a) 12
b) 13
c) 1
d) 14
View Answer
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 _____________
a) 0
b) 1
c) -1
d) arbitrary
View Answer
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.
- Apply for Programming Internship
- Practice Programming MCQs
- Check Information Technology Books
- Check MySQL Books