INSERT with SELECT in MySQL - Mini Project: Build & Apply

Insert Data Using SELECT in MySQL

📖 Scenario: You work in a company database where customer orders are stored in one table, and you want to create a summary table that stores only orders with a total amount greater than 100. This helps the sales team focus on big orders.

🎯 Goal: Create a new table called big_orders and insert data into it by selecting orders from the orders table where the total amount is greater than 100.

📋 What You'll Learn

Create a table called big_orders with columns order_id, customer_name, and total_amount.

Insert data into big_orders by selecting from orders where total_amount is greater than 100.

Use the INSERT INTO ... SELECT ... syntax.

💡 Why This Matters

🌍 Real World

In real companies, copying filtered data from one table to another helps create reports or summaries for specific needs, like focusing on big orders.

💼 Career

Database administrators and developers often use INSERT with SELECT to move or archive data efficiently without manual re-entry.

1

Create the orders table with sample data

Create a table called orders with columns order_id (integer), customer_name (varchar 50), and total_amount (decimal 10,2). Then insert these exact rows: (1, 'Alice', 120.50), (2, 'Bob', 75.00), (3, 'Charlie', 200.00).

MySQL

-- Your code here to create the orders table and insert data

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add data.

2

Create the big_orders table

Create a table called big_orders with the same columns as orders: order_id (integer), customer_name (varchar 50), and total_amount (decimal 10,2).

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(50),
  total_amount DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_name, total_amount) VALUES
(1, 'Alice', 120.50),
(2, 'Bob', 75.00),
(3, 'Charlie', 200.00);

-- Your code here to create the big_orders table

Need a hint?

Use CREATE TABLE with the same column definitions as orders.

3

Insert data into big_orders using SELECT

Write an INSERT INTO big_orders statement that selects order_id, customer_name, and total_amount from orders where total_amount is greater than 100.

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(50),
  total_amount DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_name, total_amount) VALUES
(1, 'Alice', 120.50),
(2, 'Bob', 75.00),
(3, 'Charlie', 200.00);

CREATE TABLE big_orders (
  order_id INT,
  customer_name VARCHAR(50),
  total_amount DECIMAL(10,2)
);

-- Your code here to insert data into big_orders using SELECT

Need a hint?

Use INSERT INTO ... SELECT ... FROM ... WHERE ... to copy filtered rows.

4

Verify the data in big_orders

Write a SELECT statement to get all rows from big_orders to check that only orders with total_amount greater than 100 were inserted.

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(50),
  total_amount DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_name, total_amount) VALUES
(1, 'Alice', 120.50),
(2, 'Bob', 75.00),
(3, 'Charlie', 200.00);

CREATE TABLE big_orders (
  order_id INT,
  customer_name VARCHAR(50),
  total_amount DECIMAL(10,2)
);

INSERT INTO big_orders (order_id, customer_name, total_amount)
SELECT order_id, customer_name, total_amount FROM orders WHERE total_amount > 100;

-- Your code here to select all rows from big_orders

Need a hint?

Use SELECT * FROM big_orders; to see all rows in the table.