[SQL 100제#67-74] UNION, UNION ALL, EXCEPT, IN, NOT IN

2021. 7. 29.·🤓 기술 학습 & 공부 기록/컴퓨터 일반

[강의명]
패스트캠퍼스 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;

 

쿼리 결과

데이터 형태가 잘 유지되면서 원하는 정보를 출력할 수 있다.

'🤓 기술 학습 & 공부 기록/컴퓨터 일반' 카테고리의 다른 글
  • [HTTP] 1-1. IP (인터넷 프로토콜)
  • [SQL 100제#75-80] ALL, ANY, EXISTS, NOT EXISTS
  • [SQL 100제#54-66] SUM, COUNT, ROUND, CASE WHEN, 서브쿼리
  • [SQL 100제#47-53] JOIN, LEFT JOIN, CASE
지식물원
지식물원
지식이 자라는 식물원!
  • 지식물원
    지식물원
    지식물원
  • 전체
    오늘
    어제
    • 분류 전체보기 (516) N
      • 🎨 프론트엔드 공부 (253) N
        • JS & TS (92) N
        • HTML & CSS (22)
        • React & Next (49)
        • Vue & Nuxt (22)
        • 기타 (68)
      • 🤓 기술 학습 & 공부 기록 (116)
        • Node.js (0)
        • Python (37)
        • 백엔드 (0)
        • 딥러닝 (1)
        • 컴퓨터 일반 (72)
        • 개발 인프라 (6)
      • 👨‍💻 프로젝트 경험 (6)
        • Work (0)
        • Toy (6)
      • ⚙️ 개발 팁 & 노하우 (21)
        • 프론트엔드 (6)
        • 기타 (15)
      • ☕️ 커리어 & 인터뷰 준비 (88)
        • 코딩 테스트 (88)
      • 📰 기술 트렌드 & 생각 정리 (4)
      • 📚 기타 (25)
        • 마케팅 (15)
        • 비개발서적 (10)
  • 블로그 메뉴

    • 태그
  • 링크

  • 공지사항

    • 모바일 접속 시 코드 하이라이팅 깨질 때
  • 인기 글

  • hELLO· Designed By정상우.v4.10.3
지식물원
[SQL 100제#67-74] UNION, UNION ALL, EXCEPT, IN, NOT IN
상단으로

티스토리툴바