Day10-2 MySQL sakila 테이블을 이용한 연습 문제

-- 1. 배우의 이름을 알파벳 내림차순 10명을 보여주세요.

SELECT * FROM `actor`
ORDER BY `first_name` DESC
LIMIT 10;

 

-- 2. 영화의 카테고리를 보여주세요.

SELECT * FROM `category`;

 

-- 3. 고객 중에 휴면 회원들을 모두 보여주세요.

SELECT * FROM `customer`
where `active` = 0;

 

-- 4. 영화를 대여기간 순으로 오름차순 정렬하세요

SELECT * FROM `film`
ORDER BY `rental_duration` ASC;

-- 5. 영화를 대여료 순으로 내림차순 정렬하세요.

SELECT * FROM `film`
ORDER BY `rental_rate` DESC;

-- 6. 영화 중 개(dog)가 나오는 것을 모두 보여주세요.

SELECT * FROM `film`
WHERE `description` LIKE '%dog%';

-- 7. 영화 중 2000년 이전에 나온 영화들을 모두 보여주세요.

SELECT * FROM `film`
WHERE `release_year` <= 2000;

-- 8. 필름 액터 테이블로 페넬로페 귀네스가 나온 영화의 제목들을 출력해주세요.

SELECT `title` FROM `film_actor`
INNER JOIN `actor`
ON `film_actor`.`actor_id` = `actor`.`actor_id`
INNER JOIN `film`
ON `film_actor`.`film_id` = `film`.`film_id`
WHERE `actor`.`first_name` = 'PENELOPE' AND `actor`.`last_name` = 'GUINESS';

-- 9. 가장 많은 영화에 출연한 배우의 성과 이름과 출연한 영화의 갯수를 출력해주세요.

SELECT `first_name`, `last_name`, COUNT(*) AS 'CNT' FROM `film_actor`
INNER JOIN `actor`
ON `film_actor`.`actor_id` = `actor`.`actor_id`
GROUP BY `film_actor`.`actor_id`
ORDER BY `CNT` DESC
LIMIT 1;

-- FULL GROUP BY 옵션 끄기 -- mysql command 창 시작 후 -- set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- 입력

-- 10. 회원 번호와 그 회원이 대여료를 총 얼마 냈는지 보여주세요.

SELECT `customer_id`, SUM(`amount`) FROM `payment`
GROUP BY `customer_id`;

-- 11. 1번 store가 가지고 있는 총 비디오의 갯수를 보여주세요.

SELECT COUNT(*) FROM `inventory`
WHERE `store_id` = 1;

-- 12. 1번 store가 가지고 있는 영화와 해당 영화의 비디오 갯수를 보여주세요.

SELECT `inventory`.`film_id`, `film`.`title`, COUNT(*) FROM `inventory`
INNER JOIN `film`
ON `inventory`.`film_id` = `film`.`film_id`
WHERE `store_id` = 1
GROUP BY `inventory`.`film_id`;

-- 13. 영화의 제목과 카테고리를 텍스트로 보여주세요.

SELECT `title`, `name` FROM `film_category`
INNER JOIN `film`
ON `film_category`.`film_id` = `film`.`film_id`
INNER JOIN `category`
ON `film_category`.`category_id` = `category`.`category_id`;

-- 14. 도시와 해당 국가를 보여주세요.

SELECT `city`, `country` FROM `city`
INNER JOIN `country`
ON `city`.`country_id` = `country`.`country_id`;

-- 15. 고객 중 미국에 살고 있는 회원의 이름과 이메일을 보여주세요.

SELECT `first_name`, `email` FROM `customer`
INNER JOIN `address`
ON `customer`.`address_id` = `address`.`address_id`
INNER JOIN `city`
ON `address`.`city_id` = `city`.`city_id`
INNER JOIN `country`
ON `city`.`country_id` = `country`.`country_id`
WHERE `country`.`country` = 'United States';

-- 16. 영화 중 대여횟수가 가장 많은 영화의 이름을 보여주세요.

SELECT `title`, COUNT(*) AS 'CNT' FROM `rental` 
INNER JOIN `inventory`
ON `rental`.`inventory_id` = `inventory`.`inventory_id`
INNER JOIN `film`
ON `inventory`.`film_id` = `film`.`film_id`
GROUP BY `inventory`.`film_id`
ORDER BY `CNT` DESC
LIMIT 1;

-- 17. 영화 중 대여기간이 4일 미만이고 주연의 이름이 JOHNNY LOLLOBRIGIDA 이고 비디오 부록에 예고편이 있는 영화의 제목을 보여주세요.

-- A. 영화 중 대여기간이 4일 미만
SELECT * FROM `film`
WHERE `film`.`rental_duration` < 4;


-- B. JOHNNY LOLLOBIRIGIDA의 정보
SELECT * FROM `actor`
WHERE `actor`.`first_name` = 'JOHNNY' 
AND `actor`.`last_name` = 'LOLLOBRIGIDA';


-- C. 영화 부록 중 예고편이 있는 영화들
SELECT * FROM `film`
WHERE `film`.`special_features` LIKE '%Trailers%';

-- D. 대여 기간 4일 미만 + 예고편이 있는 영화들
SELECT * FROM `film`
WHERE `film`.`special_features` LIKE '%Trailers%' 
AND `film`.`rental_duration` < 4;
-- E. LOLLOBRIGIDA 가 출연한 영화들
SELECT * FROM `film_actor`
INNER JOIN `actor`
ON `film_actor`.`actor_id` = `actor`.`actor_id`
WHERE `actor`.`first_name` = 'JOHNNY'
AND `actor`.`last_name` = 'LOLLOBRIGIDA';


-- F. 최종
SELECT `film`.`title` FROM `film_actor`
INNER JOIN `actor`
ON `film_actor`.`actor_id` = `actor`.`actor_id`
INNER JOIN `film`
ON `film_actor`.`film_id` = `film`.`film_id`
WHERE `actor`.`first_name` = 'JOHNNY'
AND `actor`.`last_name` = 'LOLLOBRIGIDA'
AND `film`.`rental_duration` < 4
AND `film`.`special_features` LIKE '%Trailers%';

-- 18. 직원 중 Mike Hillyer가 LINDA WILLIAMS 에게 대여해준 영화의 제목, 대여일, 반납일을 보여주세요.

 

-- A. Mike Hillyer 정보 확인
SELECT * FROM `staff`
WHERE `staff`.`first_name` = 'Mike'
AND `staff`.`last_name` = 'Hillyer';


-- B. Linda Williams 정보 확인
SELECT * FROM `customer`
WHERE `customer`.`first_name` = 'Linda'
AND `customer`.`last_name` = 'Williams';


-- C. Rental에서 Mike Hillyer가 Linda Williams에게 대여한 영화 기록 확인
SELECT `film`.`film_id`, `film`.`title` FROM `rental`
INNER JOIN `staff`
ON `rental`.`staff_id` = `staff`.`staff_id`
INNER JOIN `customer`
ON `rental`.`customer_id` = `customer`.`customer_id`
INNER JOIN `inventory`
ON `rental`.`inventory_id` = `inventory`.`inventory_id`
INNER JOIN `film`
ON `film`.`film_id` = `inventory`.`film_id`
WHERE `staff`.`first_name` = 'Mike'
AND `staff`.`last_name` = 'Hillyer'
AND `customer`.`first_name` = 'Linda'
AND `customer`.`last_name` = 'Williams';

-- 19. 영화의 이름과 출연한 배우의 숫자를 배우의 숫자로 내림차순해서 보여주되 출연한배우의 숫자 컬럼의 이름을 출연배우수로 만들어 보여세요.

SELECT `film_actor`.`film_id`, `film`.`title`, COUNT(*) AS '출연배우수' FROM `film_actor`
INNER JOIN `film`
ON `film_actor`.`film_id` = `film`.`film_id`
GROUP BY `film_actor`.`film_id`
ORDER BY `출연배우수` DESC;

-- 20. 회원의 이름, 빌린 비디오의 숫자, 빌린 비디오 대여점의 주소, 회원의 거주 도시를 회원 번호순으로 정렬하여 보여주세요.

SELECT `customer`.`first_name`, COUNT(*), `sa`.`address`, `city`.`city` FROM `rental`
INNER JOIN `customer`
ON `rental`.`customer_id` = `customer`.`customer_id`
INNER JOIN `address` AS `ca`
ON `customer`.`address_id` = `ca`.`address_id`
INNER JOIN `city`
ON `ca`.`city_id` = `city`.`city_id`
INNER JOIN `staff`
ON `rental`.`staff_id` = `staff`.`staff_id`
INNER JOIN `store`
ON `staff`.`store_id` = `store`.`store_id`
INNER JOIN `address` AS `sa`
ON `store`.`address_id` = `sa`.`address_id`
GROUP BY `rental`.`customer_id`, `sa`.`address_id`
ORDER BY `rental`.`customer_id`;