Skip to Content
Suffering builds character
아카이브14.데이터베이스SQL1.DML연습문제6.추추가 연습문제 해답

6.추추가 연습문제 해답

1. actor 테이블에서 first_name과 last_name을 연결하여(concat) 대문자(upper)로 출력되도록 쿼리 작성

sql
SELECT UPPER(CONCAT(first_name, ' ', last_name)) 'Actor Name'
FROM actor;

2. actor 테이블에서 actor_id, first_name, last_name을 조회하되, first_name이 Joe인 사람을 조회

제약조건: 테이블 내 실제 값은 대문자인 JOE로 되어있지만, ‘Joe’와 같이 대문자 외에 소문자로 섞어서 조회해도 조회가 가능하도록 쿼리 작성

sql
SELECT actor_id, first_name, last_name 
FROM actor 
WHERE LOWER(first_name) = LOWER("Joe");

3. actor 테이블에서 last_name과 총 몇 명이 동일한 last_name을 가지고 있는지 조회

그룹핑 기준 last_name

정렬 기준

  1. actor_count 내림차순
  2. last_name 오름차순
sql
SELECT last_name, COUNT(*) actor_count 
FROM actor 
GROUP BY last_name 
ORDER BY actor_count DESC, last_name;

4. 3번 문제 내용으로 동일하게 조회하되, 그룹핑 조건으로 actor_count가 3보다 큰 데이터만 조회

sql
SELECT last_name, COUNT(*) actor_count 
FROM actor 
GROUP BY last_name 
HAVING actor_count > 3 
ORDER BY actor_count desc, last_name;

5. address 테이블에 대한 테이블 생성 쿼리를 조회하기 위한 쿼리 작성

sql
SHOW CREATE TABLE address;

→ address 테이블 생성 시 사용된 DDL 쿼리를 조회할 수 있음

6. JOIN을 활용하여 staff의 first_name과 last_name, address, district, postal_code, city_id를 조회

sql
SELECT first_name, last_name, address, district, postal_code, city_id 
FROM staff stf 
LEFT JOIN address adr 
ON stf.address_id = adr.address_id;

→ 조인하는 테이블 간 공통되지 않는 컬럼은 별칭을 작성하지 않아도, 참조 가능(first_name, last_name 등) address_id는 두 테이블에 모두 존재하기 때문에 각각 별칭 지정 필요

7. JOIN을 활용하여 각 staff가 2005년 8월에 집계한 총 대여금액, first_name, last_name 조회

sql
SELECT stf.first_name, stf.last_name, SUM(pay.amount)
FROM staff stf 
LEFT JOIN payment pay 
ON stf.staff_id = pay.staff_id 
WHERE month(pay.payment_date) = 8 
AND year(pay.payment_date)  = 2005 
GROUP BY stf.first_name, stf.last_name;

8. 영화 제목과 해당 영화에 등장한 배우의 총 인원 수를 조회(INNER JOIN 활용)

sql
SELECT flm.title, COUNT(*) number_of_actors 
FROM film flm 
INNER JOIN film_actor fim_act 
ON flm.film_id = fim_act.film_id 
GROUP BY flm.title 
ORDER BY number_of_actors DESC;

9. 영화 제목이 ‘Hunchback Impossible’인 영화는 inventory 테이블에 총 몇 개의 복제본이 있는지 조회

ex) 만약, Inventory 테이블에 A라는 영화의 복제본이 5개일 경우, 5개의 비디오를 대여 가능하다는 의미

sql
SELECT flm.title, COUNT(*) number_in_inventory 
FROM film flm 
INNER JOIN inventory inv 
ON flm.film_id = inv.film_id 
WHERE lower(flm.title) = lower('Hunchback Impossible') 
GROUP BY flm.title;

10. 고객의 first_name, last_name과 각 고객(customer)이 지금까지 대여 과정에서 지불한 총 금액을 조회

sql
SELECT cust.first_name, cust.last_name, SUM(pay.amount) 'Total Amount Paid' 
FROM payment pay 
JOIN customer cust 
ON pay.customer_id = cust.customer_id 
GROUP BY cust.first_name, cust.last_name 
ORDER BY cust.last_name;

11. 제목이 ‘K’나 ‘Q’로 시작하고, 지원 언어가 English인 모든 영화 제목 조회(서브쿼리 활용)

sql
SELECT title 
FROM film 
WHERE (title LIKE 'K%' OR title LIKE 'Q%') 
AND language_id IN ( 
	SELECT language_id 
	FROM language 
	WHERE name = 'English'
) 
ORDER BY title;

12. 'Alone Trip'에 등장하는 모든 영화 배우들의 first_name, last_name 조회(서브쿼리 활용)

sql
SELECT first_name, last_name 
FROM actor 
WHERE actor_id IN ( 
	SELECT actor_id 
	FROM film_actor 
	WHERE film_id IN (
		SELECT film_id FROM film WHERE LOWER(title) = LOWER('Alone Trip')
	)
);

13. 국적이 캐나다인 고객의 first_name과 last_name, email 조회

sql
-- Sub Query
SELECT first_name, last_name, email 
FROM customer 
WHERE address_id IN (
	SELECT address_id 
	FROM address 
	WHERE city_id IN (
		SELECT city_id 
		FROM city 
		WHERE country_id IN (
			SELECT country_id 
			FROM country 
			WHERE country = 'Canada'
		)
	)
);
 
-- Join
SELECT cus.first_name, cus.last_name, cus.email 
FROM customer cus 
JOIN address adr 
ON cus.address_id = adr.address_id 
JOIN city cit 
ON adr.city_id = cit.city_id 
JOIN country cou 
ON cit.country_id = cou.country_id 
WHERE cou.country = 'Canada';

14. 영화 카테고리가 가족('Family')으로 분류된 모든 영화의 제목과 출시연도 조회

sql
SELECT film_id, title, release_year 
FROM film 
WHERE film_id IN (
	SELECT film_id 
	FROM film_category 
	WHERE category_id IN ( 
		SELECT category_id 
		FROM category 
		WHERE name = 'Family'
	)
);

15. 가장 많이 대여된 영화ID(film_id)와 영화 제목, 대여 횟수를 조회

sql
SELECT A.film_id, A.title, B.times_rented 
FROM film A 
JOIN (
	SELECT inv.film_id, COUNT(ren.rental_id) times_rented 
	FROM rental ren 
	JOIN inventory inv 
	ON ren.inventory_id = inv.inventory_id 
	GROUP BY inv.film_id
) B 
ON A.film_id = B.film_id 
ORDER BY B.times_rented DESC;

16. 영화 가게 id(store_id)와 각 영화 가게(store)가 벌어들인 총 매출 조회

sql
SELECT A.store_id, B.sales 
FROM store A 
JOIN (
	SELECT cus.store_id, SUM(pay.amount) sales 
	FROM customer cus 
	JOIN payment pay 
	ON pay.customer_id = cus.customer_id 
	GROUP BY cus.store_id
) B 
ON A.store_id = B.store_id 
ORDER BY a.store_id;

17. 각 영화 가게의 id, 도시, 국가, 총 매출에 대해 조회

(활용 테이블: store, address, customer, payment, city, country)

sql
SELECT A.*, B.sales 
FROM (
	SELECT sto.store_id, cit.city, cou.country 
	FROM store sto 
	LEFT JOIN address adr 
	ON sto.address_id = adr.address_id 
	JOIN city cit 
	ON adr.city_id = cit.city_id 
	JOIN country cou 
	ON cit.country_id = cou.country_id
) A 
JOIN (
	SELECT cus.store_id, sum(pay.amount) sales 
	FROM customer cus 
	JOIN payment pay 
	ON pay.customer_id = cus.customer_id 
	GROUP BY cus.store_id
) B 
ON A.store_id = B.store_id 
ORDER BY a.store_id;

18. 총 수익 기준 상위 5개에 해당하는 영화 장르와 총 수익(별칭으로 revenue라고 작명) 조회

sql
SELECT cat.name category_name, SUM( IFNULL(pay.amount, 0) ) revenue 
FROM category cat 
LEFT JOIN film_category flm_cat 
ON cat.category_id = flm_cat.category_id 
LEFT JOIN film fil 
ON flm_cat.film_id = fil.film_id 
LEFT JOIN inventory inv 
ON fil.film_id = inv.film_id 
LEFT JOIN rental ren 
ON inv.inventory_id = ren.inventory_id 
LEFT JOIN payment pay 
ON ren.rental_id = pay.rental_id 
GROUP BY cat.name 
ORDER BY revenue DESC 
LIMIT 5;

19. view를 활용하여 18번에서 조회한 쿼리 임시 저장하기

sql
CREATE VIEW top_five_genres as 
SELECT cat.name category_name, SUM( IFNULL(pay.amount, 0) ) revenue 
FROM category cat 
LEFT JOIN film_category flm_cat 
ON cat.category_id = flm_cat.category_id 
LEFT JOIN film fil 
ON flm_cat.film_id = fil.film_id 
LEFT JOIN inventory inv 
ON fil.film_id = inv.film_id 
LEFT JOIN rental ren 
ON inv.inventory_id = ren.inventory_id 
LEFT JOIN payment pay 
ON ren.rental_id = pay.rental_id 
GROUP BY cat.name 
ORDER BY revenue DESC 
LIMIT 5;

20. 저장한 view로 다시 조회

sql
SELECT * FROM top_five_genres;

21. 사용하던 view 제거

sql
DROP VIEW top_five_genres;
Last updated on
5.추추가 연습문제1. 테이블 설계