This set of MySQL online quiz focuses on “How MySQL Handles Invalid Data Values”.
1. By default, MySQL clips out of range numeric values to the nearest fit value.
Explanation: For the numeric or TIME columns, the values that are outside the legal range are clipped to the nearest endpoint of the range. The resulting value is stored. This is the method to handle defaults for numerics.
2. For which type are illegal values converted to the appropriate ‘zero’ value?
Explanation: In MySQL, there are different ways to handle the illegal values for different datatypes for default. For date or time columns, illegal values are converted to the appropriate “zero” value for the type.
3. What is the command to see the warning messages?
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.
4. The SQL mode to check for divide by zero error is ________________
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’.
5. Which mode is used to turn on strict mode and all of the additional restrictions?
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.
6. Which mode is a shorthand for ‘both strict modes plus a bunch of other restrictions’?
Explanation: In MySQL, the ‘TRADITIONAL’ mode is used to enable the strict mode. It also enables all of the additional restrictions by the command ‘SET sql_mod = ‘TRADITIONAL” The other SQL modes have other functions.
7. Which mode prevents MySQL to perform full checking of date parts?
Explanation: In MySQL, it is also possible to selectively weaken the strict mode at some places. If the ALLOW_INVALID_DATES SQL mode is enabled, MySQL doesn’t perform full checking of the date parts.
8. Which keyword suppresses errors?
Explanation: In MySQL, to suppress errors, IGNORE keyword is used with INSERT or UPDATE statements. With the IGNORE clause, statements that would result in an error due to invalid values result only in a warning.
9. Which mode prevents entry of the ‘zero’ date value in strict mode?
Explanation: The ‘NO_ZERO_DATE’ prevents the entry of the ‘zero’ date value in strict mode. In MySQL, to suppress errors, the IGNORE keyword is used with INSERT or UPDATE statements.
10. STRICT_ALL_TABLES turns on stricter checking of data values.
Explanation: The SQL mode ‘STRICT_ALL_TABLES’ turns on the stricter checking of inserted or the updated data values. This can also be done with the ‘STRICT_TRANS_TABLES’ mode for transactionals.
Sanfoundry Global Education & Learning Series – MySQL Database.
To practice all areas of MySQL for online Quizzes, here is complete set of 1000+ Multiple Choice Questions and Answers.