Ejemplos subconsultas

-- Quiero saber las ciudades del país con más clientes

-- ¿Cual es el país con más clientes?

select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1;
        
-- Entonces buscamos las ciudades cuyo país sea el resultado de la consulta anterior
        
select country,city,count(customer_id) total from country join city using(country_id)
	join address using(city_id) join customer using(address_id)
    where country=(
		select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1
)
	group by country_id, city_id;

-- Que películas están por encima de la media en total de alquileres

create view total_peliculas as
select title, sum(amount) total from
film join inventory using(film_id)
join rental using(inventory_id)
join payment using(rental_id)
group by title;

select avg(total) from total_peliculas;

select * from total_peliculas where total>(
select avg(total) from total_peliculas
);