DEFAULT values in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INSERT with DEFAULT values?

Consider a table users with columns id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Guest', and age INT DEFAULT 18. What will be the result of this query?

INSERT INTO users (id) VALUES (1);
SELECT * FROM users WHERE id = 1;

SQL

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Guest', age INT DEFAULT 18);
INSERT INTO users (id) VALUES (1);
SELECT * FROM users WHERE id = 1;

A (1, 'Guest', 18)

B (1, NULL, NULL)

C (1, '', 0)

D Error: Missing values for name and age

💡 Hint

If a column has a DEFAULT value and you don't provide a value, the default is used.

When inserting a row without specifying columns that have DEFAULT values, the database fills those columns with their default values automatically.

🧠 Conceptual

intermediate

Which statement about DEFAULT values is true?

Choose the correct statement about DEFAULT values in SQL columns.

A DEFAULT values are used only when explicitly specified in the INSERT statement.

B DEFAULT values are ignored if NULL is inserted explicitly into the column.

C DEFAULT values can only be numeric constants.

D DEFAULT values are applied when the column is omitted in the INSERT statement.

💡 Hint

Think about what happens when you don't mention a column in an INSERT.

If a column is omitted in an INSERT, the database uses its DEFAULT value if defined. Explicit NULL insertion overrides the default.

📝 Syntax

advanced

Which CREATE TABLE statement correctly sets a DEFAULT value for a timestamp column?

You want to create a table events with a created_at column that defaults to the current timestamp. Which option is correct?

A CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW());

B CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

C CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP');

D CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT current_timestamp());

💡 Hint

Look for the correct syntax to use a function as a default value without parentheses.

The correct syntax uses DEFAULT CURRENT_TIMESTAMP without parentheses or quotes. NOW() is a function call and not allowed as default in standard SQL.

❓ optimization

advanced

How can DEFAULT values improve INSERT performance?

Which of the following explains how using DEFAULT values can optimize database INSERT operations?

A DEFAULT values require explicit values in every INSERT, increasing data size.

B DEFAULT values force the database to compute values on every INSERT, slowing performance.

C DEFAULT values reduce the amount of data sent in INSERT statements by omitting columns with defaults.

D DEFAULT values cause the database to lock the table during INSERTs.

💡 Hint

Think about how omitting columns with defaults affects the size of the INSERT statement.

By omitting columns that have DEFAULT values, INSERT statements are smaller and simpler, which can improve performance.

🔧 Debug

expert

Why does this INSERT fail despite DEFAULT values?

Given this table:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Unknown', price DECIMAL(10,2) NOT NULL);

Why does this INSERT fail?

INSERT INTO products (id) VALUES (10);

A Because price has no DEFAULT and is NOT NULL, so it must be provided.

B Because DEFAULT values cannot be used with DECIMAL columns.

C Because id is missing in the INSERT statement.

D Because name DEFAULT value is invalid for VARCHAR columns.

💡 Hint

Check which columns require values and which have defaults.

The price column has no DEFAULT and is NOT NULL, so it must be given a value in the INSERT.

Practice

1. What does the DEFAULT keyword do in a SQL table column definition?

easy

A. Deletes the column if no value is provided

B. Prevents any value from being inserted into the column

C. Sets a value automatically if no value is provided during insert

D. Requires the user to always provide a value

Solution

1. Step 1: Understand the purpose of DEFAULT

   The DEFAULT keyword assigns a preset value to a column when no value is given during data insertion.

2. Step 2: Compare options with this purpose

   Only Sets a value automatically if no value is provided during insert correctly describes this automatic assignment behavior.

3. Final Answer:

   Sets a value automatically if no value is provided during insert -> Option C

4. Quick Check:

   DEFAULT = automatic value assignment [OK]

Hint: DEFAULT fills missing values automatically [OK]

Common Mistakes:

• Thinking DEFAULT deletes or restricts data
• Confusing DEFAULT with NOT NULL constraint
• Assuming DEFAULT requires manual input

2. Which of the following is the correct syntax to set a default value of 100 for a column named score in a table?

easy

A. score INT DEFAULT 100

B. score INT SET DEFAULT 100

C. score INT DEFAULT = 100

D. score INT DEFAULT TO 100

Solution

1. Step 1: Recall standard SQL syntax for DEFAULT

   The correct syntax is: column_name data_type DEFAULT value, without extra keywords or symbols.

2. Step 2: Check each option

   score INT DEFAULT 100 matches the correct syntax exactly. Options B, C, and D add incorrect keywords or symbols.

3. Final Answer:

   score INT DEFAULT 100 -> Option A

4. Quick Check:

   DEFAULT syntax = column type DEFAULT value [OK]

Hint: DEFAULT syntax: column type DEFAULT value [OK]

Common Mistakes:

• Adding extra keywords like SET or TO
• Using equals sign (=) after DEFAULT
• Placing DEFAULT before data type

3. Given the table users with column status VARCHAR(10) DEFAULT 'active', what will be the result of this query?

INSERT INTO users (id, name) VALUES (1, 'Alice');
SELECT id, name, status FROM users WHERE id = 1;

medium

A. [1, 'Alice', NULL]

B. [1, 'Alice', 'active']

C. [1, 'Alice', 'inactive']

D. Syntax error

Solution

1. Step 1: Understand DEFAULT behavior on insert

   Since status has a default of 'active' and no value was provided, SQL inserts 'active' automatically.

2. Step 2: Check the SELECT output

   The SELECT query will show id=1, name='Alice', and status='active' as inserted or defaulted.

3. Final Answer:

   [1, 'Alice', 'active'] -> Option B

4. Quick Check:

   DEFAULT fills missing column with 'active' [OK]

Hint: Missing column uses DEFAULT value automatically [OK]

Common Mistakes:

• Assuming missing columns get NULL instead of DEFAULT
• Thinking query causes syntax error
• Confusing default value with another string

4. Consider this table creation statement:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  quantity INT DEFAULT 1,
  price DECIMAL(5,2) DEFAULT 0.00
);

Which of the following INSERT statements will cause an error?

medium

A. INSERT INTO orders (id, price) VALUES (12, 'free');

B. INSERT INTO orders (id, quantity) VALUES (11, DEFAULT);

C. INSERT INTO orders (id) VALUES (10);

D. INSERT INTO orders (id, quantity, price) VALUES (13, 5, 9.99);

Solution

1. Step 1: Analyze each INSERT statement

   INSERT INTO orders (id) VALUES (10); inserts id only; quantity and price get defaults (valid). INSERT INTO orders (id, quantity) VALUES (11, DEFAULT); uses DEFAULT keyword explicitly (valid). INSERT INTO orders (id, price) VALUES (12, 'free'); tries to insert 'free' into price (decimal), causing type error. INSERT INTO orders (id, quantity, price) VALUES (13, 5, 9.99); provides all valid values.

2. Step 2: Identify the error

   Only INSERT INTO orders (id, price) VALUES (12, 'free'); has a data type mismatch causing an error.

3. Final Answer:

   INSERT INTO orders (id, price) VALUES (12, 'free'); -> Option A

4. Quick Check:

   Wrong data type in INSERT causes error [OK]

Hint: Check data types when inserting values [OK]

Common Mistakes:

• Assuming DEFAULT keyword is invalid in VALUES
• Ignoring data type mismatch errors
• Thinking missing columns cause errors

5. You have a table products with columns id INT, name VARCHAR(50), and stock INT DEFAULT 0. You want to insert multiple rows but only specify id and name. Which SQL statement correctly inserts two products using DEFAULT for stock?

hard

A. INSERT INTO products (id, name, stock) VALUES (1, 'Pen', 0), (2, 'Pencil', 0);

B. INSERT INTO products VALUES (1, 'Pen'), (2, 'Pencil');

C. INSERT INTO products (id, name, stock) VALUES (1, 'Pen', DEFAULT), (2, 'Pencil', DEFAULT);

D. INSERT INTO products (id, name) VALUES (1, 'Pen'), (2, 'Pencil');

Solution

1. Step 1: Understand requirement for column specification

   To only specify id and name, list only those columns; omitted columns like stock use their DEFAULT value automatically.

2. Step 2: Evaluate each option

   INSERT INTO products (id, name) VALUES (1, 'Pen'), (2, 'Pencil'); specifies only id and name, stock uses DEFAULT (correct).
   INSERT INTO products VALUES (1, 'Pen'), (2, 'Pencil'); no column list with 2 values (vs 3 columns) causes mismatch error.
   INSERT INTO products (id, name, stock) VALUES (1, 'Pen', DEFAULT), (2, 'Pencil', DEFAULT); specifies stock column (violates 'only id and name').
   INSERT INTO products (id, name, stock) VALUES (1, 'Pen', 0), (2, 'Pencil', 0); specifies stock explicitly with value (not relying on DEFAULT).

3. Final Answer:

   INSERT INTO products (id, name) VALUES (1, 'Pen'), (2, 'Pencil'); -> Option D

4. Quick Check:

   Omit column with DEFAULT = auto-use [OK]

Hint: Omit columns with DEFAULT to use them automatically [OK]

Common Mistakes:

• No column list + fewer values = mismatch error
• Including stock column even with DEFAULT keyword
• Hardcoding default value instead of omitting column