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.

Practice

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

easy

A. Copies rows from one table to another matching column structure

B. Deletes rows from a table based on a condition

C. Updates existing rows in a table

D. Creates a new table with selected columns

Solution

1. Step 1: Understand the purpose of INSERT INTO ... SELECT

   This statement copies data from one table to another by selecting rows from the source table.

2. Step 2: Recognize the matching column requirement

   The columns selected must match the target table's columns in number and type to insert correctly.

3. Final Answer:

   Copies rows from one table to another matching column structure -> Option A

4. Quick Check:

   INSERT with SELECT copies rows [OK]

Hint: Think of copying rows from one table to another [OK]

Common Mistakes:

• Confusing INSERT with SELECT as a delete or update operation
• Thinking it creates a new table
• Assuming it modifies existing rows instead of inserting

2. Which of the following is the correct syntax to insert rows from table1 into table2 using INSERT INTO ... SELECT?

easy

A. INSERT INTO table1 SELECT * FROM table2;

B. INSERT table2 INTO SELECT * FROM table1;

C. INSERT INTO table2 SELECT * FROM table1;

D. SELECT * INTO table2 FROM table1;

Solution

1. Step 1: Identify correct INSERT INTO ... SELECT syntax

   The correct syntax is INSERT INTO target_table SELECT ... FROM source_table;.

2. Step 2: Match the option to syntax

   INSERT INTO table2 SELECT * FROM table1; matches this syntax exactly, inserting rows from table1 into table2.

3. Final Answer:

   INSERT INTO table2 SELECT * FROM table1; -> Option C

4. Quick Check:

   Correct INSERT INTO ... SELECT syntax = INSERT INTO table2 SELECT * FROM table1; [OK]

Hint: Remember: INSERT INTO target SELECT ... FROM source [OK]

Common Mistakes:

• Swapping table names in INSERT and SELECT
• Using SELECT INTO which is not standard in MySQL
• Incorrect keyword order like INSERT table INTO

3. Given two tables:
employees(id, name, department)
managers(id, name, department)
What will be the result of this query?

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

medium

A. Only employees from Sales department are copied to managers

B. All employees are copied to managers

C. No rows are copied because of syntax error

D. Only managers from Sales department are updated

Solution

1. Step 1: Analyze the SELECT part with WHERE clause

   The SELECT picks only employees where department equals 'Sales'.

2. Step 2: Understand INSERT INTO effect

   These selected rows are inserted into the managers table, adding only Sales department employees.

3. Final Answer:

   Only employees from Sales department are copied to managers -> Option A

4. Quick Check:

   WHERE filters rows inserted [OK]

Hint: WHERE filters rows to insert [OK]

Common Mistakes:

• Assuming all employees are copied ignoring WHERE
• Thinking it updates existing rows instead of inserting
• Confusing syntax causing no rows inserted

4. What is wrong with this query?

INSERT INTO products (id, name) SELECT id, name, price FROM items;

medium

A. Missing WHERE clause causes syntax error

B. Number of columns in INSERT and SELECT do not match

C. Table names are reversed

D. No error, query runs fine

Solution

1. Step 1: Compare columns in INSERT and SELECT

   INSERT specifies 2 columns (id, name), but SELECT returns 3 columns (id, name, price).

2. Step 2: Understand column count requirement

   Number of columns must match exactly; mismatch causes error.

3. Final Answer:

   Number of columns in INSERT and SELECT do not match -> Option B

4. Quick Check:

   Column count mismatch causes error [OK]

Hint: Match column count in INSERT and SELECT [OK]

Common Mistakes:

• Ignoring extra columns in SELECT
• Thinking WHERE is mandatory
• Assuming reversed table names cause error

5. You want to copy only unique customer emails from orders to newsletter_subscribers without duplicates. Which query achieves this?

hard

A. INSERT INTO newsletter_subscribers (email) SELECT email FROM orders GROUP BY email HAVING COUNT(*) > 1;

B. INSERT INTO newsletter_subscribers (email) SELECT email FROM orders WHERE email NOT IN newsletter_subscribers;

C. INSERT INTO newsletter_subscribers (email) SELECT email FROM orders WHERE email IS NULL;

D. INSERT INTO newsletter_subscribers (email) SELECT DISTINCT email FROM orders;

Solution

1. Step 1: Identify how to select unique emails

   Using SELECT DISTINCT email returns unique emails from orders.

2. Step 2: Insert unique emails into newsletter_subscribers

   INSERT INTO with SELECT DISTINCT copies only unique emails, avoiding duplicates.

3. Final Answer:

   INSERT INTO newsletter_subscribers (email) SELECT DISTINCT email FROM orders; -> Option D

4. Quick Check:

   Use DISTINCT to avoid duplicates [OK]

Hint: Use SELECT DISTINCT to insert unique rows [OK]

Common Mistakes:

• Using WHERE NOT IN with incorrect syntax
• Grouping but selecting only duplicates
• Inserting NULL emails unintentionally