Instruction
Very good. Look at another example of a CTE with an INSERT statement.
WITH avg_prices AS (
SELECT
grocery_store_id,
avg(price) AS avg_price
FROM product
GROUP BY grocery_store_id
)
INSERT INTO grocery_avg_prices
SELECT
*
FROM avg_prices
WHERE grocery_store_id != 2;
In this case, we created a CTE to retrieve the average product price of products for each grocery store. We used the AVG aggregate function to compute this average. Then we used GROUP BY to group the records according to grocery_store_id.
In the outer query, we inserted rows into table grocery_avg_prices, but only for grocery stores with an ID other than 2.
Exercise
We have created a new table called product_max_price. Let's insert product names and their respective highest prices into this table.
In the CTE, select the name and maximum price for every product.
In the outer statement, insert product names and max prices into the table product_max_price, but only for 'Bread' and 'Milk' products.
Stuck? Here's a hint!
In the CTE, select the name and maximum price for every product in a CTE. In the outer query, insert the name and price data for 'Bread' and 'Milk' products into the table product_max_price.
