MySQL Questions and Answers – Updating MySQL

This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Updating MySQL”.

1. Which upgrade involves exporting existing data using mysqldump?
a) inplace
b) logical
c) illogical
d) system
View Answer

Answer: b
Explanation: The in-place upgrade involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.

2. Which upgrade involves shutting down the old MySQL version and replacing the old binaries?
a) inplace
b) logical
c) illogical
d) system
View Answer

Answer: a
Explanation: The logical upgrade involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version.

3. Slow shutdown can be performed by setting innodb_fast_shutdown to _____________
a) 0
b) -1
c) 1
d) 2
View Answer

Answer: a
Explanation: With a slow shutdown InnoDB performs a full purge and change buffer merge before shutting down. This ensures that data files are fully prepared in case of file format differences.
advertisement
advertisement

4. A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading.
a) True
b) False
View Answer

Answer: a
Explanation: A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table can damage the table.

5. The command to see the warning messages is ______________
a) DISPLAY WARNINGS
b) DISP WARNINGS
c) DISP WARNING
d) SHOW WARNINGS
View Answer

Answer: d
Explanation: In MySQL, the default value conversions are reported as warnings for INSERT, REPLACE and UPDATE statements. The SHOW WARNINGS command is used after executing one of those statements to see the warning messages.

6. What is the SQL mode to check for divide by zero error?
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) ERROR_DIVIDE_BY_ZERO
d) ERROR_WHEN_DIVIDE_BY_ZERO
View Answer

Answer: b
Explanation: To enable the check for divide by zero errors int all the storage engines, the SQL mode named ‘ERROR_FOR_DIVISION_BY_ZERO’ can be enabled. This is done by using SET sql_mode = ‘mode_name’.

7. The mode used to turn on strict mode and all of the additional restrictions is _____________
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
View Answer

Answer: c
Explanation: The ‘TRADITIONAL’ mode is used to enable the strict mode and all of the additional restrictions. It is done by the command SET sql_mod = ‘TRADITIONAL’; The other SQL modes have other functions.
advertisement

8. The number of values returned from a stored procedure can be _______________
a) 0
b) 1
c) 2
d) 3
View Answer

Answer: a
Explanation: In MySQL, unlike the stored functions, the stored procedures cannot return values. They can be used to perform calculations or produce the result sets passed back to the clients.

9. How would a stored function named PI() written in the database ‘sampdb’ be called?
a) PI()
b) sampdb.PI()
c) MySQL.PI()
d) db.PI()
View Answer

Answer: b
Explanation: When a stored function is defined with the same name as a built in function, the function name should be qualified with the database name when it is invoked. The function ‘PI()’ is built in.
advertisement

10. The privilege that must be given to the database to create a stored function or procedure is ____________
a) CREATE ROUTINE
b) CREATE METHOD
c) CREATE FUNCTION
d) CREATE PROCEDURE
View Answer

Answer: a
Explanation: The ‘CREATE ROUTINE’ privilege must be given for the database in order to create a stored function or procedure. Stored functions and procedures always belong to a particular database.

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.