SQL | 걷기반 마지막

다음과 같은 상품(products) 테이블과 주문(orders) 테이블이 있습니다.

44. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!

SELECT o.id "주문 ID",
       p.name "상품 이름"
FROM orders o LEFT JOIN products p ON o.product_id=p.id ;

45. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!

SELECT p.id,
       SUM(p.price * o.quantity) "총 매출"
FROM orders o LEFT JOIN products p ON o.product_id=p.id
ORDER BY "총 매출" DESC
LIMIT 1 ;

46. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!

SELECT p.id,
       SUM(o.quantity) "총 수량"
FROM orders o LEFT JOIN products p ON o.product_id=p.id
GROUP BY p.id ;

47. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!

SELECT p.name
FROM orders o LEFT JOIN products p ON o.product_id=p.id
WHERE o.order_date>'2023-03-03' ;

48. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!

SELECT p.name
FROM orders o LEFT JOIN products p ON o.product_id=p.id
ORDER BY o.quantity DESC
LIMIT 1 ;

49. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!

SELECT product_id,
       AVG(quantity)
FROM orders
GROUP BY product_id ;

50. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

SELECT p.id,
       p.name
FROM products p LEFT JOIN orders o ON p.id=o.product_id
WHERE o.product_id IS NULL ;