[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
5주차 과제 풀이 (SQL 67~74번 문제)
[강의내용]
문제67번) 180분 이상 길이의 영화에 출연하거나, rating이 R인 영화에 출연한 배우에 대해서 배우 ID와
(180분 이상 / R등급 영화)에 대한 Flag컬럼을 알려주세요.
- 1) film_actor 테이블과 film 테이블을 이용하세요.
- 2) union, unionall, intersect, except 중 상황에 맞게 사용해주세요.
- 3) actor_id가 동일한 flag값이 여러개 나오지 않도록 해주세요.
WHERE절을 통해 필터링을 쉽게 할 수 있지만 플래그 컬럼을 생성해야하기 때문에 가장 쉬운 방법인 UNION을 쓴다
180분 이상 영화에 출연한 actor_id를 추출하고, 플래그를 SELECT절에 기재해준다.
그리고 R등급 영화에 출연한 actor_id를 추출하고, 중복이 없어야 하므로 UNION으로 연결해준다.
SELECT DISTINCT a.actor_id, 'over_180m' AS flag
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.length >= 180
UNION
SELECT DISTINCT a.actor_id, 'R' AS flag
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.rating = 'R';
문제68번) R등급 영화에 출연한 배우이면서, 동시에, Alone Trip에 출연한 배우의 ID 를 확인해주세요.
- 1) film_actor 테이블와 film 테이블을 이용하세요.
- 2) union, unionall, intersect, except 중 상황에 맞게 사용해주세요.
플래그 생성이 없으므로 조건에 맞게 추출한 다음 UNION으로 연결하면 된다.
SELECT DISTINCT a.actor_id, a.first_name, a.last_name
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.film_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.rating ='R'
UNION
SELECT DISTINCT a.actor_id, a.first_name, a.last_name
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.film_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.title = 'Alone Trip';
문제69번) G 등급에 해당하는 필름을 찍었으나, 영화를 20편 이상 찍지 않은 영화배우의 ID를 확인해주세요.
- 1) film_actor 테이블과 film 테이블을 이용하세요.
- 2) union, unionall, intersect, except 중 상황에 맞게 사용해주세요.
이번에는 EXCEPT를 활용해본다.
G등급 영화 출연배우를 뽑고, 여기서 영화를 20편 이상 찍은 배우들을 EXCEPT로 빼주면 된다.
SELECT DISTINCT fa.actor_id
FROM film_actor fa
JOIN film f ON fa.film_id = f.film_id
WHERE f.rating ='G'
EXCEPT
SELECT fa.actor_id
FROM film_actor fa
GROUP BY fa.actor_id
HAVING count(fa.film_id) >= 20;
문제70번) 필름 카테고리가 Action, Animation, Horror에 해당하지 않는 필름 아이디를 알려주세요.
NOT IN 조건을 이용해서 3개의 카테고리에 해당하지 않는 영화들을 추출할 수 있다.
SELECT fc.film_id, c."name"
FROM film_category fc
JOIN category c ON fc.category_id = c.category_id
WHERE c.name NOT IN ('Action', 'Animation', 'Horror');
문제71번) Staff_id, 이름, 성에 대한 데이터와, Customer의 id, 이름, 성에 대한 데이터를 하나의 데이터셋의 형태로 보여주세요.
- 컬럼 구성 : id, 이름, 성, flag (직원/고객여부)로 구성해주세요.
하나의 데이터셋 형태로 보여주려면 크게 JOIN과 UNION으로 나눠서 살펴볼 수 있지만
직원, 고객 데이터를 전부 표시하기 위해 UNION ALL을 사용한다.
SELECT staff_id, first_name, last_name, 'staff' AS flag
FROM staff s
UNION ALL
SELECT customer_id, first_name, last_name, 'customer' AS flag
FROM customer c;
문제72번) 직원과 고객의 이름이 동일한 사람이 있나요? 있다면, 해당 사람의 이름과 성을 알려주세요.
직원은 단 2명으로 수가 적기 때문에, 고객 데이터를 기준으로, 직원의 이름과 같은 고객 정보를 뽑아본다.
SELECT c.first_name, c.last_name
FROM customer c
JOIN staff s ON c.store_id = s.store_id
WHERE c.first_name = s.first_name;
문제73번) 반납이 되지 않은 대여점(store)별 영화 재고(inventory)와 전체 영화 재고를 같이 구하세요. (union all 사용)
반납이 되지 않은 영화는 대여일은 있으나, 반납일은 없는 영화이다.
rental 테이블에 대여일, 반납일 컬럼이 존재한다.
그리고 전체 재고도 구해준 다음, UNION ALL을 사용해주면 된다.
SELECT s.store_id, count(*)
FROM rental r
JOIN staff s ON r.staff_id = s.staff_id
WHERE r.rental_date IS NOT NULL
AND r.return_date IS NULL
GROUP BY s.store_id
UNION ALL
SELECT i.store_id, count(*)
FROM inventory i
GROUP BY i.store_id;
문제74번) 국가(country)별 도시(city)별 매출액, 국가(country)별 매출액 소계 그리고 전체 매출액을 구하세요. (union all)
구해야할 데이터의 형태는 국가이름 - 도시이름 - 매출액 의 형태로 나타나면 된다.
국가별, 도시별 매출액은 형태에 맞춰서 구할 수 있지만, 국가별 매출액에는 도시이름이 공란이어야 한다.
마찬가지로 전체 매출액은 국가이름, 도시이름이 공란이어야 한다.
SELECT절에 NULL as '컬럼이름'의 형태로 데이터의 형태를 지정해서 UNION ALL을 가능하게 해줄 수 있다.
SELECT co.country, ci.city, sum(p.amount)
FROM payment p
JOIN customer c ON p.customer_id = c.customer_id
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
GROUP BY co.country, ci.city
UNION ALL
SELECT co.country, NULL AS city, sum(p.amount)
FROM payment p
JOIN customer c ON p.customer_id = c.customer_id
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
GROUP BY co.country
UNION ALL
SELECT NULL AS country, NULL AS city, sum(p.amount)
FROM payment p;
데이터 형태가 잘 유지되면서 원하는 정보를 출력할 수 있다.