SQL 9일차 생각4, 36,37

수강날짜 20-07-29

강의명 : 생각대로 SQL-4
강의명 : 01. UNION 연산
강의명 : 02. UNIONALL 연산

생각대로 SQL-4

생각대로 SQL-4의 문제들을 한 번 풀어보고 강의랑 비교해보았다.

문제1번) store 별로 staff는 몇명이 있는지 확인해주세요.	

select store_id, count(*)
 from store s
group by store_id;

문제2번) 영화등급(rating) 별로 몇개 영화film을 가지고 있는지 확인해주세요.	

select rating, count(*)
 from film
group by rating;


문제3번) 출현한 영화배우(actor)가  10명 초과한 영화명은 무엇인가요?	

select title 
 from film
where film_id in (
	select fa.film_id
	  from film_actor fa
	 group by film_id
	having count(*) > 10
	);


문제4번) 영화 배우(actor)들이 출연한 영화는 각각 몇 편인가요?  
- 영화 배우의 이름 , 성 과 함께 출연 영화 수를 알려주세요.	

select a.first_name, a.last_name, count(*) 
 from actor a, film_actor fa 
where fa.actor_id = a.actor_id
group by a.actor_id 

문제5번) 국가(country)별 고객(customer) 는 몇명인가요?	

select co.country ,sum(cnt)
from
(select a.city_id, count(*) cnt
 from address a
group by a.city_id) as ac, city c, country co
where ac.city_id = c.city_id
 and c.country_id = co.country_id
group by co.country_id;


select co.country, sum(cnt)
 from (select a.city_id, count(*) cnt
 		  from address a
		 group by a.city_id) as ac
inner join city c on ac.city_id = c.city_id
inner join country co on c.country_id = co.country_id
group by co.country_id;


문제6번) 영화 재고 (inventory) 수량이 3개 이상인 영화(film) 는? 
- store는 상관 없이 확인해주세요.	

select film_id, count(*) 
 from inventory i
group by film_id
having count(*)>=3;


문제7번) dvd 대여를 제일 많이한 고객 이름은?	 

select c.first_name, count(*)
 from customer c
inner join rental r on c.customer_id = r.customer_id 
group by c.customer_id
order by count(*) desc
limit 1;

문제8번) rental 테이블을  기준으로,   2005년 5월26일에 대여를 기록한 고객 중, 하루에 2번 이상 대여를 한 고객의 ID 값을 확인해주세요.	

select customer_id, count(*)
 from rental r 
where date(r.rental_date) = '2005-05-26'
group by customer_id 
having count(*) >=2;


문제9번) film_actor 테이블을 기준으로, 출현한 영화의 수가 많은  5명의 actor_id 와 , 출현한 영화 수 를 알려주세요.	

select fa.actor_id , count(*) 
 from film_actor fa
group by fa.actor_id
order by count(*) desc
limit 5;

문제10번) payment 테이블을 기준으로,  결제일자가 2007년2월15일에 해당 하는 주문 중에서  ,  하루에 2건 이상 주문한 고객의  총 결제 금액이 10달러 이상인 고객에 대해서 알려주세요.  
(고객의 id,  주문건수 , 총 결제 금액까지 알려주세요)	

select customer_id, count(*), sum(amount)
 from payment p
where date(p.payment_date) = '2007-02-15'
group by customer_id
having count(*)>=2
  and sum(amount) >= 10; 

문제11번) 사용되는 언어별 영화 수는?	

select l.name, count(*)
 from film f 
inner join language l ON f.language_id =l.language_id 
group by l.language_id 
 
문제12번) 40편 이상 출연한 영화 배우(actor) 는 누구인가요?	

select a.first_name || ' '|| a.last_name as name, count(*)
 from film_actor fa
inner join actor a on a.actor_id = fa.actor_id 
group by a.actor_id
having count(*) >= 40 
;

문제13번) 고객 등급별 고객 수를 구하세요. (대여 금액 혹은 매출액  에 따라 고객 등급을 나누고 조건은 아래와 같습니다.)
/*
A 등급은 151 이상 
B 등급은 101 이상 150 이하 
C 등급은   51 이상 100 이하
D 등급은   50 이하

* 대여 금액의 소수점은 반올림 하세요. 

HINT
반올림 하는 함수는 ROUND 입니다.	
*/

select 등급, count(*)
 from (  select customer_id, case
				when round(sum(amount)) >= 151 then 'A' 
				when round(sum(amount)) BETWEEN 101 and 150 then 'B'
				when round(sum(amount)) BETWEEN 51 and 100 then 'C'
				when round(sum(amount)) <= 50 then 'D'
		 		 end as 등급
		    from payment p 
		   group by p.customer_id ) as a
group by 등급
order by 등급

강의와 비교하여 좀더 좋은 것도 있고 부족한 것도 있지만 어떠한 것이 더 좋은 코드인지 아직은 모르겠다.

UNION 연산

두 개 이상의 SELECT 문들의 결과 집합을 단일 결과 집합으로 결합하며 결합시 중복된 데이터는 제거 된다.

UNION연산 문법

select 
	  COLUMN_1_1
	, COLUMN_1_2
  from TABLE_NAME_1
 union
select
	  COLUMN_2_1
	, COLUMN_2_2
  from TABLE_NAME_2;

• 두개의 SELECT문에서 중복되는 데이터 값이 있다면 중복은 제거 된다.
• ORDER BY로 정렬하고자 할 경우 맨 마지막 SELECT문에 ORDER BY절을 사용한다.

위에거를 A 집합이라고 하고 밑에거를 B 집합이라고 할 때 UNION은 두 집합을 합치는 것이다.

예제 테이블

SALE2007_1

SALE2007_2

예제
SALE2007_1과 SALE2007_2를 합친 결과를 보여라

select *
  from sale2007_1
 union
select *
  from sale2007_2;

중복되는 값이 (Mary 100,000) 하나가 생략되었다.

ORDER BY관련!

select *
  from sale2007_1
 union
select *
  from sale2007_2
 order by amount DESC;

ORDER BY는 맨 마지막 SELECT문에 기재 해야 한다.

UNION ALL 연산

두개 이상의 SELECT문들의 결과 집합을 단일 결과 집합으로 결합하며 결합 시 중복된 데이터도 모두 출력한다.

예제

select *
  from sale2007_1
 union all
select *
  from sale2007_2;

(Mary, 100,000)이 중복 되었지만 사라지지 않음을 확인 할 수 있다.

UNION ALL VS UNION
실무에서는 UNION ALL이 더 많이 사용된다! 사용자에 의도와 상관없이 자동으로 삭제되는 것을 사용자는 원하지 않는다. 그러므로 중복 제거는 DISTINCT로 명시하고 UNION ALL을 사용할 것!

실습 - ORDER BY

select *
  from sale2007_1
 union all
select *
  from sale2007_2
  order by amount DESC;
  

이것도 ORDER BY가 맨 마지막 SELECT 문에 삽입되는 것을 확인 할 수 있다.