1048.MovieEx-Find the movies with the highest average rating
题目描述
Find the movie(s) with the highest average
rating. Return the movie title(s) and
average rating(avg_rating). (Hint: This
query is more difficult to write in SQLite than other systems; you might think
of it as finding the highest average rating and then choosing the movie(s) with
that average rating.)
You can find the full
data description here
movie ( mID, title, year,
director )
English: There is a movie with ID number mID,
a title, a release year, and a director.
| 
   mID  | 
  
   title  | 
  
   year  | 
  
   director  | 
 
| 
   int  | 
  
   varchar  | 
  
   int  | 
  
   varchar  | 
 
| 
   101  | 
  
   Gone with the Wind  | 
  
   1939  | 
  
   Victor Fleming  | 
 
| 
   102  | 
  
   Star Wars  | 
  
   1977  | 
  
   George Lucas  | 
 
reviewer ( rID, name )
English: The reviewer with ID number rID
has a certain name.
| 
   rID  | 
  
   name  | 
 
| 
   int  | 
  
   varchar  | 
 
| 
   201  | 
  
   Sarah Martinez  | 
 
| 
   202  | 
  
   Daniel Lewis  | 
 
rating ( rID, mID, stars,
ratingDate )
English: The reviewer rID gave the
movie mID a number of stars rating (1-5) on a certain ratingDate.
| 
   rID  | 
  
   mID  | 
  
   stars  | 
  
   ratingDate  | 
 
| 
   int  | 
  
   int  | 
  
   int  | 
  
   date  | 
 
| 
   201  | 
  
   101  | 
  
   2  | 
  
   2011-01-22  |