SELECT p.name AS product_name,
       pi.count,
       pi.price
  FROM purchase_item pi
  JOIN product p
    ON p.product_id = pi.product_id
 WHERE pi.price = (SELECT max(pim.price)
                     FROM purchase_item pim)

SELECT c.name
  FROM category c
 WHERE c.name like '%an%'
 UNION 
SELECT p.name
  FROM product p
 WHERE p.name like '%an%'

SELECT min(price) AS price_min,
       avg(price) AS price_avg,
       max(price) AS price_max
  FROM product_price

SELECT lower(e.last_name || ' ' || e.first_name) AS lower,
       upper(e.last_name || ' ' || e.first_name) AS upper,
       initcap(e.last_name || ' ' || e.first_name) AS initcap
  FROM employee e
 ORDER BY e.last_name, e.first_name

SELECT c.name AS category_name,
       p.name as product_name,
       pp.price
  FROM product_price pp
  JOIN product p
    ON p.product_id = pp.product_id
  JOIN category c
    ON c.category_id = p.category_id
 WHERE pp.price = (SELECT max (ppm.price)
                     FROM product pm
                     JOIN product_price ppm
                       ON ppm.product_id = pm.product_id
                    WHERE pm.category_id = p.category_id)
 ORDER BY c.name, p.name