INSERT INTO multiple rows in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of inserting multiple rows?

Given the table students with columns id (auto-increment), name, and age, what will be the content of the table after running this query?

INSERT INTO students (name, age) VALUES ('Alice', 21), ('Bob', 22), ('Charlie', 23);

MySQL

SELECT * FROM students ORDER BY id;

A [{"id":1,"name":"Alice","age":21}]

B [{"id":1,"name":"Alice","age":21},{"id":2,"name":"Bob","age":22},{"id":3,"name":"Charlie","age":23}]

C SyntaxError

D []

💡 Hint

Remember that INSERT INTO with multiple VALUES adds all rows in one query.

The query inserts three rows at once. The auto-increment id assigns 1, 2, and 3 respectively.

📝 Syntax

intermediate

Which INSERT syntax is correct for multiple rows?

Which of the following queries correctly inserts multiple rows into a table products with columns product_name and price?

A INSERT INTO products (product_name, price) VALUE ('Pen', 1.5), ('Pencil', 1.0);

B INSERT INTO products (product_name, price) VALUES ('Pen', 1.5); ('Pencil', 1.0);

C INSERT INTO products VALUES ('Pen', 1.5), ('Pencil', 1.0);

D INSERT INTO products (product_name, price) VALUES ('Pen', 1.5), ('Pencil', 1.0);

💡 Hint

Check the keyword for multiple rows and punctuation.

Option D uses the correct syntax: VALUES keyword followed by multiple tuples separated by commas.

❓ optimization

advanced

Why prefer INSERT INTO multiple rows over multiple single-row inserts?

You want to add 1000 new users to a table. Which approach is more efficient and why?

A Use one INSERT INTO with 1000 rows in VALUES to reduce overhead.

B Use 1000 separate INSERT INTO statements, one per row, for clarity.

C Use DELETE then INSERT for all rows to refresh the table.

D Use UPDATE statements to add new rows one by one.

💡 Hint

Think about how many times the database has to process queries.

One multi-row INSERT reduces communication and transaction overhead, making it faster.

🔧 Debug

advanced

Identify the error in this multi-row INSERT query

What error will this query cause?

INSERT INTO orders (order_id, product) VALUES (101, 'Book'), (102, 'Pen', 5);

A SyntaxError due to mismatched number of columns and values in second row

B No error, inserts two rows correctly

C Runtime error: duplicate primary key

D TypeError: wrong data type for product

💡 Hint

Check if each row has the same number of values as columns specified.

The second row has 3 values but only 2 columns are specified, causing syntax error.

🧠 Conceptual

expert

What happens if you omit column names in multi-row INSERT?

Given a table employees with columns id (auto-increment), name, and department, what will happen if you run:

INSERT INTO employees VALUES (NULL, 'John', 'Sales'), (NULL, 'Jane', 'HR');

A Only first row inserted, second causes error

B Error because column names are missing

C Rows inserted with auto-increment ids assigned automatically

D Rows inserted but id remains NULL causing duplicates

💡 Hint

If you omit columns, values must match all columns in order.

Omitting column names is allowed if values match all columns in order. NULL for auto-increment triggers automatic id assignment.