Ejemplos create view

Primero creamos una vista base:

create view country_payment as

select country, payment.* from country
join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)

Después nos basamos en esta para crear las siguientes:

create view country_total as

select country, sum(amount) total from country_payment
group by country


create view country_month as

select country, month(payment_date) month, count(payment_id) total from country_payment
group by country, month

Ejemplos Create view

create view peliculas_pais as
select country, count(rental_id) total
from country join city using (country_id)
join address using(city_id)
join customer using(address_id)
join rental using(customer_id)
group by country_id
CREATE VIEW peliculas_categoria AS
select name, count(film_id) total
from category join film_category using (category_id)
group by category_id

 

Ejercicios subconsultas

Alquileres por encima de la media

select title, amount from film join inventory using(film_id)
join rental using (inventory_id)
join payment using (rental_id)
where amount> (select avg(amount) from payment)

Clientes con más películas alquiladas que alguno de los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> any (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Clientes con más películas alquiladas que todos los que empiezan por ‘A’

select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by first_name
having total> all (select count(rental_id) total from
customer join rental using (customer_id)
where first_name like 'a%'
group by first_name
)

Actores con más películas que el actor de id 1

select first_name, count(film_id) total 
from actor join film_actor using(actor_id)
group by actor_id
having total>(select count(film_id) from film_actor where actor_id=1)

Actores que trabajan en películas con rating ‘R’

select distinct first_name, last_name from actor join film_actor using(actor_id)
where film_id in
(SELECT film_id FROM sakila.film
where rating='R')

o

select distinct first_name, last_name from actor join film_actor using(actor_id)
join film using (film_id) where rating='R'

Actores que no han trabajado en películas con rating ‘R’

select distinct first_name, last_name from actor 
where actor_id not in
(SELECT actor_id FROM film_actor join sakila.film using(film_id)
where rating='R')

Actores que no hayan trabajado en películas de acción

select * from actor where actor_id not in (
select actor_id from film_actor join film using(film_id)
join film_category using (film_id)
join category using(category_id)
where name='ACTION'
)

Actores que no hayan trabajado en películas de rating ‘R’ con exists

select distinct first_name, last_name from actor a
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and a.actor_id=fa.actor_id)

Eliminar actores que no hayan trabajado en películas de rating ‘R’

delete from actor 
where not exists 
(SELECT film_id FROM sakila.film join film_actor fa using(film_id)
where rating='R' and actor.actor_id=fa.actor_id);

delete from actor
where actor_id in 
(select actor_id 
from film_actor join film using(film_id) 
where rating='R');

Clientes que no han alquilado  películas de rating ‘R’

select * from customer where customer_id not in (
select customer_id from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
where rating='R');

select * from customer where not exists(
select 1 from rental 
join inventory using(inventory_id)
join film using(film_id)
where rating='R' and customer.customer_id=rental.customer_id)

Media de películas alquiladas por clientes:

select avg(total) from
(select first_name,count(rental_id) total from
customer join rental using (customer_id)
group by customer_id) ttt

Media de películas por categoría

select avg(total) media from
(select name, count(film_id) total
from category join film_category using (category_id)
group by category_id) tabla

Ejercicios Base de datos

Total de pagos por países

select country, sum(amount) total from
country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join payment using (customer_id)
group by country

Películas con más de diez actores

select title, count(actor_id) actores
from film join film_actor using (film_id)
group by title
having actores>10

Actor que ha trabajado en más películas

select first_name, last_name, count(film_id) peliculas
from actor join film_actor using(actor_id)
group by first_name, last_name
order by peliculas desc
limit 0,1

Actores que han hecho películas de acción

select distinct first_name, last_name,name
from actor join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name=’Action’
order by first_name,last_name

Países con más de 50 clientes

select country, count(customer_id) total from
country join city using (country_id)
join address using (city_id)
join customer using (address_id)
group by country
having total>50