SQL 문법 다지기(11)

products 테이블

id	name	price
1	랩톱	1200
2	핸드폰	800
3	타블렛	400

 

orders 테이블

id	product_id	quantity	order_date
101	1	2	2023-03-01
102	2	1	2023-03-02
103	3	5	2023-03-04

 

위와 같은 테이블이 주어졌을 때, 다음 문제들을 풀어보세요.

문제

1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
2. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!
3. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!
4. 2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!
5. 가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!
6. 각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!
7. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

문제 풀이

데이터베이스와 테이블 생성하기

CREATE DATABASE step11;

USE step11;

CREATE TABLE products(
    id INT PRIMARY KEY
    , name VARCHAR(50)
    , price INT
);

INSERT INTO products 
(id, name, price) 
VALUES
(1, '랩톱', 1200)
, (2, '핸드폰', 800)
, (3, '타블렛', 400)
;

CREATE TABLE orders(
    id INT PRIMARY KEY
    , product_id INT
    , quantity INT
    , order_date DATE
);

INSERT INTO orders
(id, product_id, quantity, order_date)
VALUES
(101, 1, 2, '2023-03-01')
, (102, 2, 1, '2023-03-02')
, (103, 3, 5, '2023-03-04')
;

1번

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

 

 

orders 테이블을 기준으로 LEFT JOIN
→ 주문 ID(orders.id)와 주문된 상품의 이름(products.name)을 출력

 

 

정답 :

SELECT order_pro.id AS "주문 ID"
    , order_pro.name AS "상품명"
FROM
(
SELECT o.id
    , o.product_id
    , p.name
FROM orders AS o
LEFT JOIN products AS p
ON o.product_id = p.id
) order_pro
;

 

 

정답 확인 :

2번

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

 

 

orders 테이블을 기준으로 LEFT JOIN(p.id, p.price, o.quantity)
→ 총 매출의 합을 구한다.
→ 내림차순 정렬하고 LIMIT 1
→ 해당 값을 상품 ID와 함께 출력한다.

 

 

정답 :

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

 

 

정답 확인 :

3번

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

 

 

oders 테이블을 기준으로 LEFT JOIN(products.id, orders.quantity)
→ SUM(quantity) … GROUP BY product_id

 

 

정답 :

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

 

 

정답 확인 :

 

4번

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

 

 

oders 테이블을 기준으로 LEFT JOIN
WHERE절 조건식 order_date >= ‘2023-03-03’
SELECT로 선택하는 필드 : products.name, orders.order_date

 

 

정답 :

SELECT order_pro.name AS "상품명"
    , order_pro.order_date AS "주문 날짜"
FROM
(
SELECT p.name
    , o.order_date
FROM orders AS o
LEFT JOIN products p
ON o.product_id = p.id
) order_pro
WHERE order_pro.order_date >= '2023-03-03'
;

 

 

정답 확인 :

5번

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

 

 

3번을 풀었던 방식으로 이름과 수량을 구한다.
→ 수량을 기준으로 내림차순으로 정렬한 후 LIMIT 1을 해준다.

 

 

정답:

SELECT order_pro.name "상품명"
    , order_pro.quantity "판매 수량"
FROM
(
SELECT p.name
    , o.quantity
FROM orders AS o
LEFT JOIN products p
ON o.product_id = p.id
) order_pro
ORDER BY order_pro.quantity DESC
LIMIT 1
;

SELECT order_pro.name "상품명"
    , order_pro.quantity "판매 수량"
FROM
(
SELECT p.name
    , o.quantity
FROM orders AS o
LEFT JOIN products p
ON o.product_id = p.id
) order_pro
ORDER BY "판매 수량" DESC
LIMIT 1
;

 

 

위랑 아래랑 왜 결과가 다를까…?
→ 찾아보니 한글 별칭은 백틱으로 써야한다고 한다.

 

 

정답 확인:

6번

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

 

 

평균 주문 수량 계산하기
→ AVG(orders.quantity)
상품 ID별로 조회하기
→ GROUP BY product_id

 

 

정답:

SELECT product_id AS "상품 ID"
    , AVG(quantity) AS "평균 주문 수량"
FROM orders
GROUP BY product_id
;

 

 

정답 확인:

7번

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

 

 

제품이 판매되지 않았다면 products 테이블을 기준으로 LEFT JOIN 했을 때 quantity의 값이 NULL일 것이라고 추측이 가능하다. 조건에 해당하는 레코드를 조회해야 한다.

서브쿼리에서 조회해야 하는 열 : products.id, products.name, quantity

 

 

정답:

SELECT order_pro.id AS "상품 ID"
    , order_pro.name AS "상품명"
FROM
(
SELECT p.id
    , p.name
    , o.quantity
FROM products AS p
LEFT JOIN orders AS o
ON p.id = o.product_id
) order_pro
WHERE order_pro.quantity IS NULL
;

 

 

정답 확인: