use sakila;

/*concatenar campos e exibir dados em 2 ou mais tables*/
select
concat(first_name,' ', last_name) as 'cliente',
address, city, country
from customer
inner join address on customer.address_id = address.address_id
inner join city on address.city_id = city.city_id
inner join country on city.country_id = country.country_id;

select
title as 'filme', 
name as 'categoria'
from film
inner join film_category as fc on fc.film_id = film.film_id
inner join category as ca on fc.category_id = ca.category_id; 

/* exibir a categoria e a qtde de filmes por categoria*/
select 
name as 'categoria',
count(film_id)
from category as ca
inner join film_category as fc on fc.category_id = ca.category_id
group by name
order by count(film_id) desc;
  
-- 1
select
concat(first_name, ' ', last_name) as cliente,
customer.store_id as loja, 
address as 'endereço'
from customer
inner join store on customer.store_id = store.store_id
inner join address on store.address_id = address.address_id;

-- 2
select
title as filme, 
concat(first_name, ' ', last_name) as artista
from film
inner join film_actor as fa on fa.film_id = film.film_id
inner join actor on fa.actor_id = actor.actor_id
order by title asc; 

-- 3
select
title as filme,
inv.store_id as loja
from film
inner join inventory as inv on inv.film_id = film.film_id;

-- 4 e 5
select 
title as filme,
store_id as loja,
count(inv.film_id)
from film
inner join inventory as inv on inv.film_id = film.film_id
group by title, store_id;

-- 6
select
concat(first_name, ' ', last_name) as cliente,
count(rental.customer_id) as total
from rental
inner join customer on rental.customer_id = customer.customer_id
group by rental.customer_id
order by count(rental.customer_id) desc;

-- 7
select
title as filme,
count(rental.inventory_id) as total
from film
inner join inventory as inv on inv.film_id = film.film_id
inner join rental on rental.inventory_id = inv.inventory_id
group by inv.film_id 
order by count(rental.inventory_id) desc;