MySQL Questions and Answers – How MySQL Handles Invalid Data Values

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.
a) True
b) False
View Answer

Answer: a
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

Answer: d
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

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.
advertisement
advertisement

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

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’.

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

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.
Note: Join free Sanfoundry classes at Telegram or Youtube

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

Answer: c
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

Answer: b
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.
advertisement

8. Which keyword suppresses errors?
a) SUPPRESS
b) STOP
c) PREVENT
d) IGNORE
View Answer

Answer: d
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

Answer: b
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.
advertisement

10. STRICT_ALL_TABLES turns on stricter checking of data values.
a) True
b) False
View Answer

Answer: a
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.

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.