This set of MySQL Database Multiple Choice Questions & Answers (MCQs) focuses on “Updating MySQL”.
1. Which upgrade involves exporting existing data using mysqldump?
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?
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 _____________
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.
4. A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading.
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
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?
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 _____________
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.
8. The number of values returned from a stored procedure can be _______________
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?
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.
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
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.