INSERT with SELECT in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows are inserted by this query?

Given two tables employees and new_employees, what rows will be inserted into new_employees after running this query?

INSERT INTO new_employees (id, name, department) SELECT id, name, department FROM employees WHERE department = 'Sales';

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), department VARCHAR(20));
INSERT INTO employees VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales');
CREATE TABLE new_employees (id INT, name VARCHAR(20), department VARCHAR(20));

A No rows inserted

B (2, 'Bob', 'HR')

C (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales')

D (1, 'Alice', 'Sales'), (3, 'Charlie', 'Sales')

💡 Hint

Think about which employees belong to the 'Sales' department.

The SELECT statement filters employees where department is 'Sales'. Only Alice and Charlie match, so only their rows are inserted.

📝 Syntax

intermediate

Which INSERT with SELECT syntax is correct?

Choose the correct syntax to insert data from products table into archived_products table for products with price < 100.

Both tables have columns (product_id, name, price).

A INSERT INTO archived_products (product_id, name, price) SELECT product_id, name, price FROM products WHERE price < 100;

B INSERT archived_products SELECT product_id, name, price FROM products WHERE price < 100;

C INSERT INTO archived_products VALUES SELECT product_id, name, price FROM products WHERE price < 100;

D INSERT INTO archived_products (product_id, name, price) VALUES SELECT product_id, name, price FROM products WHERE price < 100;

💡 Hint

Remember the correct order: INSERT INTO ... SELECT ...

Option A uses the correct syntax: INSERT INTO table (columns) SELECT ... FROM ... WHERE ...; Options B, C, and D have syntax errors.

❓ optimization

advanced

How to optimize this INSERT with SELECT for large data?

You want to insert millions of rows from orders into archived_orders using:

INSERT INTO archived_orders SELECT * FROM orders WHERE order_date < '2023-01-01';

Which option improves performance?

A Use multiple single-row INSERT statements instead of one INSERT with SELECT.

B Remove the WHERE clause to insert all rows faster.

C Add an index on order_date in orders before running the query.

D Disable indexes on archived_orders before insert and rebuild after.

💡 Hint

Think about how filtering large data can be faster.

Adding an index on order_date speeds up the WHERE filtering. Removing WHERE inserts unnecessary rows. Multiple single-row inserts are slower. Disabling indexes on target table can help but is risky and not always best.

🔧 Debug

advanced

Why does this INSERT with SELECT fail?

Given tables users (id INT, username VARCHAR(20)) and archived_users (user_id INT, username VARCHAR(20), archived_at DATE), why does this query fail?

INSERT INTO archived_users (user_id, username) SELECT id, username FROM users;

A Because archived_at column is missing in the INSERT and has no default value.

B Because column names in INSERT and SELECT do not match exactly.

C Because SELECT statement must include archived_at column.

D Because users table has no data.

💡 Hint

Check if all NOT NULL columns without defaults are provided values.

archived_at column is NOT NULL and has no default, so it must be included in INSERT or have a default. Omitting it causes failure.

🧠 Conceptual

expert

What happens if INSERT with SELECT returns zero rows?

If you run:

INSERT INTO archive SELECT * FROM logs WHERE event_date > '2025-01-01';

and no rows match the WHERE condition, what is the result?

A The query raises an error because no rows match the condition.

B No rows are inserted, but the query runs successfully with zero affected rows.

C The archive table is truncated before insert.

D The query inserts NULL rows into archive.

💡 Hint

Think about what happens when SELECT returns empty result.

INSERT with SELECT inserts zero rows if SELECT returns no rows. This is valid and does not cause errors.