[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
생각대로 SQL - 4
[강의내용]
문제2번) 영화등급(rating) 별로 몇개의 film을 가지고 있는지 확인해주세요.
SELECT rating, count(film_id) AS count
FROM film
GROUP BY rating;
문제4번) 영화 배우(actor)들이 출연한 영화는 각각 몇 편인가요?
- 영화 배우의 이름 , 성 과 함께 출연 영화 수를 알려주세요.
actor 테이블에서 배우들의 이름, 성을 가져와야 하고, film_actor 테이블에서 actor_id 별 film_id를 count하면 될 것 같다.
GROUP BY를 써야 하는데 actor 테이블에서 이름, 성을 가져와야 하므로 서브쿼리를 이용해 한번에 쓸 수 없는 문제를 해결한다.
먼저 film_actor 테이블을 살펴보면,
GROUP BY를 써서 서브쿼리에 들어갈 가공의 테이블을 만든다.
SELECT f.actor_id, count(f.film_id) AS count
FROM film_actor f
GROUP BY f.actor_id
actor_id 별로 film_id를 count 했다. 이제 이 테이블에 film_count라는 이름을 붙이고 괄호에 넣는다.
이제 가공한 film_count 테이블과 actor 테이블을 결합해야 하는데, JOIN 대신 FROM절에 테이블 2개를 불러오고, WHERE절을 사용해 연결하면 더 간결한 쿼리가 될 것 같다.
[내 쿼리]
SELECT a.first_name, a.last_name, film_count.count
FROM actor a,
(SELECT f.actor_id, count(f.film_id) AS count
FROM film_actor f
GROUP BY f.actor_id) AS film_count
WHERE a.actor_id = film_count.actor_id;
[모범답안]
SELECT d.*, a.first_name, a.last_name
FROM (
SELECT f.actor_id, count(DISTINCT f.film_id) cnt
FROM film_actor f
GROUP BY f.actor_id
) AS d
LEFT JOIN actor a ON d.actor_id = a.actor_id;
* 쿼리 결과는 동일하다 (모범 답안에는 문제에서 요구하지 않은 actor_id 컬럼이 들어가 있다)
차이점은,
1. WHERE절 썼냐 JOIN썼냐
2. 모범답안에서는 film_id 중복값을 제거하기 위해 distinct를 걸어줌
(내 생각에는 굳이 distinct 안써도 될듯 함. 결과는 똑같음)
문제5번) 국가(country)별 고객(customer) 는 몇명인가요?
customer 테이블과 country 테이블을 사용하려면 city 테이블, address 테이블, customer 테이블이 있어야 한다.
즉, 3차례의 JOIN이 필요하다.
SELECT c.country, count(cu.customer_id)
FROM country c
JOIN city ci ON c.country_id = ci.country_id
JOIN address a ON ci.city_id = a.city_id
JOIN customer cu ON a.address_id = cu.address_id
GROUP BY c.country;
문제8번) rental 테이블을 기준으로, 2005년 5월26일에 대여를 기록한 고객 중, 하루에 2번 이상 대여를 한 고객의 ID 값을 확인해주세요.
rental_date를 2005-05-26에 고정하여 쿼리를 작성하려 했으나 문제가 있다.
rental 테이블을 보면, rental_date는 시각까지 나와있는 데이터이다.
이 컬럼을 date함수를 통해 년월일까지로 묶어버리면, 하루의 각기 다른 고객이 구매한 이력들이 통합되어 버린다.
즉, rental_date는 다양한 customer_id를 갖고 있어야 하는데, date로 통합해버리면 이것들이 다 뭉쳐져버린다.
그래서, 2005-05-26을 시분초까지로 나눠 BETWEEN에 넣는다.
SELECT r.customer_id, count(rental_id)
FROM rental r
WHERE rental_date BETWEEN '2005-05-26 00:00:00' AND '2005-05-26 23:59:59'
GROUP BY customer_id
HAVING count(rental_id) >= 2;
WHERE절에서 이미 한 번 필터링을 했기 때문에 추가적인 필터링은 HAVING절을 이용한다.
문제9번) film_actor 테이블을 기준으로, 출현한 영화의 수가 많은 5명의 actor_id 와 , 출현한 영화 수 를 알려주세요.
SELECT actor_id, count(film_id)
FROM film_actor fa
GROUP BY actor_id
ORDER BY count DESC
LIMIT 5;
문제13번) 고객 등급별 고객 수를 구하세요. (대여 금액 혹은 매출액에 따라 고객 등급을 나누고 조건은 아래와 같습니다.) + 매출액은 반올림(round 함수)
A 등급은 151 이상
B 등급은 101 이상 150 이하
C 등급은 51 이상 100 이하
D 등급은 50 이하
일단 payment 테이블에서 customer_id 별로 amount를 sum 해서 고객별 결제액을 구해본다.
SELECT customer_id, round(sum(amount)) AS sum
FROM payment
GROUP BY customer_id;
여기에 등급 컬럼을 오른쪽에 추가해본다.
SELECT customer_id, round(sum(amount)) sum_amount,
CASE WHEN round(sum(amount)) >= 151 THEN 'A'
WHEN round(sum(amount)) BETWEEN 101 AND 150 THEN 'B'
WHEN round(sum(amount)) BETWEEN 51 AND 100 THEN 'C'
WHEN round(sum(amount)) <= 50 THEN 'D'
ELSE 'Empty'
END AS level
FROM payment
GROUP BY customer_id;
CASE 절에서 WHEN 조건만 추가할 수 있고 ELSE 조건은 마지막에 딱 1개만 쓸 수 있음에 주의한다.
그래서 범위로 BETWEEN으로 지정했다.
이 가공의 테이블에 t1이라는 이름을 붙여 SELECT절에서 COUNT함수로 출력할 수 있게 서브쿼리로 넣어준다.
SELECT t1.LEVEL, count(t1.level)
FROM (SELECT customer_id, round(sum(amount)) sum_amount,
CASE WHEN round(sum(amount)) >= 151 THEN 'A'
WHEN round(sum(amount)) BETWEEN 101 AND 150 THEN 'B'
WHEN round(sum(amount)) BETWEEN 51 AND 100 THEN 'C'
WHEN round(sum(amount)) <= 50 THEN 'D'
ELSE 'Empty'
END AS level
FROM payment
GROUP BY customer_id) AS t1
GROUP BY t1.LEVEL
ORDER BY t1.LEVEL;
원하는 답을 얻을 수 있다.