x

SQL query help..

The table details are as follows:

Movie ( mID, title, year, director )

English: There is a movie with ID number mID, a title, a release year, and a director.

Reviewer ( rID, name )

English: The reviewer with ID number rID has a certain name.

Rating ( rID, mID, stars, ratingDate )

English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

I need to write a query base on the following question:

  1. For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title.

2)Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

Any assistance will be great.

more ▼

asked Oct 26, 2011 at 06:50 AM in Default

avatar image

asiandoll
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As very similar question has already been answered once yesterday here: I suspect a homework tag needs to be added to the post...

more ▼

answered Oct 26, 2011 at 06:56 AM

avatar image

BarneyL
62 1

(comments are locked)
10|1200 characters needed characters left

Enjoy !)

select avg(avgOld) - avg(avgNew) from ((SELECT AVG(avgStars) as avgOld from (SELECT r.mID, AVG(stars) AS avgStars FROM Rating as r inner join Movie as m on m.mID=r.mID where m.year<1980 GROUP BY r.mID)) from (SELECT AVG(avgStars) as avgOld from (SELECT r.mID, AVG(stars) AS avgStars FROM Rating as r inner join Movie as m on m.mID=r.mID where m.year<1980 GROUP BY r.mID))

more ▼

answered Nov 07, 2011 at 12:48 AM

avatar image

Manuk
1

(comments are locked)
10|1200 characters needed characters left
 SELECT  ( SELECT    AVG(oldiestar) AS ldiestar
           FROM      ( SELECT    Movie.title AS oldietitle ,
                                 AVG(Rating.stars) AS oldiestar
                       FROM      Rating
                                 INNER JOIN Reviewer ON Rating.rID = Reviewer.rID
                                 INNER JOIN Movie ON Rating.mID = Movie.mID
                       GROUP BY  Movie.title
                       HAVING    Movie.year < 1980
                     )
         )
         - ( SELECT  AVG(newstar) AS ewstar
             FROM    ( SELECT    Movie.title AS newtitle ,
                                 AVG(Rating.stars) AS newstar
                       FROM      Rating
                                 INNER JOIN Reviewer ON Rating.rID = Reviewer.rID
                                 INNER JOIN Movie ON Rating.mID = Movie.mID
                       GROUP BY  Movie.title
                       HAVING    Movie.year > 1980
                     )
           )
more ▼

answered Feb 20, 2014 at 11:02 PM

avatar image

tomas.polak.85
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x461
x113

asked: Oct 26, 2011 at 06:50 AM

Seen: 3147 times

Last Updated: Feb 20, 2014 at 11:04 PM

Copyright 2018 Redgate Software. Privacy Policy