SQL 7일차 23, 생각3

수강날짜 20-07-27

강의명 : 07 NATURAL 조인
강의명 : 생각대로 SQL - 3

NATURAL 조인

두개의 테이블에서 같은 이름을 가진 컴럼 간의 INNER 조인 집합 결과를 출력한다. SQL문 자체가 간소해 지는 방법이다.

실무에서는 잘 쓰이지 않는다.
그럼 왜 배우는가?

이걸 공부하면서 실무에서 가장 많이 쓰이는 INNER JOIN에 대해 깊게 배울수 있기 떄문이다.

예제 환경

CATEGORIES 테이블

PRODUCTS 테이블

실습

select *
  from PRODUCTS a 
natural join
	CATEGORIES b
;

이것을 INNER JOIN으로 표현하면 다음과 같다.

select	  A.CATEGORY_ID, A.PRODUCT_ID
	  	, A.PRODUCT_NAME, B.CATEGORY_NAME
  from PRODUCTS A 
 inner join
 	CATEGORIES B
    on (A.CATEGORY_ID = B.CATEGORY_ID);

NATURAL JOIN을 사용하면 가독성이 떨어진다.
그러므로 INNER JOIN으로 표현한 것이 좋다.

INNER JOIN도 생략이 가능하다.

select	  A.CATEGORY_ID, A.PRODUCT_ID
	  	, A.PRODUCT_NAME, B.CATEGORY_NAME
  from PRODUCTS A ,	CATEGORIES B
 where A.CATEGORY_ID = B.CATEGORY_ID;

실습2
NATURAL JOIN이 좋지 않은 이유
실습 테이블

city 테이블

country 테이블

두 테이블에는 Country_id가 같다. 그러므로 NATURAL JOIN이 가능하겠지?

select *
  from CITY a
natural join country b;

결과가 0개가 나왔다. 이런 일이 왜 벌어지는가?
두 테이블에 공통 속성은 country_id뿐만 아니라 last_update라는 속성도 둘이 동일하게 같고 있다.
이런 경우 city_id가 같으면서 last_update가 같은 경우를 찾기 때문에 결과값이 0개가 나온 것이다.

NATURAL 조인은 그러므로 실무에서 사용하지 않는다.
사용자의 의도와 다른 결과들이 나올 수 있기 떄문이다.

생각대로 SQL - 3

문제 풀이

문제1번) 고객의 기본 정보인, 고객 id, 이름, 성, 이메일과 함께 고객의 주소 address, district, postal_code, phone 번호를 함께 보여주세요. 	

select cus.customer_id, cus.first_name, cus.last_name, cus.email, addr.address, addr.district, addr.postal_code, addr.phone 
  from customer cus
 inner join address addr
    on cus.customer_id = addr.address_id;
 
문제2번) 고객의  기본 정보인, 고객 id, 이름, 성, 이메일과 함께 고객의 주소 address, district, postal_code, phone , city 를 함께 알려주세요. 	
select cus.customer_id, cus.first_name, cus.last_name, cus.email, addr.address, addr.district, addr.postal_code, addr.phone, ci.city 
  from customer cus
 inner join address addr
    on cus.customer_id = addr.address_id
 inner join city ci
    on addr.city_id = ci.city_id ; 
 
문제3번) Lima City에 사는 고객의 이름과, 성, 이메일, phonenumber에 대해서 알려주세요.	

select cus.first_name, cus.last_name, cus.email, addr.phone 
  from customer cus
 inner join address addr
 	on cus.customer_id = addr.address_id
 where addr.city_id in (
 			select city_id
 			  from city
 			 where city = 'Lima');
 

문제4번) rental 정보에 추가로, 고객의 이름과, 직원의 이름을 함께 보여주세요.
- 고객의 이름, 직원 이름은 이름과 성을  fullname 컬럼으로만들어서 직원이름/고객이름 2개의 컬럼으로 확인해주세요.	

select sta.first_name || ' ' || sta.last_name as 직원이름, cus.first_name || ' '|| cus.last_name as 고객이름
  from rental ren
 inner join customer cus
    on ren.customer_id = cus.customer_id
 inner join staff sta
	on ren.staff_id = sta.staff_id;


문제5번) seth.hannon@sakilacustomer.org 이메일 주소를 가진 고객의  주소 address, address2, postal_code, phone, city 주소를 알려주세요.	


select address, address2, postal_code, phone, city
  from address addr 
 inner join customer cus
    on addr.address_id = cus.address_id 
 inner join city ci
 	on ci.city_id = addr.city_id 
 where email = 'seth.hannon@sakilacustomer.org'; 


문제6번) Jon Stephens 직원을 통해 dvd대여를 한 payment 기록 정보를  확인하려고 합니다. 
      - payment_id,  고객 이름 과 성,  rental_id, amount, staff 이름과 성을 알려주세요. 	

select pay.payment_id, cus.first_name || ' ' || cus.last_name as customer_name, rental_id, amount, sta.first_name || ' ' || sta.last_name as staff_name
  from payment pay
 inner join staff sta
    on sta.staff_id  = pay.staff_id
 inner join customer cus
    on pay.customer_id  = cus.customer_id
 where sta.first_name = 'Jon'
   and sta.last_name  = 'Stephens';
      

문제7번) 배우가 출연하지 않는 영화의 film_id, title, release_year, rental_rate, length 를 알려주세요. 	

select fi.film_id, fi.title, fi.release_year, fi.rental_rate, fi.length 
  from film fi
  left join film_actor fa
    on fi.film_id = fa.film_id
 where fa.film_id is null ;
 
문제8번) store 상점 id별 주소 (address, address2, distict) 와 해당 상점이 위치한 city 주소를 알려주세요. 

select st.store_id, ad.address, ad.address2, ad.district, ci.city 
  from store st
 inner join address ad
    on st.address_id = ad.address_id
 inner join city ci
	on ad.city_id = ci.city_id ;


문제9번) 고객의 id 별로 고객의 이름 (first_name, last_name), 이메일, 고객의 주소 (address, district), phone번호, city, country 를 알려주세요. 	

select cu.customer_id, cu.first_name || ' ' || cu.last_name as 이름, cu.email, ad.address || ' ' || ad.district as 주소, ad.phone, ci.city, co.country 
  from customer cu
 inner join address ad
    on cu.address_id = ad.address_id
 inner join city ci
 	on ad.city_id = ci.city_id
 inner join country co
	on ci.country_id = co.country_id ;

문제10번) country 가 china 가 아닌 지역에 사는, 고객의 이름(first_name, last_name)과 , email, phonenumber, country, city 를 알려주세요	

select c.first_name || ' ' || c.last_name as 이름, c.email, a.phone, cn.country, ct.city 
  from customer c 
 inner join address a
    on c.address_id = a.address_id
 inner join city ct
 	on a.city_id = ct.city_id
 inner join country cn
 	on ct.country_id = cn.country_id
 where cn.country != 'china';

문제11번) Horror 카테고리 장르에 해당하는 영화의 이름과 description 에 대해서 알려주세요 	

select *
  from film
 inner join film_category
    on film.film_id  = film_category.film_id
 where category_id in( 
	select category_id 
 	  from category
 	 where name = 'Horror');

문제12번) Music 장르이면서, 영화길이가 60~180분 사이에 해당하는 영화의 title, description, length 를 알려주세요.
   -  영화 길이가 짧은 순으로 정렬해서 알려주세요. 	

select f.title, f.description, f.length 
  from film f
 inner join film_category fc
    on f.film_id = fc.film_id
 where fc.category_id in (
 		select category_id
 		  from category c 
 		 where c.name = 'Music')
 order by f.length;
 		)
 
 
문제13번) actor 테이블을 이용하여,  배우의 ID, 이름, 성 컬럼에 추가로    'Angels Life' 영화에 나온 영화 배우 여부를 Y , N 으로 컬럼을 추가 표기해주세요.  해당 컬럼은 angelslife_flag로 만들어주세요.	         

select a.actor_id , a.first_name, a.last_name , case when a.actor_id in (
		select actor_id
		  from film f
		 inner join film_actor fa 
			on f.film_id  = fa.film_id
		 where f.title ='Angels Life') then 'Y'
		 else 'N'
		 end as angelslife_flag
  from actor a;

문제14번) 대여일자가 2005-06-01~ 14일에 해당하는 주문 중에서 , 직원의 이름(이름 성) = 'Mike Hillyer' 이거나  고객의 이름이 (이름 성) ='Gloria Cook'  에 해당 하는 rental 의 모든 정보를 알려주세요.
 - 추가로  직원이름과, 고객이름에 대해서도  fullname 으로 구성해서 알려주세요.	

select r.*
  from rental r
  inner join customer c on r.customer_id = c.customer_id
  inner join staff s 	on r.staff_id = s.staff_id 
  where date(r.rental_date) between '2005-06-01' and '2005-06-14'
    and (
   		s.first_name || ' ' || s.last_name = 'Mike Hillyer'
   		or
   		c.first_name || ' ' || c.last_name = 'Gloria Cook'
   )
  
문제15번) 대여일자가 2005-06-01~ 14일에 해당하는 주문 중에서 , 직원의 이름(이름 성) = 'Mike Hillyer' 에 해당 하는 직원에게  구매하지 않은  rental 의 모든 정보를 알려주세요.
 - 추가로  직원이름과, 고객이름에 대해서도  fullname 으로 구성해서 알려주세요.	

select r.*
  from rental r
  inner join staff s 	on r.staff_id = s.staff_id 
  where date(r.rental_date) between '2005-06-01' and '2005-06-14'
  	and (s.first_name || ' ' || s.last_name != 'Mike Hillyer')

다음과 같이 풀이 하였다.