INSERT INTO single row in MySQL - Step-by-Step Execution

Concept Flow - INSERT INTO single row

Start

↓

Write INSERT INTO statement

↓

Specify table name

↓

List columns

↓

Provide values for columns

↓

Execute statement

↓

Row added to table

↓

End

The flow shows writing an INSERT INTO statement, specifying the table and columns, providing values, executing, and adding a new row.

Execution Sample

MySQL

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

This adds one new row to the employees table with id=1, name='Alice', and age=30.

Execution Table

Step	Action	Details	Table State
1	Start	Prepare to insert row	employees table unchanged
2	Parse statement	Identify table: employees	employees table unchanged
3	Parse columns	Columns: id, name, age	employees table unchanged
4	Parse values	Values: 1, 'Alice', 30	employees table unchanged
5	Execute insert	Add row (1, 'Alice', 30)	employees table now has 1 new row
6	End	Insert complete	employees table updated with new row

💡 Insert completes after adding one row to the employees table.

Variable Tracker

Variable	Start	After Step 4	After Step 5	Final
table	undefined	employees	employees	employees
columns	undefined	(id, name, age)	(id, name, age)	(id, name, age)
values	undefined	(1, 'Alice', 30)	(1, 'Alice', 30)	(1, 'Alice', 30)
table state	empty or previous rows	unchanged	1 new row added	updated with new row

Key Moments - 3 Insights

Why do we need to specify columns in the INSERT statement?

Specifying columns (see execution_table step 3) tells the database exactly which columns the values belong to, preventing errors if the table has many columns or defaults.

What happens if the values count doesn't match the columns count?

The database will give an error during parsing or execution (before step 5), because each column must have a matching value.

Does INSERT INTO add multiple rows at once here?

No, this example inserts a single row only (see execution_table step 5), because only one set of values is provided.

Visual Quiz - 3 Questions

Look at the execution_table, what is the table state after step 4?

A Table is unchanged

B Table is deleted

C Table has the new row added

D Table columns are changed

💡 Hint

Check the 'Table State' column in execution_table row for step 4

At which step is the new row actually added to the table?

A Step 2

B Step 3

C Step 5

D Step 6

💡 Hint

Look for the action 'Execute insert' in execution_table

If we omit the column list in the INSERT statement, what must be true?

A Values can be fewer than columns

B Values must match all columns in table order

C Values can be in any order

D No values are needed

💡 Hint

Recall that columns specify order; without them, values must match table columns exactly

Concept Snapshot

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- Adds one new row to the table.
- Columns list matches values count.
- Values must be in order of columns.
- Executes immediately to update table.

Full Transcript

This visual execution shows how an INSERT INTO statement adds a single row to a database table. First, the statement is prepared and parsed to identify the target table and columns. Then the values are matched to the columns. Finally, the row is added to the table, updating its state. Key points include specifying columns to avoid errors and ensuring values count matches columns. The execution table tracks each step and the table's state changes. This helps beginners see exactly when and how the row is inserted.

Practice

1. What does the INSERT INTO statement do in MySQL?

easy

A. Creates a new table

B. Deletes a row from a table

C. Adds a new row to a table

D. Updates existing rows in a table

Solution

1. Step 1: Understand the purpose of INSERT INTO

   The INSERT INTO statement is used to add new data rows into an existing table.

2. Step 2: Compare with other SQL commands

   Deleting removes rows, updating changes existing rows, and creating makes new tables, so these are different commands.

3. Final Answer:

   Adds a new row to a table -> Option C

4. Quick Check:

   INSERT INTO = Add row [OK]

Hint: INSERT INTO always adds new rows, not deletes or updates [OK]

Common Mistakes:

• Confusing INSERT with DELETE or UPDATE
• Thinking INSERT creates tables
• Assuming INSERT modifies existing rows

2. Which of the following is the correct syntax to insert a single row into a table named users with columns id and name?

easy

A. INSERT users INTO (id, name) VALUES (1, 'Alice');

B. INSERT INTO users (id, name) VALUES (1, 'Alice');

C. INSERT INTO users VALUES id=1, name='Alice';

D. INSERT INTO users (id, name) SET (1, 'Alice');

Solution

1. Step 1: Check the standard INSERT INTO syntax

   The correct syntax is INSERT INTO table_name (columns) VALUES (values); which matches INSERT INTO users (id, name) VALUES (1, 'Alice');.

2. Step 2: Identify errors in other options

   INSERT users INTO (id, name) VALUES (1, 'Alice'); has wrong order of keywords, C uses invalid assignment syntax, and D uses SET incorrectly.

3. Final Answer:

   INSERT INTO users (id, name) VALUES (1, 'Alice'); -> Option B

4. Quick Check:

   INSERT INTO ... VALUES (...) is correct syntax [OK]

Hint: Remember: INSERT INTO table (cols) VALUES (vals); [OK]

Common Mistakes:

• Swapping keywords order
• Using SET instead of VALUES
• Incorrect assignment inside VALUES

3. Given the table products(id INT, name VARCHAR(20), price DECIMAL(5,2)), what will be the result of this query?
INSERT INTO products (id, name, price) VALUES (10, 'Pen', 1.50); SELECT * FROM products WHERE id = 10;

medium

A. [{"id":10, "name":"Pen", "price":1.50}]

B. Syntax error due to missing semicolon

C. Empty result, no rows inserted

D. [{"id":null, "name":"Pen", "price":1.50}]

Solution

1. Step 1: Analyze the INSERT statement

   The query inserts a row with id=10, name='Pen', price=1.50 into the products table.

2. Step 2: Check the SELECT query result

   The SELECT fetches the row with id=10, which was just inserted, so it returns that row.

3. Final Answer:

   [{"id":10, "name":"Pen", "price":1.50}] -> Option A

4. Quick Check:

   Inserted row is returned by SELECT [OK]

Hint: Inserted row appears in SELECT immediately after insert [OK]

Common Mistakes:

• Assuming insert fails without error
• Expecting empty result after insert
• Confusing syntax errors with correct queries

4. Identify the error in this query:
INSERT INTO employees (id, name, salary) VALUES (5, John, 3000);

medium

A. Salary should be a string

B. Missing parentheses around VALUES

C. Wrong table name

D. Missing quotes around the string 'John'

Solution

1. Step 1: Check string value syntax

   String values like names must be enclosed in single quotes in SQL, but 'John' is unquoted here.

2. Step 2: Verify other parts of the query

   Parentheses around VALUES are correct, table name is plausible, and salary is numeric so no quotes needed.

3. Final Answer:

   Missing quotes around the string 'John' -> Option D

4. Quick Check:

   Strings need quotes in VALUES [OK]

Hint: Always quote text values in INSERT [OK]

Common Mistakes:

• Forgetting quotes for text
• Confusing numeric and string types
• Misplacing parentheses

5. You have a table orders(order_id INT, customer VARCHAR(50), total DECIMAL(7,2)). You want to insert a new order with order_id 101, customer name "O'Reilly", and total 250.75. Which query correctly inserts this row?

hard

A. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75);

B. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O\'Reilly', 250.75);

C. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O'Reilly', 250.75);

D. INSERT INTO orders (order_id, customer, total) VALUES (101, "O'Reilly", 250.75);

Solution

1. Step 1: Understand how to escape single quotes in SQL strings

   In SQL, single quotes inside strings are escaped by doubling them: 'O''Reilly'.

2. Step 2: Evaluate each option's string quoting

   INSERT INTO orders (order_id, customer, total) VALUES (101, 'O'Reilly', 250.75); breaks string due to unescaped single quote. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O\'Reilly', 250.75); uses backslash which is not standard in MySQL. INSERT INTO orders (order_id, customer, total) VALUES (101, "O'Reilly", 250.75); uses double quotes which are not string delimiters in SQL. INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75); correctly doubles the single quote.

3. Final Answer:

   INSERT INTO orders (order_id, customer, total) VALUES (101, 'O''Reilly', 250.75); -> Option A

4. Quick Check:

   Escape single quote by doubling it [OK]

Hint: Escape single quote inside strings by doubling it '' [OK]

Common Mistakes:

• Using unescaped single quotes inside strings
• Using backslash escape in MySQL strings
• Using double quotes for string literals