TIL 2024-09-20( SQL_걷기반 마지막 문제 )

마지막 연습 문제 ! 

products table

orders table

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

SELECT p.name, o.id
FROM products p LEFT JOIN orders o ON p.id = o.product_id

주문 ID = orders.id, 상품의 이름 = products.name 이라고 생각하면된다.

각 테이블마다 데이터 갯수가 동일하기 때문에 inner와 left 중 사용하고 싶은 것을 사용하면 된다.

 

 

 

 

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

SELECT p.id ,(p.price * o.quantity) sales
FROM products p LEFT JOIN orders o ON p.id = o.product_id
ORDER BY sales DESC
LIMIT 1

이 문제를 풀면서 문제가 조금 이상하다고 느꼈다. 총 매출이 가장 높은 상품.

각 상품의 price와 quantity를 곱하여 각 상품의 가격 중 매출이 가장 높은 것을 고르라는 문제 같은데...

 

(price * quantity'의 합') '의 합'..? 이미 두 컬럼을 곱하면 각 상품의 매출이 나오는데 합은 무엇을 뜻하는 지 이해하지 못했다.

 

일단 문제의 의도는 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 것.

sales로 기준으로 내림차순으로 만들고 LIMIT 1으로 맨 위 데이터만 조회하면 된다.

 

  

 

 

 

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

SELECT p.id, SUM(o.quantity) sum_quantity
FROM products p LEFT JOIN orders o ON p.id = o.product_id
GROUP BY 1

각 상품 ID 별 판매된 총 수량.

 

위에 문제처럼 왜 총 수량이 필요할까 계속 생각이 드는 문제였다.

계속 곰곰히 생각해보니 지금은 data table의 갯수가 적지만 추가적으로 새로 생긴다고 생각하니 조금은 이해가 되었다.

 

문제를 풀어보면,

sum(quantity)를 해주고 상품 ID 별 이기 때문에 GROUP BY로 묶어준다.

 

 

 

 

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

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

상품의 이름을 나열해야하기에 p.name 컬럼만 넣어주고

order_date가 2023-03-03 이후이니 '>' 을 이용하여 더 큰 날짜만 조회되도록한다.

이때 order_date가 무슨 타입으로 저장되어 있는지 꼭 확인해야한다.

문자형으로 저장되어 있는 경우가 많다.

 

 

 

 

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

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

가장 많이 판매된 상품, 그 상품의 이름

두 가지만 찾으면 된다.

 

가장 많이 판매된 상품은 당연히 quantity가 제일 높은 상품이고

그렇기에 quantity 기준으로 내림차순 정렬을 하고 LIMIT 1을 해주면 된다.

 

 

 

 

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

SELECT p.id, AVG(o.quantity) avg_quan
FROM products p LEFT JOIN orders o ON p.id = o.product_id
GROUP BY 1

각 상품 ID별 이기에 p.id를  GROUP BY 로 묶어주고 평균 주문 수량...

사실 각 상품별 평균 주문 수량을 따로 구할 필요 없지만 문제가 요구하기에 AVG를 사용해주었다.

 

 

 

 

 

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

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

판매되지 않았다면 주문된 상품의 아이디가 없다는 것을 생각해야한다.

 

그렇기에 주문 아이디가 NULL인 상품의 아이디와 이름을 가져오면 된다.