This set of MySQL Multiple Choice Questions & Answers (MCQs) focuses on “How MySQL Handles Invalid Data Values”.
1. By default, MySQL clips out of range numeric values to the nearest fit value.
a) True
b) False
View Answer
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?
a) Numeric
b) String
c) ENUM
d) TIME
View Answer
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
View Answer
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 ________________
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) ERROR_DIVIDE_BY_ZERO
d) ERROR_WHEN_DIVIDE_BY_ZERO
View Answer
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?
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
View Answer
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’?
a) STRICT_ALL_TABLES
b) STRICT_TRANS_TABLES
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
View Answer
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?
a) ALLOW_DATES_INVALID
b) ALLOW_INVALID_DATES
c) PREVENT_DATE_CHECK
d) STOP_DATES_CHECK
View Answer
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?
a) SUPPRESS
b) STOP
c) PREVENT
d) IGNORE
View Answer
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?
a) SUPPRESS
b) NO_ZERO_DATE
c) PREVENT
d) NO_ZERO_IN_DATE
View Answer
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.
a) True
b) False
View Answer
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, here is complete set of 1000+ Multiple Choice Questions and Answers.
- Practice Programming MCQs
- Check Information Technology Books
- Apply for Programming Internship
- Check MySQL Books
