231208 sql코딩테스트 1차 종료!

-- 31.
SELECT title, rating, c.name
FROM film f
         JOIN film_category fc ON f.film_id = fc.film_id
         JOIN category c ON fc.category_id = c.category_id
WHERE c.name = 'Action'
ORDER BY rating DESC
LIMIT 5
;
# film_id, rating

SELECT *
FROM category;
# category_id, name

SELECT *
FROM film_category;
# film_id >> category_id

-- 32.
# SELECT rating, avg(TIMESTAMPDIFF(HOUR, rental_date, return_date))
# FROM rental r
#          JOIN inventory i ON r.inventory_id = i.inventory_id
#          JOIN film f ON i.film_id = f.film_id
# GROUP BY 1;

SELECT rating, AVG(film.rental_duration)
FROM film
GROUP BY rating;

-- 33.

SELECT *
FROM payment;
# rental_id, amount
SELECT *
FROM rental;
# rental_id >> inventory_id
SELECT *
FROM inventory;
# inventory_id >> store_id

CREATE VIEW branch_sales AS
SELECT i.store_id, SUM(amount)
FROM payment p
         JOIN rental r ON p.rental_id = r.rental_id
         JOIN inventory i ON r.inventory_id = i.inventory_id
GROUP BY i.store_id;

-- 34.
DROP VIEW branch_sales;

-- 35.
SELECT *
FROM customer;
# address_id

SELECT *
FROM address;
# city_id

SELECT *
FROM city;
# country_id

SELECT *
FROM country;

SELECT co.country, COUNT(*)
FROM customer cu
         JOIN address a ON cu.address_id = a.address_id
         JOIN city ci ON a.city_id = ci.city_id
         JOIN country co ON ci.country_id = co.country_id
GROUP BY co.country
ORDER BY 2 DESC
LIMIT 5;

 

지금 올린 코드에, 오후에 5개만 더하면, 코딩테스트 문제의 절반을 풀게 된다! 즐겁게 도전했고, 많이 배웠다.

남은 employee db를 활용한 코테도 성실하게 풀어내겠다^^!