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.

Practice

1. What does the NOT NULL constraint do in a database table?

easy

A. It allows a column to have duplicate values.

B. It ensures that a column cannot have empty or missing values.

C. It automatically fills the column with default values.

D. It deletes rows with null values in any column.

Solution

1. Step 1: Understand the purpose of NOT NULL

   The NOT NULL constraint prevents a column from having NULL (empty) values, ensuring data completeness.

2. Step 2: Compare options with the definition

   Only the description 'It ensures that a column cannot have empty or missing values.' correctly describes this behavior; others describe different constraints or actions.

3. Final Answer:

   It ensures that a column cannot have empty or missing values. -> Option B

4. Quick Check:

   NOT NULL = no empty values [OK]

Hint: NOT NULL means no empty values allowed in that column [OK]

Common Mistakes:

• Thinking NOT NULL allows duplicates
• Confusing NOT NULL with default values
• Assuming NOT NULL deletes rows

2. Which of the following is the correct syntax to add a NOT NULL constraint to a column named email in a table users?

easy

A. ALTER TABLE users MODIFY email NOT NULL;

B. ALTER TABLE users ADD email NOT NULL;

C. ALTER TABLE users SET email NOT NULL;

D. ALTER TABLE users CHANGE email TO NOT NULL;

Solution

1. Step 1: Recall ALTER TABLE syntax for modifying column constraints

   To add NOT NULL, we use ALTER TABLE with MODIFY (MySQL syntax).

2. Step 2: Evaluate each option's syntax

   ALTER TABLE users MODIFY email NOT NULL; uses correct syntax; others use invalid or incorrect commands.

3. Final Answer:

   ALTER TABLE users MODIFY email NOT NULL; -> Option A

4. Quick Check:

   ALTER TABLE + MODIFY + NOT NULL = correct syntax [OK]

Hint: Use ALTER TABLE with MODIFY to change NOT NULL [OK]

Common Mistakes:

• Using ADD instead of MODIFY for existing columns
• Using SET or CHANGE incorrectly
• Missing semicolon or wrong keywords

3. Given the table products with a price column defined as DECIMAL NOT NULL, what happens when you run this query?

INSERT INTO products (name, price) VALUES ('Pen', NULL);

medium

A. The query fails with an error due to NOT NULL violation.

B. The row is inserted with price set to 0.

C. The row is inserted with price set to NULL anyway.

D. The query succeeds but price is ignored.

Solution

1. Step 1: Understand NOT NULL effect on insert

   NOT NULL means the column cannot accept NULL values during insert or update.

2. Step 2: Analyze the insert statement

   Inserting NULL into a NOT NULL column causes the database to reject the query with an error.

3. Final Answer:

   The query fails with an error due to NOT NULL violation. -> Option A

4. Quick Check:

   Inserting NULL into NOT NULL column = error [OK]

Hint: Inserting NULL into NOT NULL column causes error [OK]

Common Mistakes:

• Assuming NULL becomes 0 automatically
• Thinking NULL is allowed silently
• Believing the query will partially succeed

4. You have this table creation statement:

CREATE TABLE employees (id INT, name VARCHAR(50) NOT NULL);

You try to insert:

INSERT INTO employees (id, name) VALUES (1, NULL);

But it gives an error. What is the fix?

medium

A. Add DEFAULT NULL to the name column.

B. Change the id column to NOT NULL.

C. Remove NOT NULL from the name column.

D. Insert a non-NULL value for name.

Solution

1. Step 1: Identify cause of error

   The name column is NOT NULL, so inserting NULL causes an error.

2. Step 2: Find correct fix

   To fix, insert a valid non-NULL value for name; changing other columns or constraints won't help here.

3. Final Answer:

   Insert a non-NULL value for name. -> Option D

4. Quick Check:

   NOT NULL column must get non-NULL value [OK]

Hint: Insert real value for NOT NULL columns to avoid errors [OK]

Common Mistakes:

• Removing NOT NULL without reason
• Changing unrelated columns
• Adding DEFAULT NULL which conflicts with NOT NULL

5. You want to ensure that the username column in your accounts table always has a value and cannot be empty. Which of these combined approaches is best to enforce this?

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  username VARCHAR(30) NOT NULL CHECK (username <> '')
);

hard

A. Use NOT NULL only, because it blocks empty strings too.

B. Use CHECK only, because NOT NULL allows empty strings.

C. Use both NOT NULL and CHECK to block NULL and empty strings.

D. No constraints needed; application code will handle it.

Solution

1. Step 1: Understand NOT NULL and empty strings

   NOT NULL prevents NULL values but allows empty strings ('').

2. Step 2: Understand CHECK constraint role

   CHECK (username <> '') prevents empty strings but allows NULL unless combined with NOT NULL.

3. Step 3: Combine constraints for full validation

   Using both NOT NULL and CHECK ensures username is neither NULL nor empty.

4. Final Answer:

   Use both NOT NULL and CHECK to block NULL and empty strings. -> Option C

5. Quick Check:

   NOT NULL + CHECK = no NULL or empty [OK]

Hint: Combine NOT NULL and CHECK to block NULL and empty strings [OK]

Common Mistakes:

• Assuming NOT NULL blocks empty strings
• Using CHECK without NOT NULL allows NULLs
• Relying only on application code