Ayudantia 4

Esta es una vista previa del archivo. Inicie sesión para ver el archivo original

USE imdb;
-- Consultas Anidadas
SELECT Titulopelicula
FROM Peliculas
WHERE idpelicula IN (
SELECT idpelicula
FROM fechadeestreno
WHERE fecha > '2019-12-31' 
AND fecha < '2021-01-01' );
-- Consultas Anidadas Correlacionadas
SELECT p.Titulopelicula
FROM Peliculas p 
WHERE Duracion >(
SELECT AVG(Duracion)
FROM Peliculas);
SELECT SUM(duracion) AS Duracion_Total
FROM Peliculas;
SELECT MIN(duracion) AS Duracion_Minima
FROM Peliculas;
SELECT MAX(duracion) AS Duracion_Maxima
FROM Peliculas;
SELECT AVG(duracion) AS Promedio_Duracion
FROM Peliculas;
-- Count
SELECT COUNT(rating)
FROM Rating;
SELECT COUNT(idpelicula)
FROM Peliculas;
-- Group By
SELECT p.Titulopelicula AS Pelicula, AVG(r.rating) AS Rating
FROM Rating r , Peliculas p
WHERE p.idpelicula = r.idpelicula 
GROUP BY r.idpelicula;
SELECT u.mail, SUM(r.rating)
FROM Usuario u, Rating r
WHERE u.mail = r.mail
GROUP BY (u.mail);
SELECT u.mail, MIN(r.rating)
FROM Usuario u, Rating r
WHERE u.mail = r.mail
GROUP BY (u.mail);
SELECT p.Titulopelicula, MAX(r.rating)
FROM Peliculas p, Rating r
WHERE p.idpelicula = r.idpelicula 
GROUP BY (p.titulopelicula);
SELECT p.Titulopelicula, COUNT(r.idrating)
FROM Peliculas p, Rating r
WHERE r.idpelicula = p.idpelicula 
GROUP BY p.Titulopelicula;