Error Handling and Null Logic in SQLite Quiz

Explore fundamental concepts of error management and handling of null values in SQLite databases. This quiz assesses your understanding of key SQL behaviors, common functions, and best practices related to nulls and error scenarios in SQLite systems.

1. Understanding SQLite's handling of NULL values in columns

   When you insert a row into a SQLite table without specifying a value for a particular column that allows NULLs, what value is stored in that column?

   1. Default value of the table
   2. 0
   3. An empty string
   4. NULL

   Explanation: If no value is provided for a column that allows NULLs in SQLite, the value stored is NULL. Zero and empty string are data values but not the same as NULL, which represents unknown or missing information. The default value only applies if a DEFAULT constraint is set and used; otherwise, NULL is used.

2. Detecting NULL values in queries

   Which SQL operator is used to determine if a column contains a NULL value in SQLite?

   1. IS NULL
   2. = NULL
   3. NULL VALUE
   4. == NULL

   Explanation: The IS NULL operator is correctly used to check for NULL values in SQLite. Using = NULL or == NULL does not correctly identify NULLs because standard equality operators cannot be used with NULL. NULL VALUE is not a recognized operator.

3. Effect of NULL in arithmetic expressions

   What is the result of evaluating the SQL expression 5 + NULL in SQLite?

   1. NULL
   2. An error occurs
   3. 0
   4. 5

   Explanation: In SQLite, any arithmetic operation that involves a NULL value results in NULL, because NULL represents an unknown value. The output cannot be 5 or 0, since the value of NULL is indeterminate. This does not cause an error; it simply propagates NULL.

4. Handling division by zero in SQLite

   What is the result of executing SELECT 10 / 0 in SQLite?

   1. 10
   2. 0
   3. An error occurs
   4. NULL

   Explanation: In SQLite, dividing by zero results in NULL rather than causing an error or returning zero. Returning 10 would be incorrect, and although some databases may throw an error, SQLite specifically returns NULL. The output is never 0 unless defined by a custom expression.

5. COALESCE function in SQLite

   What is the primary use of the COALESCE function in SQLite?

   1. Combine text from two columns
   2. Check for errors in queries
   3. Return the first non-NULL value
   4. Count null values in a table

   Explanation: The COALESCE function returns the first non-NULL value from its arguments, which helps handle NULL values efficiently. It does not check for errors, combine text, or count null values, so the other options are not correct functionalities for COALESCE.

6. Comparison operations involving NULL

   What will the expression NULL = NULL return when evaluated in a WHERE clause in SQLite?

   1. FALSE
   2. NULL
   3. TRUE
   4. An error

   Explanation: In SQLite and standard SQL, NULL = NULL evaluates to NULL, not TRUE or FALSE, because NULL means unknown, so SQLite can't determine if two unknowns are equal. This does not result in an error. Choosing TRUE or FALSE is incorrect due to how SQL handles NULL logic.

7. Effect of UNIQUE constraint with NULLs

   If two rows in a SQLite table both have NULL in a column with a UNIQUE constraint, what happens?

   1. A constraint error occurs
   2. Only one row is allowed
   3. Rows are automatically updated
   4. Both rows are valid

   Explanation: In SQLite, NULL values are considered as not comparable, so multiple NULLs are allowed in a column with a UNIQUE constraint. No constraint error occurs since SQL treats NULL as different from all other values. It doesn't update rows or limit the number to one in this scenario.

8. Default error mode in SQLite under constraint violation

   What is the default error response when a NOT NULL constraint is violated during an INSERT in SQLite?

   1. IGNORE
   2. ROLLBACK
   3. ABORT
   4. REPLACE

   Explanation: The default response in SQLite to a constraint violation, such as NOT NULL, is ABORT, which rolls back the current statement and leaves the prior changes intact. IGNORE, REPLACE, and ROLLBACK are other conflict resolution strategies but are not the default.

9. Using IFNULL for null substitution

   Which value does the expression IFNULL(NULL, 'substitute') return in SQLite?

   1. Empty string
   2. Error
   3. NULL
   4. substitute

   Explanation: The IFNULL function returns its second argument if the first is NULL, so 'substitute' is correct. It does not return NULL, cause an error, or return an empty string unless specifically provided in the function call.

10. Understanding NULL in GROUP BY and aggregate functions

    When using the COUNT(column_name) aggregate in SQLite, how are NULL values in that column treated?

    1. They are counted
    2. They are ignored
    3. They cause an error
    4. They are replaced with zero

    Explanation: COUNT(column_name) in SQLite ignores NULL values in the specified column, counting only non-NULL entries. NULLs are not included in the count, they do not cause an error, nor are they replaced with zero automatically. Counting NULLs would require using COUNT(*), not COUNT(column_name).