join.sql

select distinct c.customer_id,c.first_name from customer as c right join rental as r on c.customer_id = r.customer_id;  

select c.customer_id,c.first_name,f.title
from customer as c right join rental as r on c.customer_id = r.customer_id 
right join payment as p on r.rental_id=p.rental_id
left join inventory as i on r.inventory_id=i.inventory_id
left join film as f on i.film_id=f.film_id
where p.payment_date<'2007-02-15';

select count(c.category_id) cantidad,c.name
from rental as r
left join inventory as i on r.inventory_id=i.inventory_id
left join film as f on i.film_id=f.film_id
left join film_category as fc on f.film_id=fc.film_id
left join category as c on c.category_id=fc.category_id
group by c.category_id 
order by cantidad desc limit 5
;

select count(c.category_id) cantidad,c.name,cu.customer_id
from rental as r
left join customer as cu on r.customer_id = cu.customer_id
left join inventory as i on r.inventory_id=i.inventory_id
left join film as f on i.film_id=f.film_id
left join film_category as fc on f.film_id=fc.film_id
left join category as c on c.category_id=fc.category_id
group by (c.category_id,cu.customer_id) 
order by cu.customer_id,cantidad desc
;

select cantidad, nom from (select count(l.name) cantidad, l.name nom
from rental as r
left join inventory as i on r.inventory_id=i.inventory_id
left join film as f on i.film_id=f.film_id
left join language as l on f.language_id=l.language_id
group by l.language_id ) as sq order by cantidad desc limit 1
;

select count(r.rental_id) cantidad, s.staff_id, s.first_name
from rental as r
left join payment as p on r.rental_id=p.rental_id
right join staff as s on p.staff_id=s.staff_id
group by s.staff_id
;

select count(a.actor_id) cantidad,a.first_name
from rental as r
left join inventory as i on r.inventory_id=i.inventory_id
left join film as f on i.film_id=f.film_id
right join film_actor as fa on f.film_id=fa.film_id
right join actor as a on a.actor_id=fa.actor_id
group by a.actor_id
order by cantidad desc limit 1
;

select count(c.customer_id) cantidad,c.first_name
from rental as r
left join customer as c on c.customer_id=r.customer_id
group by c.customer_id order by cantidad desc limit 1
;