Ejemplos subqueries

Pagos por encima de la media:

select * from payment where amount>(
select avg(amount) from payment
)

Películas de la categoría 1:

select distinct first_name,last_name from actor join film_actor using(actor_id) where film_id in
(
select film_id from film_category where category_id=1
)

Películas con el mayor coste de reemplazo:

select * from film where replacement_cost=(
select max(replacement_cost) from film
)

Películas de la categoría con más alquileres:

select * from film join film_category using(film_id)
where category_id in (
select category_id from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
having count(rental_id)=(
select max(total) from (
select count(rental_id) total from category join film_category using(category_id)
join film using(film_id)
join inventory using(film_id)
join rental using(inventory_id)
group by category_id
) alias_falso
)
)