Join 예제 2

 

INSERT INTO series (title, released_year, genre) VALUES

('Archer', 2009, 'Animation'),

('Arrested Development', 2003, 'Comedy'),

("Bob's Burgers", 2011, 'Animation'),

('Bojack Horseman', 2014, 'Animation'),

("Breaking Bad", 2008, 'Drama'),

('Curb Your Enthusiasm', 2000, 'Comedy'),

("Fargo", 2014, 'Drama'),

('Freaks and Geeks', 1999, 'Comedy'),

('General Hospital', 1963, 'Drama'),

('Halt and Catch Fire', 2014, 'Drama'),

('Malcolm In The Middle', 2000, 'Comedy'),

('Pushing Daisies', 2007, 'Comedy'),

('Seinfeld', 1989, 'Comedy'),

('Stranger Things', 2016, 'Drama');

 

 

INSERT INTO reviewers (first_name, last_name) VALUES

('Thomas', 'Stoneman'),

('Wyatt', 'Skaggs'),

('Kimbra', 'Masters'),

('Domingo', 'Cortes'),

('Colt', 'Steele'),

('Pinkie', 'Petit'),

('Marlon', 'Crafford');

 

 

INSERT INTO reviews(series_id, reviewer_id, rating) VALUES

(1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),

(2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),

(3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),

(4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),

(5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),

(6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),

(7,2,9.1),(7,5,9.7),

(8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),

(9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),

(10,5,9.9),

(13,3,8.0),(13,4,7.2),

(14,2,8.5),(14,3,8.9),(14,4,8.9);

 

reviews 테이블의 rating 컬럼은 Decima(3,1) 으로 설정 (10.0 4,5 이런식으로 반환하기위함)

foreign key 설정 - reviews series_id=series id / reviews reviewer_id=reviewers id

-- 고객 이름과 주문 날짜를 조회하세요

SELECT c.customer_name ,o.order_date 
FROM customers c 
join orders o
on c.id =o.customer_id ;

 

-- 각 주문에 대해 주문번호, 고객이름, 주문총액을 조회하세요.

SELECT o.id, c.customer_name ,o.total_amount 
FROM orders o 
join customers c 
on o.customer_id =c.id 
join order_items oi 
on oi.order_id =o.id ;

 

-- 제품 이름과 가격을 조회하세요.

SELECT product_name ,price 
FROM products p ;

 

-- 주문한 고객의 이름과 해당 주문에서 구매한 제품의 이름을 조회하세요.

SELECT o.id,c.customer_name , p.product_name 
FROM orders o 
join customers c 
on o.customer_id =c.id 
join order_items oi 
on oi.order_id =o.id 
join products p 
on oi.product_id =p.id

 

-- 주문 id 15에 포함된 제품 이름과 수량을 조회하세요.

SELECT p.product_name ,oi.quantity 
FROM orders o 
join order_items oi 
on oi.order_id =o.id 
join products p 
on oi.product_id =p.id
where o.id =15;

 

-- 총액 (total_amount)이 200만원 이상인 주문의 데이터를 가져와서

-- 고객 이름과 주문 총액(total_amount)을 조회하세요.

SELECT c.customer_name ,o.total_amount 
FROM orders o 
join customers c 
on c.id =o.customer_id 
where o.total_amount >=2000000;

 

-- 모든 주문에 대해 고객의 이름과 해당 고객이 주문한 제품 수량을 조회하세요.

SELECT c.customer_name ,oi.quantity 
FROM orders o 
join customers c 
on o.customer_id =c.id
join order_items oi 
on o.id =oi.order_id ;