[SQL 100제#47-53] JOIN, LEFT JOIN, CASE

[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반

[수강목록]

3주차 과제 풀이 (SQL 39~53번 문제)

[강의내용]

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

문제 46번처럼, customer_id 별 주소 이므로 SELECT절에서 맨 앞에 customer_id를 써주는 것을 유의하면 된다.

SELECT c.customer_id, c.first_name, c.last_name, c.email,
	a.address, a.district, a.phone, ci.city, co.country 
FROM customer c 
JOIN address a ON c.address_id = a.address_id 
JOIN city ci ON a.city_id = ci.city_id 
JOIN country co ON ci.country_id = co.country_id;

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

지금까지 JOIN으로 만들어온 DB에서 country가 China가 아닌 정보를 출력하면 된다.

'~가 아니다'는 <> 또는 != 기호를 사용하면 된다.

SELECT c.first_name, c.last_name, c.email, a.phone, ci.city, co.country
FROM customer c 
JOIN address a ON c.address_id = a.address_id 
JOIN city ci ON a.city_id = ci.city_id 
JOIN country co ON ci.country_id = co.country_id
WHERE co.country <> 'China';

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

WHERE절에서 category 테이블의 name 컬럼이 Horror인 것을 스크린할 수 있다.

SELECT title, description
FROM film f 
JOIN film_category fc ON f.film_id = fc.film_id 
JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Horror';

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

WHERE절에서 스크린할 조건이 2개이고, ORDER BY를 통해 정렬까지만 해주면 된다.

SELECT f.title, f.description, f.length
FROM film f 
JOIN film_category fc ON f.film_id = fc.film_id 
JOIN category c ON fc.category_id = c.category_id 
WHERE c."name" = 'Music'
AND f.length BETWEEN 60 AND 180
ORDER BY f.length;

문제51번) actor 테이블을 이용하여, 배우의 ID, 이름, 성 컬럼에 추가로 'Angels Life' 영화에 나온 영화 배우 여부를
Y, N으로 컬럼을 추가 표기해주세요. 해당 컬럼은 angelslife_flag로 만들어주세요.

드디어 만만치 않은 문제가 나왔다. 이 문제에서 가장 주의할 점은 LEFT JOIN을 써야 한다는 점이다.

그냥 INNER JOIN을 사용하게 되면, 출연한 배우들이 Y, N 플래그가 모두 달려서, 총 행 수가 209행이 나오게 된다.

(전체 actor 수는 200)

따라서, LEFT JOIN을 사용해서 전체 actor 테이블에 플래그 컬럼을 얹는 느낌으로 접근해야 한다.

또한 서브쿼리를 사용해서 Angels Life 영화에 관한 정보만 출력하여 LEFT JOIN을 사용했다.

SELECT a.actor_id, a.first_name, a.last_name,
	CASE WHEN a.actor_id = feat_actor.actor_id THEN 'Y'
		ELSE 'N' 
	END AS angelslife_flag
FROM actor a
LEFT JOIN (
	SELECT f.film_id, f.title, fa.actor_id 
	FROM film_actor fa
	JOIN film f ON fa.film_id = f.film_id
	WHERE f.title = 'Angels Life'
	) AS feat_actor ON a.actor_id = feat_actor.actor_id;

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

rental_date 컬럼이 타임스탬프 타입이므로, 날짜까지만 비교하기 위해 date함수를 사용해준다.

또한 A와(B또는C) 조건을 구현하기 위해 괄호 안에 두 조건과 OR을 넣어주어야 한다.

SELECT r.*, s.first_name || ' ' || s.last_name AS staff_fullname,
	c.first_name || ' ' || c.last_name AS cust_fullname
FROM rental r
	JOIN staff s ON r.staff_id = s.staff_id
	JOIN customer c ON r.customer_id = c.customer_id
WHERE date(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');

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

SELECT r.*, s.first_name || ' ' || s.last_name AS staff_fullname,
	c.first_name || ' ' || c.last_name AS cust_fullname
FROM rental r
	JOIN staff s ON r.staff_id = s.staff_id
	JOIN customer c ON r.customer_id = c.customer_id
WHERE date(rental_date) BETWEEN '2005-06-01' AND '2005-06-14'
	AND s.first_name || ' ' || s.last_name != 'Mike Hillyer';

티스토리툴바