걷기 SQL - 마지막 연습 문제 !

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

SELECT o.id AS order_id, p.name AS products_name FROM orders o JOIN products p ON o.product_id = p.id

 

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

SELECT o.product_id, SUM(p.price * o.quantity) AS total_sales FROM orders o JOIN products p ON o.product_id = p.id GROUP BY o.product_id ORDER BY total_sales DESC LIMIT 1;

 

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

SELECT product_id, SUM(quantity) AS total_quantity_sold FROM orders GROUP BY product_id;

 

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

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

 

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

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

 

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

SELECT product_id, AVG(quantity) AS avg_quantity_sold 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 --LEFT JOIN은 NULL인 항목도 표시 WHERE o.product_id IS NULL;

 

---

풀이

49번

평균 구하기

1) 칼럼 값에 NULL 없을 때 : AVG 함수

평균을 구할 칼럼의 값이 NULL이면 집계 대상에서 제외됩니다.

SELECT AVG(sal) FROM emp WHERE job = 'SALESMAN'

 

2) 칼럼 값에 NULL 이 있을 때 : ISNULL함수

ISNULL 함수를 사용하여 성과급(comm)이 NULL인 경우 "0"으로 치환하여 모든 직원(7명)의 평균 성과급을 집계합니다.

SELECT AVG(comm) AS avg1 , AVG(ISNULL(comm, 0)) AS avg2 FROM emp WHERE job IN ('MANAGER', 'SALESMAN')

 

3) 그룹별 평균 구하기

SELECT job , AVG(sal) AS avg1 , SUM(sal) / COUNT(*) AS avg2 FROM emp GROUP BY job

 

4)조회된 전체 데이터에서 그룹별 평균 구하기

SELECT empno , ename , job , sal , AVG(sal) OVER(PARTITION BY job) AS avg_sal FROM emp WHERE job IN ('MANAGER', 'SALESMAN')

※PARTITION BY 절은 GROUP BY 절과 비슷한 개념이며 전체 데이터를 그룹으로 묶지 않고 집계된 값을 세부 데이터와 함께 사용할 수 있습니다.

 

---

출처

49번 : https://gent.tistory.com/513