[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
4주차 과제 풀이 (SQL 54~66번 문제)
[강의내용]
문제54번) store별로 staff는 몇명이 있는지 확인해주세요.
집계함수인 COUNT를 쓰면, 그룹별 컬럼의 갯수를 셀 수 있다.
집계함수를 쓸 때 주의할 점은 FROM절 뒤에 GROUP BY절을 명시해서 어떻게 그룹지을지를 설정해주어야 한다.
SELECT s.store_id, count(st.staff_id)
FROM store s
JOIN staff st ON s.store_id = st.store_id
GROUP BY s.store_id;
store_id별로 staff_id의 갯수를 세어주게 된다.
문제55번) 영화등급(rating) 별로 몇개의 영화를 가지고 있는지 확인해주세요.
등급별 영화의 갯수를 세어본다.
SELECT rating, count(*)
FROM film
GROUP BY rating;
문제56번) 출현한 영화배우(actor)가 10명 초과한 영화명은 무엇인가요?
film_actor 카테고리에서 10개 이상의 actor_id가 인볼브되어 있는 film_id를 찾고,
film 테이블과 JOIN한 뒤 title을 출력하면 된다.
SELECT f.film_id, f.title, count(actor_id)
FROM film_actor fa
JOIN film f ON fa.film_id = f.film_id
GROUP BY f.film_id
HAVING count(actor_id) > 10;
문제57번) 영화 배우(actor)들이 출연한 영화는 각각 몇 편인가요?
- 영화 배우의 이름, 성 과 함께 출연 영화 수를 알려주세요.
56번과 비슷하다. actor_id 컬럼 기준으로 film_id를 COUNT하면 된다.
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id
ORDER BY a.actor_id;
문제58번) 국가(country)별 고객(customer)수는 몇명인가요?
customer 테이블과 country 테이블을 JOIN하기 위해 매개체 역할을 하는 다른 테이블들을 이용한다.
SELECT c.country_id, c.country, count(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_id;
문제59번) 영화 재고 (inventory) 수량이 3개 이상인 영화(film)는?
- store는 상관 없이 확인해주세요.
inventory_id count가 3 이상인 film을 찾으면 된다.
SELECT f.film_id, f.title, count(*)
FROM inventory i
JOIN film f ON i.film_id = f.film_id
GROUP BY f.film_id
HAVING count(*) >= 3;
문제60번) dvd 대여를 제일 많이한 고객 이름은?
rental_id가 가장 많은 customer_id를 구하고 customer 테이블과 JOIN 후 이름을 구하면 된다.
그리고 rental_id의 count를 내림차순으로 정렬하고 LIMIT1을 통해 가장 많은 1명만 추출하면 된다.
SELECT c.customer_id, c.first_name, c.last_name, t1.count
FROM customer c
JOIN (
SELECT r.customer_id, count(r.rental_id)
FROM rental r
GROUP BY r.customer_id) AS t1 ON c.customer_id = t1.customer_id
ORDER BY t1.count DESC
LIMIT 1;
문제61번) rental 테이블을 기준으로, 2005년 5월 26일에 대여를 기록한 고객 중,
하루에 2번 이상 대여를 한 고객의 ID 값을 확인해주세요.
WHERE 조건에서 rental_date가 2005년 5월 26일인 데이터만 필터링하고,
rental_id count가 2 이상인 데이터만 추출하면 된다.
주의할 점은 집계함수를 사용할 때 GROUP BY를 사용하게 되는데, 반드시 WHERE절 다음에 위치해야 한다.
따라서 집계함수인 count 조건을 필터링하기 위해서는 GROUP BY 다음에 오는 HAVING을 사용해야 한다.
SELECT r.customer_id, count(rental_id)
FROM rental r
WHERE date(rental_date) = '2005-05-26'
GROUP BY customer_id
HAVING count(rental_id) >= 2;
문제62번) film_actor 테이블을 기준으로, 출연한 영화의 수가 많은 5명의 actor_id와 출연한 영화 수를 알려주세요.
가장 많은 N행 과 같은 조건을 출력하기 위해 ORDER BY (컬럼명) DESC + LIMIT N 조건을 사용하면 된다.
SELECT actor_id, count(film_id)
FROM film_actor fa
GROUP BY actor_id
ORDER BY count DESC
LIMIT 5;
문제63번) payment 테이블을 기준으로, 결제일자가 2007년2월15일에 해당하는 주문중에서
하루에 2건 이상 주문한 고객의 총 결제 금액이 10달러 이상인 고객에 대해서 알려주세요.
(고객의 id, 주문건수, 총결제금액까지 알려주세요)
SELECT 절에서 집계함수 2개를 차례로 사용할 수 있다. (단 GROUP BY 절의 컬럼이 공통적으로 적용된다)
SELECT customer_id, count(payment_id), sum(amount)
FROM payment p
WHERE date(payment_date) = '2007-02-15'
GROUP BY customer_id
HAVING count(payment_id) >= 2
AND sum(amount) >= 10;
문제64번) 사용되는 언어별 영화 수는?
SELECT l.language_id, l.name, count(f.film_id)
FROM "language" l
LEFT JOIN film f ON l.language_id = f.language_id
GROUP BY l.language_id;
모든 영화가 영어임을 알 수 있다.
문제65번) 40편 이상 출연한 영화 배우(actor) 는 누구인가요?
film_actor 테이블을 중심으로 actor_id 별 film_id를 카운트 한 뒤
이를 actor 테이블과 JOIN하여 배우 정보를 출력하면 된다.
SELECT a.first_name, a.last_name, t1.count
FROM actor a
JOIN (
SELECT fa.actor_id, count(fa.film_id)
FROM film_actor fa
GROUP BY fa.actor_id
HAVING count(fa.film_id) >= 40) AS t1 ON a.actor_id = t1.actor_id;
문제66번) 고객 등급별 고객 수를 구하세요. (대여 금액 혹은 매출액에 따라 고객 등급을 나누고 조건은 아래와 같습니다.) * 대여 금액의 소수점은 반올림 하세요.
| 등급 | 매출액 기준 |
| A | 151 이상 |
| B | 101 이상 150 이하 |
| C | 51 이상 100 이하 |
| D | 50 이하 |
먼저 1) 등급 기준에 따라 고객을 분류하고, 2) 등급별 인원수를 카운트하면 된다.
기준에 따라 등급을 부여하려면 CASE WHEN 조건을 사용한다.
또한 round()는 반올림을 해주는 함수이다. (디폴트는 첫째자리에서 반올림)
SELECT customer_id, 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'
ELSE 'D' END AS level
FROM payment
GROUP BY customer_id
이제 이 쿼리를 서브쿼리 형태로 집어넣어 등급별 인원수를 계산한다.
SELECT t1.LEVEL, count(t1.customer_id)
FROM (
SELECT customer_id, 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'
ELSE 'D' END AS level
FROM payment
GROUP BY customer_id) AS t1
GROUP BY t1.LEVEL;
