INSERT with DEFAULT values in SQL - Step-by-Step Execution

Concept Flow - INSERT with DEFAULT values

Start INSERT command

↓

Check if VALUES provided

↓

Insert given

↓

Add row to table

↓

End

When inserting a row, if no value is given for a column, SQL uses the DEFAULT value for that column.

Execution Sample

SQL

CREATE TABLE fruits (
  id INT DEFAULT 1,
  name VARCHAR(10) DEFAULT 'apple'
);

INSERT INTO fruits DEFAULT VALUES;

This creates a table with default values and inserts a row using those defaults.

Execution Table

Step	Action	Evaluation	Result
1	Start INSERT command	INSERT INTO fruits DEFAULT VALUES;	Ready to insert a new row
2	Check if VALUES provided	No explicit values given	Use DEFAULT values for all columns
3	Assign DEFAULT to id	id DEFAULT is 1	id = 1
4	Assign DEFAULT to name	name DEFAULT is 'apple'	name = 'apple'
5	Add row to table	Row to insert: (1, 'apple')	Row inserted successfully
6	End	Insertion complete	Table now has 1 row with id=1 and name='apple'

💡 No explicit values given, so DEFAULT values used for all columns

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
id	NULL	1	1	1
name	NULL	NULL	'apple'	'apple'

Key Moments - 2 Insights

Why does the INSERT work without specifying any column values?

Because the INSERT uses DEFAULT VALUES, SQL fills in all columns with their default values as shown in execution_table step 2 and 3.

What happens if a column has no DEFAULT and no value is given?

The INSERT would fail with an error because the column requires a value. Here, all columns have defaults, so it succeeds (see execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution table, what value is assigned to 'name' at step 4?

A 'default'

B NULL

C 'apple'

D 1

💡 Hint

Check the 'Assign DEFAULT to name' row in the execution_table.

At which step does the row get added to the table?

A Step 5

B Step 3

C Step 2

D Step 6

💡 Hint

Look for the action 'Add row to table' in the execution_table.

If the 'id' column had no DEFAULT value, what would happen at step 3?

A id would be set to NULL

B An error would occur

C id would be set to 0

D The row would be inserted anyway

💡 Hint

Refer to key_moments about missing DEFAULT values causing errors.

Concept Snapshot

INSERT with DEFAULT values:
- Use: INSERT INTO table DEFAULT VALUES;
- Inserts one row using all columns' default values
- If no default and no value given, error occurs
- Useful for quick inserts with defaults
- Defaults defined in table schema

Full Transcript

This visual execution shows how an INSERT statement with DEFAULT VALUES works. The process starts by checking if explicit values are provided. Since none are given, SQL uses the default values defined for each column. The 'id' column gets the default 1, and the 'name' column gets 'apple'. Then the row (1, 'apple') is added to the table. The execution ends with one row inserted. If a column had no default and no value, the insert would fail. This method is useful for inserting rows quickly using predefined defaults.

Practice

1. What does the SQL statement INSERT INTO table_name DEFAULT VALUES; do?

easy

A. Inserts a new row using all default values for columns

B. Deletes all rows from the table

C. Updates existing rows with default values

D. Selects all rows with default values

Solution

1. Step 1: Understand the INSERT statement

   The statement inserts a new row into the table.

2. Step 2: Recognize DEFAULT VALUES usage

   Using DEFAULT VALUES means the new row uses default values defined for each column.

3. Final Answer:

   Inserts a new row using all default values for columns -> Option A

4. Quick Check:

   INSERT ... DEFAULT VALUES inserts default row [OK]

Hint: DEFAULT VALUES inserts a row with all default column values [OK]

Common Mistakes:

• Thinking it deletes or updates rows
• Confusing DEFAULT VALUES with SELECT
• Assuming it inserts NULLs instead of defaults

2. Which of the following is the correct syntax to insert a row with default values in SQL?

easy

A. INSERT INTO table_name VALUES (DEFAULT);

B. INSERT INTO table_name VALUES DEFAULT;

C. INSERT DEFAULT INTO table_name VALUES;

D. INSERT INTO table_name DEFAULT VALUES;

Solution

1. Step 1: Recall correct syntax for default insertion

   The correct syntax is INSERT INTO table_name DEFAULT VALUES; to insert a row with all default values.

2. Step 2: Check each option

   INSERT INTO table_name DEFAULT VALUES; matches the correct syntax exactly; others are invalid or incorrect.

3. Final Answer:

   INSERT INTO table_name DEFAULT VALUES; -> Option D

4. Quick Check:

   Correct syntax = INSERT INTO ... DEFAULT VALUES [OK]

Hint: Remember: DEFAULT VALUES comes after table name in INSERT [OK]

Common Mistakes:

• Placing DEFAULT inside VALUES parentheses
• Swapping DEFAULT and VALUES keywords
• Using incorrect keyword order

3. Given the table users(id INT DEFAULT 0, name VARCHAR(20) DEFAULT 'guest'), what will be the result of INSERT INTO users DEFAULT VALUES; SELECT * FROM users; after running on an empty table?

medium

A. Empty result set

B. [{"id": 0, "name": "guest"}]

C. Syntax error

D. [{"id": NULL, "name": NULL}]

Solution

1. Step 1: Understand table defaults

   The table has default values: id = 0, name = 'guest'.

2. Step 2: Effect of INSERT DEFAULT VALUES

   Inserting default values adds a row with id=0 and name='guest'.

3. Step 3: SELECT result

   After insertion, selecting all rows returns one row with those default values.

4. Final Answer:

   [{"id": 0, "name": "guest"}] -> Option B

5. Quick Check:

   Defaults inserted row = id 0, name guest [OK]

Hint: Defaults fill columns if no values given [OK]

Common Mistakes:

• Expecting NULL instead of default values
• Assuming syntax error due to DEFAULT VALUES
• Thinking table remains empty

4. Consider the table products(id INT NOT NULL, name VARCHAR(20) DEFAULT 'unknown'). What error occurs when running INSERT INTO products DEFAULT VALUES;?

medium

A. Error: Column 'id' has no default and no value provided

B. No error, inserts row with NULL id and 'unknown' name

C. Syntax error due to DEFAULT VALUES usage

D. Inserts row with id=0 and name='unknown'

Solution

1. Step 1: Check columns for defaults

   The 'id' column has no default and is NOT nullable.

2. Step 2: Effect of INSERT DEFAULT VALUES

   Trying to insert default values fails because 'id' has no default and no value is given.

3. Final Answer:

   Error: Column 'id' has no default and no value provided -> Option A

4. Quick Check:

   Missing default for NOT NULL column causes error [OK]

Hint: All columns need defaults or allow NULL for DEFAULT VALUES [OK]

Common Mistakes:

• Assuming NULL is inserted automatically
• Thinking DEFAULT VALUES always works
• Confusing syntax error with constraint error

5. You have a table orders(order_id INT PRIMARY KEY DEFAULT nextval('order_seq'), status VARCHAR(10) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP). Which SQL statement correctly inserts a new order using all defaults except setting status to 'shipped'?

hard

A. INSERT INTO orders (order_id, status, created_at) VALUES (DEFAULT, DEFAULT, 'shipped');

B. INSERT INTO orders DEFAULT VALUES SET status = 'shipped';

C. INSERT INTO orders (status) VALUES ('shipped');

D. INSERT INTO orders (DEFAULT, 'shipped', DEFAULT);

Solution

1. Step 1: Understand default and explicit values

   We want to use defaults for order_id and created_at, but set status explicitly.

2. Step 2: Check each option

   INSERT INTO orders (status) VALUES ('shipped'); correctly lets the DBMS use defaults implicitly for unspecified columns order_id and created_at. INSERT INTO orders DEFAULT VALUES SET status = 'shipped'; is invalid syntax. INSERT INTO orders (order_id, status, created_at) VALUES (DEFAULT, DEFAULT, 'shipped'); incorrectly sets status to its default ('pending') and attempts to set created_at to 'shipped' causing a type mismatch error. INSERT INTO orders (DEFAULT, 'shipped', DEFAULT); is invalid syntax (missing VALUES keyword).

3. Final Answer:

   INSERT INTO orders (status) VALUES ('shipped'); -> Option C

4. Quick Check:

   Unspecified columns use their default values automatically [OK]

Hint: Unspecified columns in INSERT use column defaults [OK]

Common Mistakes:

• Thinking omitting columns always causes errors
• Using DEFAULT VALUES with SET syntax
• Misplacing values and DEFAULT keywords in VALUES clause