-- En qué paises se han alquilado películas de comedia
-- ¿En que tablas está la información?
-- Pais en country
-- comedia en category
-- Pues tengo que buscar el camino:
-- country--city--address--customer--rental--inventory--film--film_category--category
-- Formato estándar
select distinct country from country
join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where name='comedy'
order by country;
-- optimizado para mysql
select * from country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='comedy'
order by country;
-- Formato estándar usando alias
select distinct country 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
join rental r on cu.customer_id=r.customer_id
join inventory i on r.inventory_id=i.inventory_id
join film f on i.film_id=f.film_id
join film_category fc on f.film_id=fc.film_id
join category ca on fc.category_id=ca.category_id
where name='comedy'
order by country;
