SQL query questions

[raymondu999]

Hey all... need a bit of help here... I have a database, and I'll just describe the relevant parts...

The "movie" table has the pk of "movie_id," and has attribute "title"

The "movie_rating" table has no pk, instead having two fk's, one "movie_id" and "user_id," each referencing the movie and user databases. It also has one attribute, "rating."

Now, I must write an SQL query in such a way that it returns the "movie" records with the top 20 highest ratings. There's nowhere in the database that stores the average rating of each movie. Hence the SQL query must look through the rating table and calculate the average for each movie. Then if there are 2 movies with the same rating it takes the movie with more votes as higher.

I was thinking SELECT Max(avg(movie_rating)), WHERE movie_id = whatever?

Also, there's another problem I'm having. I am quite stumped as to how go about doing this. For this same database, there is another table, where "artists" are stored. Then there are 3 associative tables, respectively, act, write, and direct. These all have 2 fk's each, movie_id and artist_id. Now I need to print out all the movies that each artist has starred in, and also print out the average rating (from the tables aforementioned) for each movie.

note: nvm about the PHP displaying part, I'm just sorting out the SQL side first.

I was thinking it'll prolly be something along those lines, but I can't think of where to go from there. Anyone can lend a hand, or a pointing finger?

Thanks in advance for any help!

 

[belvdr]

SELECT Max(avg(movie_rating)), WHERE movie_id = whatever

Close, (and assuming an Oracle database) but what you need to do is:

SELECT mo.title as title, AVG(mr.rating) as avg_rating
FROM movie mo, movie_rating mr
WHERE mo.movie_id = mr.movie_id
GROUP BY mo.title
ORDER BY avg_rating desc
LIMIT 20;

This should sort the data with the highest rating first.

This really depends on the size of the database. If the database is large, I would create a materialized view and refresh it when needed. If the database is small, a view would work just fine.

 

[raymondu999]

I'm actually running on MySQL. And maybe this wasn't clear in the first post, but I don't mean "top 20" as in records 1-20, but for example, say there are 400 records. I have to work out the 20 with the most votes, and highest ranking of votes.

 

[belvdr]

I'm actually running on MySQL. And maybe this wasn't clear in the first post, but I don't mean "top 20" as in records 1-20, but for example, say there are 400 records. I have to work out the 20 with the most votes, and highest ranking of votes.

That's what that does. I'll fix it for MySQL.

EDIT: Corrected for MySQL. It calculates the title and the average rating for that title. It then sorts by the average rating, and then limits the number of rows to 20.

 

[raymondu999]

woah. Thanks a bunch mate. Now it all clears up. The rownum < 21 was the one throwing me off. Any ideas on my second part though?

 

[belvdr]

SELECT ar.artist_name, mo.title as title, AVG(mr.rating) as avg_rating
FROM movie mo, movie_rating mr, artists ar, act
WHERE mo.movie_id = mr.movie_id
AND mo.movie_id = ar.movie_id
AND ar.artist_id = act.artist_id
AND ar.artist_name = 'Clint Eastwood'
GROUP BY ar.artist_name, mo.title
ORDER BY avg_rating desc;

I believe that will do it.

 

[raymondu999]

Hi there. Thanks for the tip, the first part worked wonders! However, for the second part I think I might've miscommunicated what I meant. I meant that I have these tables (sql is attached so that you can see how the db is structured).

Now, I have to take, for each celebrity, the movies that he/she starred in, the year of release of the movie, the "average rating" as from the query above, and the amount of awards. So for example, an artist Mr. Bob Smith might have the following returned for him: (attached)

I'm really stumped on this any help is appreciated

 

[raymondu999]

Anyone know how to do these?

 

[belvdr]

SELECT AVG(mr.rating) as avg_rating, mo.title as title, mo.release_date as Year, count(aa.artist_id) as Num_Awards
FROM movie mo, movie_rating mr, artists ar, cast_movie cm, artist_award aa
WHERE mo.movie_id = mr.movie_id
AND mo.movie_id = ar.movie_id
AND ar.artist_id = cm.artist_id
AND ar.artist_id = aa.artist_id
AND mo.movie_id = aa.movie_id
AND ar.artist_name = 'Clint Eastwood'
GROUP BY mo.title, mo.release_date
ORDER BY avg_rating asc;