[SQL] 데이터 분석, 활용 _ 데이터의 관계 연습 문제

카페 DB 구조도

카페 DB 데이터

---

📑 다음 중, 주문(orders)과 음료(beverages)의 관계는? [ M:N ]
1:1
1:N
M:N

📑 다음 중, 주문#2에 담긴 음료가 아닌 것은? [ 콜드브루 ]
아메리카노
라떼
콜드브루
바닐라 쉐이크

📑 다음 중, 취소된 주문내역(order_detatils)이 아닌 것은? [ 주문내역#11 ]
주문내역#2
주문내역#3
주문내역#4
주문내역#11

---

🧸연습하기

📑 카페 DB의 주문, 음료, 주문내역 테이블을 만드시오.

-- 주문
CREATE TABLE ORDERS(
	ID SERIAL PRIMARY KEY,
	STATUS VARCHAR(15),
	CREATED_AT timestamptz
);

-- 음료
CREATE TABLE BEVERAGES(
	ID SERIAL PRIMARY KEY,
	NAME VARCHAR(50),
	PRICE INTEGER,
	BTYPE VARCHAR(50)
);

-- 주문내역
CREATE TABLE ORDER_DETAIL(
	ID SERIAL PRIMARY KEY,
	ORDER_ID INTEGER REFERENCES ORDERS(ID),
	BEVERAGE_ID INTEGER REFERENCES BEVERAGES(ID)
);

-- 열 추가
ALTER TABLE ORDER_DETAIL ADD COUNT INTEGER;

-- 테이블명 변경
ALTER TABLE ORDER_DETAIL RENAME TO ORDER_DETAILS;

SELECT * FROM ORDER_DETAILS;

📑 생성한 테이블에 레코드를 채우시오.

-- 주문(orders)
INSERT INTO
  orders(status, created_at)
VALUES
  ('DELIVERED', '2022-02-26 8:32:13'),
  ('CANCELLED', '2022-02-26 8:35:27'),
  ('DELIVERED', '2022-02-26 8:44:53'),
  ('DELIVERED', '2022-02-26 9:05:12'),
  ('DELIVERED', '2022-02-26 9:11:35'),
  ('DELIVERED', '2022-02-26 9:14:18'),
  ('DELIVERED', '2022-02-26 9:34:20'),
  ('DELIVERED', '2022-02-26 9:44:27'),
  ('DELIVERED', '2022-02-26 9:48:25'),
  ('DELIVERED', '2022-02-26 11:14:52'),
  ('CANCELLED', '2022-02-26 11:32:13'),
  ('DELIVERED', '2022-02-26 11:55:40'),
  ('DELIVERED', '2022-02-26 12:15:01'),
  ('DELIVERED', '2022-02-26 12:21:52'),
  ('CANCELLED', '2022-02-26 12:29:17')
;
-- 음료(beverages)
INSERT INTO
  beverages(name, price, btype)
VALUES
  ('아메리카노',   4500, 'COFFEE'),
  ('라떼',        5000, 'COFFEE'),
  ('카푸치노',     5000, 'COFFEE'),
  ('카페모카',     5500, 'COFFEE'),
  ('콜드브루',     5200, 'COFFEE'),
  ('레몬 에이드',  3800, 'AID'),
  ('자몽 에이드',  4800, 'AID'),
  ('바닐라 쉐이크', 5800, 'SHAKE')
;
-- 주문내역(order_details)
INSERT INTO
  order_details(order_id, beverage_id, count)
VALUES
  (1,  1, 1),
  (2,  1, 2),
  (2,  2, 3),
  (2,  8, 2),
  (3,  1, 2),
  (4,  8, 1),
  (5,  4, 1),
  (6,  5, 3),
  (7,  1, 3),
  (7,  2, 2),
  (8,  1, 1),
  (9,  5, 1),
  (10, 1, 4),
  (11, 2, 1),
  (12, 3, 1),
  (13, 1, 6),
  (14, 1, 8),
  (14, 2, 2),
  (14, 6, 1),
  (15, 1, 4)
;

📑 다음은 문제2와 문제3, 그리고 문제4를 위한 쿼리이다.
이를 실행하여 결과를 확인하고, 여기서 사용된 JOIN의 개념을 조사하시오.

-- 주문#2에 담긴 음료 이름들
-- JOIN 문법 사용(다음 강의에서 배움)
SELECT
  beverages.name
FROM
  order_details
JOIN beverages ON
  beverages.id = order_details.beverage_id
WHERE
  order_id = 2
;
-- 취소된 주문내역의 id들
-- JOIN 문법 사용(다음 강의에서 배움)
SELECT
  order_details.id
FROM
  order_details
JOIN orders ON
  orders.id = order_details.order_id
WHERE
  orders.status = 'CANCELLED'
;
-- 주문#14의 매출
-- JOIN 문법 사용(다음 강의에서 배움)
SELECT
  SUM(order_details.count * beverages.price)
FROM
  order_details
JOIN beverages ON
  beverages.id = order_details.beverage_id
WHERE
  order_id = 14
;