1. By default, MySQL clips out of range numeric values to the nearest fit value.
a) True
b) False
Answer
Answer: a [Reason:] 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?
a) Numeric
b) String
c) ENUM
d) TIME
Answer
Answer: d [Reason:] 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
Answer
Answer: d [Reason:] 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 ____
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) ERROR_DIVIDE_BY_ZERO
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer
Answer: b [Reason:] 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?
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer
Answer: c [Reason:] 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’?
a) STRICT_ALL_TABLES
b) STRICT_TRANS_TABLES
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer
Answer: c [Reason:] 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?
a) ALLOW_DATES_INVALID
b) ALLOW_INVALID_DATES
c) PREVENT_DATE_CHECK
d) STOP_DATES_CHECK
Answer
Answer: b [Reason:] 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?
a) SUPPRESS
b) STOP
c) PREVENT
d) IGNORE
Answer
Answer: d [Reason:] 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?
a) SUPPRESS
b) NO_ZERO_DATE
c) PREVENT
d) NO_ZERO_IN_DATE
Answer
Answer: b [Reason:] 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.
a) True
b) False
Answer
Answer: a [Reason:] 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.
