Question

What is the SQL mode to check for divide by zero error?

(a) STRICT_ALL_TABLES

(b) ERROR_FOR_DIVISION_BY_ZERO

(c) ERROR_DIVIDE_BY_ZERO

(d) ERROR_WHEN_DIVIDE_BY_ZERO

I had been asked this question in an interview.

The above asked question is from Updating MySQL in portion General MySQL Administration of MySQL

Answer 1

The correct answer is:

(b) ERROR_FOR_DIVISION_BY_ZERO

Explanation: In MySQL, the SQL mode ERROR_FOR_DIVISION_BY_ZERO is used to throw an error when a division by zero occurs. This mode ensures that any query that attempts to divide by zero will result in an error, rather than returning NULL or continuing with the execution of the query.

• STRICT_ALL_TABLES (option a) enforces strict SQL mode and causes errors on invalid or missing values in insert statements, but it doesn't specifically deal with division by zero errors.
• ERROR_DIVIDE_BY_ZERO (option c) and ERROR_WHEN_DIVIDE_BY_ZERO (option d) are not valid SQL modes in MySQL.

When ERROR_FOR_DIVISION_BY_ZERO is enabled, a division by zero operation will trigger an error, helping to prevent unintended results in queries.