MySQL Questions and Answers – Working with Sequences

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

Answer: a
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

Answer: a
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

Answer: b
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.
advertisement
advertisement

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

Answer: a
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

Answer: b
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.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

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

Answer: a
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

Answer: d
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.
advertisement

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

Answer: b
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

Answer: b
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.
advertisement

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

Answer: b
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.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.