Day 7 – Write Subqueries

For test exercise we’ll use the PizzaDB. You can find link to the database script on GIT.

You don’t need to have a database installed to do these exercise, just use the prompts below.

Exercise #1

For each product in the Product table,  list the ProductID, ProductName, Price, and Average ProductPrice as AverageProductPrice.

Order your results by ProductName in ascending order

/* Answer */
select ProductID, ProductName, Price,
    (select avg(price) from Product) AverageProductPrice
from product
order by ProductName

Exercise #2

Here is a query to get the average price for all Pizzas

select avg(price) from Product Where ProductName like '%Pizza%'

Using that as part of your subquery, list all Pizza relate products and compare their price to the average price for pizzia.

Your result should contain the following columns:

• ProductID
• ProductName
• Price
• AveragePizzaPrice
• PriceDifference

where PriceDifference is Price minus the Average Pizza Price

order your result by ProductName

/* Answer */
select ProductID, ProductName, Price,
    (select avg(price) from Product where ProductName like '%Pizza%') AveragePizzaPrice,
    Price - (select avg(price) from Product where ProductName like '%Pizza%') PriceDifference
from Product
where ProductName like '%Pizza%'
order by ProductName

Hint: You’ll need to write two subqueries to complete this query.