Más ejercicios modelo entidad relación

Acabas de empezar tu colección de películas y quieres hacer una base de datos para construir su ficha técnica. De cada película, necesitas su título, año, nacionalidad y algunos datos de su director: el nombre, la fecha de nacimiento y su país de origen. Además, quieres saber su idioma, si es en blanco y negro o en color, si tiene alguna restricción de edad, un resumen y poder poner tus propias observaciones.

La ficha técnica de cada película también debe incluir el reparto de actores, donde aparecerá su nombre, su nacionalidad y el nombre del personaje que tiene en la película.

 

 

Se desea crear una base de datos que contenga información sobre las revistas a las que estás suscrito o compras habitualmente. De cada revista, se pide su título, el ISSN (un código que identifica a la publicación), el número y el año de publicación. También se desea almacenar información de cada uno de los artículos publicados: el título, la página de inicio y la página de fin. Se asume que no hay dos artículos con el mismo título.

Cada artículo puede estar escrito por varios autores, de quienes interesa conocer su nombre, una dirección de correo electrónico y su país de nacimiento, así como un número que indique la posición en la que aparece en cada artículo: un 1 si es el primer autor, un 2 si aparece en segundo lugar, etc.

 

 

Una ONG desea elaborar una base de datos para llevar el seguimiento de todos sus proyectos. Tiene diversas sedes en varios países que se encargan de gestionar y coordinar los proyectos de ese país, cada uno de los cuales puede afectar a una o varias poblaciones.

Sobre la sedes se desea mantener un identificador, la ciudad y país en el que se encuentra, junto con su dirección, un teléfono de contacto y el nombre del director. Cada sede gestiona un conjunto de proyectos, con un código, un título, fechas de inicio y finalización, el presupuesto asignado y el nombre del responsable.

De cada proyecto es necesario conocer qué actuaciones se realizan en cada población, almacenando el nombre, país y nº de habitantes y un identificador para diferenciarlas. Además se desea la inversión del proyecto que corresponde a la población y una pequeña descripción de la actuación.

 

Un parque zoológico quiere construir una BD para organizar las especies que posee y los distintos itinerarios para visitar el parque. La información se estructura de la siguiente forma. De las especies, se desea conocer su nombre común y su nombre científico, así como una descripción general y una fotografía. Cada especie puede vivir en distintos hábitats naturales, definidos por su nombre, clima y vegetación predominante. Cada especie tiene asociado un índice de vulnerabilidad dentro de cada hábitat, que mide el riesgo de extinción de la especie en el dicho hábitat. Para organizar las visitas, y en función de los hábitats que desee recorrer un visitante, el parque le ofrece una serie de recorridos por los hábitats, que se identifican por su código y se caracterizan por su duración estimada, longitud y número máximo de visitantes permitidos. Un hábitat sólo puede formar parte de un itinerario.

 

Una compañía aérea necesita una base de datos para registrar la información de sus vuelos. Los vuelos están caracterizados por un Id, la fecha y los aeropuertos de origen y destino. Cada vuelo es realizado por un avión. Los aviones tienen una matrícula que los identifica, el fabricante, un modelo e información sobre su capacidad (número máximo de pasajeros) y autonomía de vuelo (en horas). La tripulación asignada al vuelo está formada por el personal de la propia compañía. De cada trabajador se conoce su id, su nombre y su categoría profesional, así como el puesto que ocupa en cada vuelo en particular.

Por último, para cada vuelo, se almacena la lista completa de pasajeros, con su dni, el nombre, el asiento que ocupa y su clase (turista, primera o business).

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