Amazing! Let's summarize what we have learned. You know how to:

• Use INSERT INTO with SELECT to insert data from one table into another:

INSERT INTO HistoryPurchase 
SELECT * FROM Purchase;

• Delete information from one table based on data from another table, as in DELETE FROM ... FROM:

DELETE FROM Purchase 
FROM Product 
WHERE Product.Id = Purchase.ProductId 
AND Product.Price IS NULL;

• Update data in one table based on data from another table, using UPDATE ... FROM:

UPDATE Purchase 
SET Purchase. TotalPrice = Product.Price * Purchase.Quantity 
FROM Product 
WHERE Purchase.ProductId = Product.Id;

• Use JOIN in INSERT, UPDATE, and DELETE statements:

DELETE Purchase 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE Product.DeliveredDateTime < 2017;

• Write INSERT, UPDATE, and DELETE statements that include subqueries:

DELETE FROM Product 
WHERE Id NOT IN (SELECT ProductId FROM Order) 
AND YEAR(DeliveredDateTime) < YEAR(GETDATE())- 3;

Note: INSERT, UPDATE, and DELETE operations can also include common table expressions (CTEs). However, an explanation of that is beyond the scope of this course. If you would like to learn more about it, check out the Recursive Queries in SQL Server course that we offer.