Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
Referring to other tables
10. Insert rows using JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Well done! When writing an INSERT statement, you can also refer to other tables by using a JOIN in a SELECT clause.

The store staff would like to add new tables to help them prepare detailed sales reports. They have created a table named sales_report that combines details about individual purchases: purchase_id, total_price, order_date, and quantity. Additionally, the table also includes details about the product bought in this purchase: product_name (i.e., name from the product table). To insert data into this table, you can use the following statement:

INSERT INTO sales_report
SELECT
  pu.id,
  pr.name,
  pu.total_price,
  pu.order_date,
  pu.quantity
FROM purchase pu
INNER JOIN product pr
  ON pu.product_id = pr.id
WHERE is_completed = true
  AND pu.order_date <= '2016-12-31';

The SELECT query chooses important product details for each purchase, making it easy to create sales reports. The query joins data from the product and purchase tables and puts it into the sales_report table.

Exercise

Insert the sales information for 2017 into the sales_report table. Insert the following data:

  • The ID of the purchase (as purchase_id).
  • The name of the product (as product_name).
  • The total price for this purchase.
  • The order date.
  • The quantity.

Stuck? Here's a hint!

Use:

WHERE pu.order_date < '2018-01-01'
  AND pu.order_date >= '2017-01-01'