DB/SQL 예제

join 예제 3

ssury94 2024. 12. 3. 17:11

외래 키 설정

더보기

-- customers table data

INSERT INTO customers (customer_name, email, address) VALUES

('김철수', 'kimchulsoo@example.com', '서울시 강남구'),

('이영희', 'leeyounghee@example.com', '서울시 마포구'),

('박준혁', 'parkjunhyuk@example.com', '경기도 수원시'),

('최수진', 'choisujin@example.com', '부산시 해운대구'),

('정우진', 'jungwoojin@example.com', '대구시 수성구'),

('한지수', 'hanjisu@example.com', '서울시 성동구'),

('김지훈', 'kimjihun@example.com', '인천시 남동구'),

('이민영', 'leeminyoung@example.com', '경기도 고양시'),

('장서현', 'jangseohyun@example.com', '서울시 종로구'),

('박다연', 'parkdayeon@example.com', '전라북도 전주시'),

('윤성민', 'yoonseongmin@example.com', '강원도 원주시'),

('신예린', 'shinyeorin@example.com', '서울시 관악구'),

('오지훈', 'ohjihoon@example.com', '서울시 구로구'),

('김서준', 'kimseojun@example.com', '경기도 파주시'),

('이하은', 'leehaneun@example.com', '서울시 동작구'),

('문찬영', 'moonchanyeong@example.com', '경기도 용인시'),

('임수연', 'imsuyeon@example.com', '울산시 남구'),

('유진우', 'yoojinu@example.com', '서울시 서초구'),

('정하영', 'jeonghayoung@example.com', '경기도 성남시'),

('최민준', 'choiminjun@example.com', '서울시 송파구');

 

-- orders table data (reflecting multiple purchases by some users and no purchases by others)

INSERT INTO orders (customer_id, order_date, total_amount) VALUES

(1, '2023-10-01', 2500000),

(2, '2023-10-03', 1800000),

(1, '2023-10-10', 2200000),

(3, '2023-10-12', 3000000),

(4, '2023-10-15', 800000),

(2, '2023-10-18', 1400000),

(5, '2023-10-20', 1800000),

(6, '2023-10-25', 1600000),

(1, '2023-10-27', 1900000),

(3, '2023-10-29', 2000000),

(7, '2023-11-01', 2200000),

(8, '2023-11-05', 2500000),

(9, '2023-11-08', 800000),

(10, '2023-11-11', 1300000),

(2, '2023-11-15', 1500000),

(4, '2023-11-20', 1700000),

(11, '2023-11-22', 2000000),

(12, '2023-11-25', 2100000),

(6, '2023-11-28', 1800000),

(8, '2023-11-30', 1600000);

 

-- products table data

INSERT INTO products (product_name, price) VALUES

('노트북', 1200000),

('스마트폰', 800000),

('태블릿', 600000),

('헤드폰', 200000),

('이어폰', 100000),

('스마트워치', 300000),

('모니터', 700000),

('마우스', 50000),

('키보드', 80000),

('USB 메모리', 20000),

('외장하드', 100000),

('파워뱅크', 150000),

('충전기', 40000),

('케이블', 10000),

('프린터', 500000),

('책상', 100000),

('의자', 200000),

('모니터 받침대', 50000),

('스피커', 300000),

('마이크', 60000);

 

-- order_items table data (reflecting multiple items per order)

INSERT INTO order_items (order_id, product_id, quantity) VALUES

(1, 1, 1),

(1, 2, 2),

(2, 3, 1),

(2, 4, 3),

(3, 5, 2),

(3, 1, 1),

(4, 6, 2),

(5, 7, 1),

(5, 3, 1),

(6, 8, 2),

(7, 9, 4),

(8, 10, 1),

(9, 1, 1),

(9, 6, 1),

(10, 2, 2),

(10, 5, 1),

(11, 3, 3),

(12, 4, 1),

(13, 5, 1),

(14, 8, 2),

(15, 1, 2),

(15, 5, 1),

(16, 2, 3),

(16, 3, 1),

(17, 4, 2),

(17, 6, 1),

(18, 7, 1),

(18, 8, 4),

(19, 9, 2),

(20, 3, 2),

(20, 5, 3);

 

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

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

 

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

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

 

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

SELECT product_name ,price 
FROM products p ;

 

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

SELECT o.id order_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 ;

 

-- 각 주문에 포함된 총 제품 수량을 조회하세요. = 각 주문별 총 제품수량

SELECT o.id , sum(oi.quantity) 
FROM orders o 
join customers c 
on o.customer_id =c.id
join order_items oi 
on o.id =oi.order_id
group by o.id;

 

-- 제품 이름과 해당 제품이 포함된 주문의 총 주문 횟수를 조회하세요.

SELECT p.product_name ,count(*)
FROM orders o 
join order_items oi
on o.id=oi.order_id 
JOIN products p 
on p.id =oi.product_id 
group by p.product_name ;

 

-- 2023년 10월에 주문한 고객이름과 주문 총액을 조회하세요.

SELECT c.customer_name ,o.total_amount 
FROM orders o 
join customers c 
on o.customer_id=c.id 
WHERE year(order_date)=2023 and month(order_date)=10 ;

 

-- 2단계 문제

-- 2023년 10월에 구매한 고객의 이름과 주문 횟수를 조회하세요.

SELECT c.customer_name ,count(*) cnt
FROM orders o 
join customers c 
on o.customer_id=c.id 
WHERE year(order_date)=2023 and month(order_date)=10
group by c.id;

 

-- 각 고객별로 총 주문금액(total_amount의 sum)과 고객 이름이 나오도록 조회하세요.

-- 단 고객별 총 주문금액이 200만원 이상인 고객만 나오도록 하세요.

SELECT c.customer_name , sum(o.total_amount) sum_amount
FROM customers c 
join orders o 
on o.customer_id =c.id 
GROUP by c.id 
HAVING sum_amount>=2000000;

 

-- 각 고객이 구매한 총 제품 수량을 조회하세요. 결과는 고객 이름별로 정렬

SELECT c.customer_name ,sum(oi.quantity) sum_quntity
FROM customers c 
join orders o 
on c.id =o.customer_id 
join order_items oi 
on o.id =oi.order_id 
group by c.id
order by c.customer_name ;

 

-- ★가장 많이 주문된 제품의 이름과 총 주문 수량을 조회하세요.

 

SELECT p.product_name ,sum(oi.quantity)as sum_quantity
FROM products p 
join order_items oi 
on p.id =oi.product_id 
group by p.id 
-- 서브쿼리
SELECT max(sum_quantity)
FROM (SELECT p.product_name ,sum(oi.quantity)as sum_quantity
		FROM products p 
		join order_items oi 
		on p.id =oi.product_id 
		group by p.id ) sub_table;
-- 서브쿼리	
SELECT *
FROM (SELECT p.product_name ,sum(oi.quantity)as sum_quantity
	  FROM products p 
	  join order_items oi 
	  on p.id =oi.product_id 
	  group by p.id ) sub_table
where sum_quantity = (select  max( sum_quantity )
					  from (SELECT p.product_name  ,  sum( oi.quantity ) sum_quantity
						    from products p
							join order_items oi 
							on p.id = oi.product_id
							group by p.id   ) sub_table );

 

 

 

 

-- 각 주문에 대해 고객 이름, 주문일, 해당 주문에서 구매한 제품의 총 금액을 조회

SELECT o.id, c.customer_name , o.order_date ,o.total_amount

FROM orders o

join customers c

on o.customer_id =c.id

JOIN order_items oi

on o.id =oi.order_id ;

 

-- ★주문한 제품 종류가 3개 이상인 고객의 이름과 해당 고객의 주문 횟수를 조회하세요.

SELECT c.customer_name ,count(distinct o.id)
FROM order_items oi 
join orders o 
on oi.order_id = o.id 
join customers c 
on o.customer_id =c.id 
group by c.id 
having count(distinct oi.product_id )>=3;

 

-- 2023년 10월에 주문한 제품 이름과 총 판매 수량을 조회하세요.

SELECT p.product_name , sum(oi.quantity)as sum_quantity
FROM orders o 
join order_items oi 
on o.id =oi.order_id 
join products p 
on p.id =oi.product_id 
WHERE year(order_date)=2023 and month(order_date)=10
group by oi.product_id ;

 

-- 가장 많이 구매한 고객 이름과 해당 고객이 구매한 총 제품 수량을 조회하세요.

SELECT c.customer_name , sum(oi.quantity)as sum_quantity
FROM customers c 
join orders o ON o.customer_id =c.id
join order_items oi on o.id =oi.order_id 
group by c.id ;
-- 서브쿼리
SELECT max(sum_quantity)
FROM (SELECT c.customer_name , sum(oi.quantity)as sum_quantity
		FROM customers c 
		join orders o ON o.customer_id =c.id
		join order_items oi on o.id =oi.order_id 
		group by c.id )as sub_table;
-- 서브쿼리
SELECT *
FROM (SELECT c.customer_name , sum(oi.quantity)as sum_quantity
		FROM customers c 
		join orders o ON o.customer_id =c.id
		join order_items oi on o.id =oi.order_id 
		group by c.id )as sub_table
where sum_quantity=(select max(sum_quantity)
					FROM (SELECT c.customer_name , sum(oi.quantity)as sum_quantity
					FROM customers c 
					join orders o ON o.customer_id =c.id
					join order_items oi on o.id =oi.order_id 
					group by c.id )as sub_table);

 

 

 

-- 고객별로 구매한 제품의 종류 수와 총 구매수량을 조회하세요.

SELECT c.id, count(*), count(oi.quantity)

FROM customers c

join orders o ON c.id =o.customer_id

join order_items oi on o.id =oi.order_id

group by c.id ;

 

-- 2023년 11월에 2회 이상 주문한 고객이름과 주문횟수 조회

SELECT c.customer_name ,count(*)

FROM orders o

join customers c

on o.customer_id =c.id

where year(order_date )=2023 and MONTH (order_date )=11

group by c.id

HAVING count(*)>=2;

 

-- 2023년에 주문한 각 고객별, 그 고객의 주문 중 가장 높은 주문금액 조회

SELECT c.id,c.customer_name ,Max(o.total_amount)

FROM orders o

join customers c

on o.customer_id =c.id

where YEAR (order_date)=2023

group by c.id ;

 

-- 제품 종류를 2개 이상 구매한 주문만 조회하고,

-- 해당 주문의 고객이름, 주문일, 총 금액을 출력하세요.

SELECT c.customer_name ,o.order_date ,o.total_amount

FROM order_items oi

join orders o

on o.id =oi.order_id

join customers c

on c.id =o.customer_id

group by order_id

HAVING count(*) >=2;

 

-- 2023년에 주문 횟수가 가장 많은 고객의 이름과 해당 고객의 총 주문횟수를 조회하세요.

SELECT c.customer_name ,count(*)

FROM orders o

join customers c

on o.customer_id =c.id

group by customer_id

order by count(*) desc;

 

SELECT c.customer_name ,count(*)

FROM orders o

join customers c

on o.customer_id =c.id

group by customer_id

HAVING count(*)=3;

 

-- 고객아이디 1000, order_date는 2024-11-12 토탈금액 200000원

-- orders 데이터에 추가하세요.

 

INSERT INTO orders (customer_id,order_date,total_amount)

values (1000,'2024-11-12',200000);

-- 외래키 설정때문에 들어가지지 않음. customers id에 1000이 없기 때문에

 

-- 2023년 주문 고객 중 가장 많은 금액을 소비한 "큰손" 고객 순으로

-- 고객이름, 해당 고객의 총 주문 금액을 조회하세요.

SELECT c.customer_name ,sum(o.total_amount) '큰손'

FROM customers c

join orders o

on o.customer_id =c.id

group by c.id

order by '큰손' desc;

 

-- 모든 고객의 이름과 이메일 조회하고, 각 고객의 가장 최근 주문일을 함께 표시하세요.

SELECT c.customer_name ,c.email, max(o.order_date)

FROM customers c

join orders o

on o.customer_id = c.id

group by c.id ;

 

-- 고객의 이름과 주소를 표시하되, 특정 고객이 주문한 적이 없으면 '주문없음'으로 표시하세요.

SELECT c.customer_name ,c.address ,if(o.id is null,'주문없음','있음')

FROM customers c

left join orders o

on c.id =o.customer_id ;

 

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

-- 주문총액이 200만원 이상이면 '고액주문'으로 표시

-- 그렇지않으면 '일반주문'으로 표시

SELECT c.customer_name ,o.id ,o.total_amount ,if(o.total_amount >=2000000,'고액주문','일반주문')

FROM orders o

join customers c

on o.customer_id =c.id ;