[PostgresDB-2] 10. Relationship (별다방 카페 실습)

참고 : https://cloudstudying.kr/lectures/509

테이블생성 및 데이터 DB 쿼리

CREATE TABLE orders(
	id SERIAL PRIMARY KEY, --PK
	status VARCHAR(50),
	created_at TIMESTAMPTZ
);

CREATE TABLE beverages(
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	price INTEGER,
	btype VARCHAR(50)
);


CREATE TABLE order_details(
	id SERIAL PRIMARY KEY,
	order_id INTEGER REFERENCES orders(id),
	beverage_id INTEGER REFERENCES beverages(id),
	count INTEGER
);

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)
;

문제

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

 

2. 다음 중, 주문#2에 담긴 음료가 아닌 것은?

• 아메리카노
• 라떼
• 콜드브루
• 바닐라 쉐이크

-- 주문 #2에 담긴 음료 찾기 (with JOIN)

SELECT
	beverages.name
FROM
	order_details
JOIN beverages ON
	beverages.id = order_details.beverage_id
WHERE
	order_details.order_id = 2
;

결과

 

3. 다음 중, 취소된 주문내역(order_detatils)이 아닌 것은?

• 주문내역#2
• 주문내역#3
• 주문내역#4
• 주문내역#11

4. 다음 표를 채워 주문#14의 매출액을 구하시오.

결과

Query

-- 주문 #14의 매출
SELECT
	SUM(beverages.price*order_details.count)
FROM
	order_details
JOIN beverages ON
	beverages.id = order_details.beverage_id
WHERE
	order_details.order_id = 14
;

 

 

## 혼자 학습문제

 

다음은 문제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
;

 

출처 : 홍팍의 'SQL 데이터 분석, 활용!'