INSERT with specific columns in SQL - Step-by-Step Execution

Concept Flow - INSERT with specific columns

Start

↓

Specify Table

↓

Specify Columns

↓

Provide Values

↓

Execute Insert

↓

Row Added to Table

↓

End

This flow shows how to insert data into specific columns of a table step-by-step.

Execution Sample

SQL

INSERT INTO employees (id, name) VALUES (1, 'Alice');

Inserts a new row into the employees table with values only for id and name columns.

Execution Table

Step	Action	Table State Before	Columns Specified	Values Provided	Table State After
1	Start insert command	employees table empty	id, name	(1, 'Alice')	employees table empty
2	Check columns exist	employees table empty	id, name	(1, 'Alice')	employees table empty
3	Insert row with specified columns	employees table empty	id, name	(1, 'Alice')	employees table has 1 row: {id:1, name:'Alice', other columns NULL}
4	End insert command	employees table has 1 row	id, name	(1, 'Alice')	employees table has 1 row: {id:1, name:'Alice', other columns NULL}

💡 Insert completes after adding row with specified columns; unspecified columns get NULL or default.

Variable Tracker

Variable	Start	After Step 3	Final
Table rows count	0	1	1
Inserted row	N/A	{id:1, name:'Alice', others:NULL}	{id:1, name:'Alice', others:NULL}

Key Moments - 2 Insights

Why do unspecified columns get NULL or default values?

Because the INSERT specifies only certain columns, the database fills other columns with NULL or their default values as shown in execution_table step 3.

What happens if you specify columns that don't exist?

The database will raise an error at step 2 when checking columns, preventing the insert.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of the employees table after step 3?

A Empty, no rows

B One row with id=1 and name='Alice', other columns NULL

C One row with all columns filled

D Error, insert failed

💡 Hint

Check the 'Table State After' column in row for step 3.

At which step does the database verify the specified columns exist?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column describing column checks.

If you add a value for a column not listed in the INSERT columns, what happens?

A The insert fails with an error

B The database ignores the extra value

C The value is inserted anyway

D The extra value is inserted into the last column

💡 Hint

Refer to key_moments about specifying columns and errors.

Concept Snapshot

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Specify only columns you want to insert.
- Unspecified columns get NULL or default.
- Database checks columns exist before insert.
- Helps insert partial data safely.

Full Transcript

This visual execution trace shows how an INSERT statement with specific columns works. First, the database starts the insert command and checks that the specified columns exist in the table. Then it inserts a new row with values only for those columns. Other columns in the row get NULL or default values automatically. The table state changes from empty to having one new row with the inserted data. The process ends after the row is added. Common confusions include why unspecified columns get NULL and what happens if columns don't exist. The quizzes test understanding of table state after insert, when column checks happen, and error behavior for mismatched columns.

Practice

1. What does the SQL statement INSERT INTO employees (name, age) VALUES ('Alice', 30); do?

easy

A. Deletes rows where name is 'Alice' and age is 30.

B. Inserts a new row filling all columns with 'Alice' and 30.

C. Updates existing rows setting name to 'Alice' and age to 30.

D. Inserts a new row with only the name and age columns filled, others get default or NULL.

Solution

1. Step 1: Understand the INSERT with specific columns

   The statement specifies only the columns 'name' and 'age' to insert values into.

2. Step 2: Effect on other columns

   Columns not listed get their default values or NULL if allowed.

3. Final Answer:

   Inserts a new row with only the name and age columns filled, others get default or NULL. -> Option D

4. Quick Check:

   INSERT with columns fills only those columns [OK]

Hint: List columns to insert only those, others get defaults [OK]

Common Mistakes:

• Thinking all columns get values from VALUES list
• Confusing INSERT with UPDATE
• Assuming unlisted columns get random data

2. Which of the following is the correct syntax to insert only the columns id and email into a table users?

easy

A. INSERT INTO users (id, email) VALUES (1, '[e-mail]');

B. INSERT INTO users SET id=1, email='[e-mail]';

C. INSERT users (id, email) VALUES (1, '[e-mail]');

D. INSERT INTO users VALUES (1, '[e-mail]');

Solution

1. Step 1: Identify correct INSERT syntax with specific columns

   The correct syntax requires specifying the table, columns in parentheses, then VALUES with matching values.

2. Step 2: Check each option

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); matches the correct syntax: INSERT INTO table (columns) VALUES (values).

3. Final Answer:

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); -> Option A

4. Quick Check:

   INSERT INTO table (cols) VALUES (vals) is correct [OK]

Hint: Use INSERT INTO table (cols) VALUES (vals) format [OK]

Common Mistakes:

• Omitting INTO keyword
• Using SET instead of VALUES
• Not specifying columns when needed

3. Given the table products(id INT, name VARCHAR, price DECIMAL DEFAULT 0), what will be the result of this query?
INSERT INTO products (id, name) VALUES (101, 'Pen');
Then selecting all rows?

medium

A. [{id: 101, name: 'Pen', price: NULL}]

B. [{id: 101, name: 'Pen', price: 0}]

C. [{id: 101, name: 'Pen'}]

D. Syntax error

Solution

1. Step 1: Understand default values on missing columns

   The 'price' column has a default value of 0, so if not specified, it gets 0.

2. Step 2: Result of the INSERT and SELECT

   The inserted row will have id=101, name='Pen', and price=0 from default.

3. Final Answer:

   [{id: 101, name: 'Pen', price: 0}] -> Option B

4. Quick Check:

   Default values fill missing columns [OK]

Hint: Unlisted columns get default values if defined [OK]

Common Mistakes:

• Assuming missing columns become NULL despite default
• Expecting syntax error without all columns
• Ignoring default values

4. Identify the error in this SQL statement:
INSERT INTO orders (order_id, customer) VALUES (123);

medium

A. Missing value for 'customer' column

B. Table name is incorrect

C. VALUES keyword is missing

D. Extra parentheses around columns

Solution

1. Step 1: Check number of columns vs values

   The statement lists two columns but provides only one value.

2. Step 2: Identify mismatch error

   SQL requires the number of values to match the number of columns exactly.

3. Final Answer:

   Missing value for 'customer' column -> Option A

4. Quick Check:

   Columns and values count must match [OK]

Hint: Count columns and values to match [OK]

Common Mistakes:

• Providing fewer values than columns
• Assuming missing values default to NULL always
• Ignoring syntax error messages

5. You have a table students(id INT PRIMARY KEY, name VARCHAR, grade CHAR DEFAULT 'F'). You want to insert a new student with id=5 and name='John', but leave grade as default. Which query is correct?

hard

A. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F');

B. INSERT INTO students VALUES (5, 'John');

C. INSERT INTO students (id, name) VALUES (5, 'John');

D. INSERT INTO students (name, grade) VALUES ('John', 'F');

Solution

1. Step 1: Understand default column behavior

   Omitting 'grade' column in INSERT lets it use its default 'F'.

2. Step 2: Check each option

   INSERT INTO students (id, name) VALUES (5, 'John'); omits grade, using default. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F'); sets it explicitly. INSERT INTO students VALUES (5, 'John'); has value count mismatch. INSERT INTO students (name, grade) VALUES ('John', 'F'); misses id primary key.

3. Final Answer:

   INSERT INTO students (id, name) VALUES (5, 'John'); -> Option C

4. Quick Check:

   Omit columns to use default values [OK]

Hint: Omit columns to use their default values [OK]

Common Mistakes:

• Providing all columns when default is desired
• Omitting primary key column causing errors
• Assuming VALUES must match all columns always