3-8 연습 문제 — CASE / 서브쿼리,
직접 짜본 SQL만 머리에 남는다

SELECT
  member_name,
  age,
  CASE
    WHEN age < 30 THEN '20대'
    WHEN age < 40 THEN '30대'
    WHEN age < 50 THEN '40대'
    ELSE '50대 이상'
  END AS 연령대
FROM
  members;

SELECT
  CASE
    WHEN age < 30 THEN '20대'
    WHEN age < 40 THEN '30대'
    WHEN age < 50 THEN '40대'
    ELSE '50대 이상'
  END AS 연령대,
  COUNT(*) AS 회원수
FROM
  members
GROUP BY
  연령대
ORDER BY
  회원수 DESC;

SELECT
  p1.product_name,
  p1.category_id,
  p1.price
FROM
  products p1
WHERE
  p1.price > (
    SELECT
      AVG(p2.price)
    FROM
      products p2
    WHERE
      p2.category_id = p1.category_id
  )
ORDER BY
  p1.category_id,
  p1.price DESC;