[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
생각대로 SQL - 6
[강의내용]
문제1번) 매출을 가장 많이 올린 dvd 고객 이름은? (subquery 활용)
customer_id별 sum(amount)를 구하고 이를 customer 테이블과 연결해 이름을 구해주면 된다.
[내 답안 (인라인 뷰)]
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c, (
SELECT customer_id, sum(amount)
FROM payment p
GROUP BY customer_id
ORDER BY sum DESC
LIMIT 1) AS t1
WHERE t1.customer_id = c.customer_id;
이 문제는 WHERE절에 서브쿼리를 사용한 중첩 서브쿼리로도 해결할 수 있다.
[모범답안 (중첩 서브쿼리)]
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM payment
GROUP BY customer_id
ORDER BY sum(amount) DESC
LIMIT 1
);
결과는 동일하다.
차이점은, = 대신 IN을 씀, ORDER BY절에만 sum(acount) 쓰고 select 에는 생략해준 것 뿐이다.
문제2번) 대여가 한번도이라도 된 영화 카테고리 이름을 알려주세요. (쿼리는, Exists조건을 이용하여 풀어봅시다)
대여가 한번이라도 된 영화 -> rental_id가 있는 film_id
SELECT c.category_id, c.name
FROM category c
WHERE EXISTS (
SELECT 1
FROM rental r, inventory i, film_category fc
WHERE r.inventory_id = i.inventory_id
AND i.film_id = fc.film_id
AND fc.category_id = c.category_id
);
서브쿼리에서 메인쿼리의 컬럼을 사용할 수 있으므로 서브쿼리에 caregory 컬럼을 FROM하지 않고 WHERE절에 적었다
모든 카테고리가 대여 이력이 있는 것으로 조회된다.
문제3번) 대여가 한번도이라도 된 영화 카테고리 이름을 알려주세요. (쿼리는, Any 조건을 이용하여 풀어봅시다)
2번과 같은 문제이다. 이번에는 ANY 조건을 이용해서 풀어보자
SELECT c.category_id, c.name
FROM category c
WHERE c.category_id = ANY (
SELECT fc.category_id
FROM rental r, inventory i, film_category fc
WHERE r.inventory_id = i.inventory_id
AND i.film_id = fc.film_id
AND fc.category_id = c.category_id
);
결과는 동일하다.
EXISTS에서는 메인쿼리의 WHERE절과 서브쿼리의 SELECT절에 컬럼 이름이 명시되지 않았지만,
ANY 조건에서는 양쪽의 컬럼 이름을 명시해주어야 한다.
문제5번) dvd 대여를 제일 많이한 고객 이름은? (subquery 활용)
customer_id 별로 GROUP BY한 다음 count(rental_id)를 구해준다.
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (
SELECT customer_id
FROM rental
GROUP BY customer_id
ORDER BY count(rental_id) DESC
LIMIT 1
);
문제6번) 영화 카테고리값이 존재하지 않는 영화가 있나요?
전체 film_id 중에서 film_category 테이블에 없는 film_id를 구하면 된다.
NOT IN을 쓸 수도 있고, NOT EXISTS를 쓸 수도 있다.
[NOT IN 사용]
SELECT *
FROM film
WHERE film_id NOT IN (
SELECT film_id
FROM film_category
);
쿼리 결과가 없다 (모든 film_id에 category_id가 존재한다)
[NOT EXISTS]
SELECT *
FROM film f
WHERE NOT EXISTS (
SELECT 1
FROM film_category fc
WHERE fc.film_id = f.film_id
);
결과는 동일하다.
* NOT IN에는 null값 미포함 (null값은 조회 불가)
* NOT EXISTS에는 null값 포함 (null값도 조회)