Encontrar la calificación promedio de cada película SQL

Actualmente estoy tomando la class de standford en línea en las bases de datos. Si pudiera ayudarme a resolver este problema sql, lo agradecería enormemente. Lo siento, soy un completo novato.

Película de tabla:

mID | title | year | director 

Clasificación de la tabla

 rID | mID | stars | ratingDate 

Revisor de tabla

 rID | name 

Para todos los pares de revisores, de manera que ambos revisores dieron una calificación a la misma película, devuelva los nombres de ambos revisores. Elimine los duplicates, no empareje a los revisores consigo mismos, e incluya cada par solo una vez. Para cada par, devuelva los nombres en el par en order alfabético.

Bueno. Usted realmente hizo dos preguntas diferentes.

Primero, responderé la pregunta que hizo en el título de la publicación.

Esto debería funcionar.

 select Movie.title, avg(Rating.stars) as AR from Rating join Movie on Rating.mID = Movie.mID group by Movie.title order by AR desc 

Si no especifica la cláusula "on" en la unión, así,

 select Movie.title, avg(Rating.stars) as AR from Rating join Movie group by Movie.title order by AR desc 

entonces todas las películas tendrán la misma calificación, el promedio global, debido al producto cruzado completo de la unión. Desea una combinación interna en el nombre o, en este caso, id.

En segundo lugar, esta es la respuesta a la otra pregunta que hizo, sobre pares de revisores. R1.name < R2.name garantiza que los nombres de los revisores se orderarán de izquierda a derecha. y el order by R1.name asegura que esos pares se orderarán de arriba a abajo.

 select distinct R1.name, R2.name from Rating Ra1 join Rating Ra2 join Reviewer R1 join Reviewer R2 where Ra1.mID = Ra2.mID and Ra1.rID != Ra2.rID and R1.name < R2.name and Ra1.rID = R1.rID and Ra2.rID = R2.rID order by R1.name 

Esto te ayudará a comenzar.

 SELECT m.*, ra.*, re.* FROM Movie m JOIN Rating ra ON ra.mID = m.mID JOIN Reviewer re ON re.rID = ra.rID 

Encuentre la calificación promedio por película:

 SELECT [m].[mID], [m].[title], AVG([r].[stars]) AS [AvgRating] FROM [Movie] [m] LEFT JOIN [Rating] [r] ON [m].[mID] = [r].[mID] GROUP BY [m].[mID], [m].[title] 

Encuentra los revisores únicos por película:

 SELECT [r].[mID], [v].[rID], [v].[name] FROM [Reviewer] [v] INNER JOIN [Rating] [r] ON [v].[rID] = [r].[rID] GROUP BY [v].[rID], [v].[name], [r].[mID] 

Combinar:

 SELECT [rat].[mID], [rat].[title], [rev].[rID], [rev].[name], [rat].[AvgRating] FROM ( SELECT [m].[mID], [m].[title], AVG([r].[stars]) AS [AvgRating] FROM [Movie] [m] LEFT JOIN [Rating] [r] ON [m].[mID] = [r].[mID] GROUP BY [m].[mID], [m].[title] ) AS [rat] LEFT JOIN ( SELECT [r].[mID], [v].[rID], [v].[name] FROM [Reviewer] [v] INNER JOIN [Rating] [r] ON [v].[rID] = [r].[rID] GROUP BY [v].[rID], [v].[name], [r].[mID] ) AS [rev] ON [rat].[mID] = [rev].[mID] ORDER BY [rat].[mID] ASC, [rev].[name] ASC 

Para un violín, mira aquí .

Tal vez algo como esto:

 SELECT rev.rID, rev.name, m.title FROM [Reviewer] rev JOIN [Rating] rate ON rev.rID = rate.rID JOIN [Movie] m ON rate.mID = m.mID JOIN ( SELECT mID FROM [Rating] GROUP BY mID HAVING COUNT(Distinct rID) > 1 ) m2 on m.mID = m2.mID ORDER BY rev.name 

–EDITAR

Si necesita get el promedio también:

 SELECT rev.rID, rev.name, m.title, ar.avgRating FROM [Reviewer] rev JOIN [Rating] rate ON rev.rID = rate.rID JOIN [Movie] m ON rate.mID = m.mID JOIN ( SELECT mID FROM [Rating] GROUP BY mID HAVING COUNT(Distinct rID) > 1 ) m2 on m.mID = m2.mID JOIN ( SELECT mID, AVG(stars) as avgRating FROM [Rating] GROUP BY mID ) ar on m.mID = ar.mID ORDER BY rev.name 

Y el SQL Fiddle .

 select distinct r2.name, r1.name from (select r1.rid, r1.mid, r2.name from (select * from reviewer order by name) r2 join (select r1.rid, r1.mid from rating r1 join reviewer r2 on r1.rid = r2.rid order by r2.name) r1 on r2.rid = r1.rid group by mid order by r2.name) r1, (select r1.rid, r1.mid, r2.name from reviewer r2, rating r1 where r1.rid = r2.rid)r2 where r1.rid <> r2.rid and r1.mid = r2.mID order by r2.name, r1.name 

Estoy haciendo el mismo curso y esta es la respuesta que obtuve:

 SELECT DISTINCT name1, name2 FROM (SELECT R1.name AS name1, R2.name AS name2 FROM (Rating JOIN Reviewer using(rId)) AS R1, (Rating JOIN Reviewer using(rId)) AS R2 WHERE R1.mId = R2.mId AND R1.rId <> R2.rId) AS Tuple WHERE name1 < name2; 

No sé por qué OP habla sobre el promedio en el título, ya que esta pregunta no lo pide.

EDITAR: para reference adicional, aquí están los datos: https://class.stanford.edu/c4x/DB/SQL/asset/moviedata.html