SQL 003

21. Media de duración de peliculas con PG.

Answer explanation

SELECT AVG(length) as media_duracion

FROM film

WHERE rating = 'PG'

22. Suma de rental_rate de todas las peliculas.

Answer explanation

SELECT SUM(rental_rate) as suma_rental

FROM film

23. Pelicula con mayor duración.

Answer explanation

SELECT MAX(length) as mayor_duracion

FROM film

24. Pelicula con menor duración.

Answer explanation

SELECT MIN(length) as mayor_duracion

FROM film

25. Mostrar las ciudades del country Spain (multitabla).

Answer explanation

SELECT co.country, c.city

FROM city c, country co

WHERE c.country_id = co.country_id

26. Mostrar el nombre de la película y el nombre de los actores.

Answer explanation

SELECT f.title, a.first_name, a.last_name

FROM film f, actor a, film_actor fa

WHERE f.film_id = fa.film_id

AND a.actor_id = fa.actor_id

ORDER BY f.title

27. Mostrar el nombre de la película y el de sus categorías.

Answer explanation

SELECT f.title, c.name as category

FROM film f, category c, film_category ca

WHERE f.film_id = ca.film_id

AND c.category_id = ca.category_id

ORDER BY f.title

28. Mostrar el country, la ciudad y dirección de cada miembro del staff.

Answer explanation

SELECT co.country, c.city, a.address, a.address2, s.first_name, s.last_name

FROM country co, city c, address a, staff s

WHERE co.country_id = c.country_id

AND a.city_id = c.city_id

AND s.address_id = a.address_id

29. Mostrar el country, la ciudad y dirección de cada customer.

Answer explanation

SELECT co.country, c.city, a.address, a.address2, cu.first_name, cu.last_name

FROM country co, city c, address a, customer cu

WHERE co.country_id = c.country_id

AND a.city_id = c.city_id

AND cu.address_id = a.address_id

30. Numero de peliculas de cada rating

Answer explanation

SELECT rating, count(*)

FROM film

GROUP BY rating