NOT NULL constraint behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What happens when inserting NULL into a NOT NULL column?

Consider a table Users with a column username defined as VARCHAR(50) NOT NULL. What will be the result of this query?

INSERT INTO Users (username) VALUES (NULL);

SQL

CREATE TABLE Users (id INT PRIMARY KEY, username VARCHAR(50) NOT NULL);
INSERT INTO Users (id, username) VALUES (1, 'Alice');
INSERT INTO Users (username) VALUES (NULL);

A The insertion fails with a NOT NULL constraint violation error.

B The insertion succeeds and stores NULL in the username column.

C The insertion succeeds and stores an empty string ('') in the username column.

D The insertion succeeds but the username column is automatically set to a default value.

💡 Hint

Think about what NOT NULL means for a column's values.

The NOT NULL constraint means the column cannot store NULL values. Attempting to insert NULL causes an error.

❓ query_result

intermediate

What is the output of selecting rows with NULL in a NOT NULL column?

Given a table Products with a price column defined as DECIMAL NOT NULL, what will this query return?

SELECT * FROM Products WHERE price IS NULL;

SQL

CREATE TABLE Products (id INT PRIMARY KEY, price DECIMAL NOT NULL);
INSERT INTO Products VALUES (1, 10.5), (2, 20.0);

A No rows are returned because price cannot be NULL.

B All rows are returned because price IS NULL matches all values.

C An error occurs because price is NOT NULL and cannot be checked for NULL.

D Only rows with price 0 are returned.

💡 Hint

Think about whether NULL values can exist in a NOT NULL column.

Since price is NOT NULL, no rows can have NULL in that column, so the query returns zero rows.

📝 Syntax

advanced

Which table definition correctly enforces NOT NULL on a column?

Which of the following SQL statements correctly creates a table with a NOT NULL constraint on the email column?

A CREATE TABLE Contacts (id INT, email VARCHAR(100) NULL NOT);

B CREATE TABLE Contacts (id INT, email VARCHAR(100) NOT NULL NULL);

C CREATE TABLE Contacts (id INT, email VARCHAR(100) NOT NULL);

D CREATE TABLE Contacts (id INT, email VARCHAR(100) NOTNULL);

💡 Hint

Check the correct syntax for NOT NULL in SQL column definitions.

Option C uses the correct syntax: column_name data_type NOT NULL. Other options have syntax errors.

❓ optimization

advanced

How does NOT NULL constraint affect query performance?

Which statement best describes how a NOT NULL constraint on a column can impact query performance?

A NOT NULL constraints have no impact on query performance.

B NOT NULL constraints slow down queries because the database must check for NULL values.

C NOT NULL constraints cause the database to store extra metadata, reducing performance.

D NOT NULL constraints allow the database to optimize queries by skipping NULL checks, improving performance.

💡 Hint

Think about how knowing a column cannot be NULL helps the database.

Knowing a column cannot be NULL lets the database skip NULL checks during query execution, which can improve performance.

🧠 Conceptual

expert

Why might a NOT NULL constraint be preferred over a default value?

Which reason best explains why a database designer might choose to use a NOT NULL constraint without a default value on a column?

A To allow NULL values but treat them as zero internally.

B To ensure that every row explicitly provides a meaningful value, avoiding hidden defaults.

C To automatically fill missing values with the current timestamp.

D To improve storage efficiency by compressing NULL values.

💡 Hint

Consider the difference between forcing explicit input and silently filling values.

Using NOT NULL without a default forces users to provide a value, ensuring data is intentional and meaningful.