231204 sql 계속(3)

-- 11.
SELECT COUNT(*)
FROM film_category
WHERE category_id = (SELECT category_id FROM category WHERE name = 'Comedy');

-- 12.
SELECT COUNT(*)
FROM customer
         RIGHT JOIN address ON customer.address_id = address.address_id
WHERE customer.address_id IS NULL;

-- 13.
SELECT CONCAT(c.first_name, ' ', c.last_name), c.email
FROM customer c
         JOIN address a ON c.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
WHERE co.country = 'Canada';

-- city id
SELECT *
FROM city;
-- country_id
SELECT *
FROM country;
-- country

-- 14.
SELECT *
FROM category;
-- Family: category_id(8)

SELECT *
FROM film_category;
-- category_id;

SELECT title
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 = 'Family';

-- 15.
SELECT title, COUNT(*)
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 title
ORDER BY 2 DESC
LIMIT 100;

SELECT *
FROM inventory;
-- inventory_id > film_id

SELECT *
FROM film;
-- film_id > title;

 

계속 sql로 타고 타고 들어가서 문제를 푸는것은 매우 흥미롭다, 자꾸 두들겨보다보니, sakila database가 점점 더 친숙하게 느껴진다. 오늘도 화이팅