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.
mID | title | year | director |
---|---|---|---|
101 | Gone with the Wind | 1939 | Victor Fleming |
102 | Star Wars | 1977 | George Lucas |
103 | The Sound of Music | 1965 | Robert Wise |
104 | E.T. | 1982 | Steven Spielberg |
105 | Titanic | 1997 | James Cameron |
106 | Snow White | 1937 | <null> |
107 | Avatar | 2009 | James Cameron |
108 | Raiders of the Lost Ark | 1981 | Steven Spielberg |
Reviewer
rID | name |
---|---|
201 | Sarah Martinez |
202 | Daniel Lewis |
203 | Brittany Harris |
204 | Mike Anderson |
205 | Chris Jackson |
206 | Elizabeth Thomas |
207 | James Cameron |
208 | Ashley White |
Rating(这里的stars是几星的意思,就是评价)
rID | mID | stars | ratingDate |
---|---|---|---|
201 | 101 | 2 | 2011-01-22 |
201 | 101 | 4 | 2011-01-27 |
202 | 106 | 4 | <null> |
203 | 103 | 2 | 2011-01-20 |
203 | 108 | 4 | 2011-01-12 |
203 | 108 | 2 | 2011-01-30 |
204 | 101 | 3 | 2011-01-09 |
205 | 103 | 3 | 2011-01-27 |
205 | 104 | 2 | 2011-01-22 |
205 | 108 | 4 | <null> |
206 | 107 | 3 | 2011-01-15 |
206 | 106 | 5 | 2011-01-19 |
207 | 107 | 5 | 2011-01-20 |
208 | 104 | 3 | 2011-01-02 |
1 select title from Movie 2 where director='Steven Spielberg
1 select distinct Movie.year from Movie 2 join Rating 3 on Movie.mID=Rating.mID and (Rating.stars=4 or Rating.stars=5) 4 order by Movie.year
1 select distinct Movie.title from Movie 2 left join Rating 3 on Movie.mID=Rating.mID 4 where Rating.mID is null
1 select Reviewer.name from Reviewer 2 join Rating 3 on Reviewer.rID=Rating.rID 4 where Rating.ratingDate is null
1 select Reviewer.name,Movie.title,Rating.stars,Rating.ratingDate from 2 (Reviewer join Rating) join Movie 3 on Reviewer.rID=Rating.rID and Rating.mID=Movie.mID 4 order by Reviewer.name,Movie.title,Rating.stars
Question 6:For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.
1 select Reviewer.name,Movie.title from Movie join 2 (select Rating.mID,Rating.rID,Rating.stars,Rating.ratingDate from Rating, 3 (select Rating.mID,Rating.rID,max(Rating.stars) as maxStars,max(Rating.ratingDate) as maxDate from Rating 4 group by mID,rID 5 having count(rId)=2 and count(mID)=2) as nan 6 where Rating.mID=nan.mID and Rating.rID=nan.rID and Rating.stars=nan.maxStars and Rating.ratingDate=maxDate) as nannan 7 on Movie.mID=nannan.mID join Reviewer on Reviewer.rID=nannan.rID
或者:
select Movie.title,Reviewer.name from Movie join
(select Rating.rID,Rating.mID from Rating,
(select Rating.rID,Rating.mID,max(Rating.stars) as maxStar,max(Rating.ratingDate) as maxDate from Rating
group by Rating.rID,Rating.mID
having count(rID)=2) as nan
where nan.maxStar=Rating.Stars and nan.maxDate=Rating.ratingDate) as nannan
on Movie.mID=nannan.mID
join Reviewer on Reviewer.rID=nannan.rID
1 select Movie.title,nan.maxStars from Movie join 2 (select Rating.mID,max(Rating.stars) as maxStars from Rating 3 group by mID) as nan 4 on Movie.mID=nan.mID 5 ORDER BY Movie.title
1 select Movie.title,nan.avgStars from Movie join 2 (select Rating.mID,avg(Rating.stars) as avgStars from Rating 3 group by Rating.mID 4 ) as nan 5 on Movie.mID=nan.mID 6 order by nan.avgStars desc,Movie.title
Question 9:Find the names of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.)
常规方法:
1 select Reviewer.name from Reviewer join 2 (select Rating.rID,count(Rating.rID) as countRID from Rating 3 group by rID 4 having countRID>=3) as nan 5 on nan.rID=Reviewer.rID
非常规方法暂时还没有想到。
Question 10: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.
1 select Movie.title,nan.spread from Movie join 2 (select Rating.mID,(max(Rating.stars)-min(Rating.stars)) as spread from Rating 3 group by Rating.mID)as nan 4 on Movie.mID=nan.mID 5 order by nan.spread desc,Movie.title
未完待续。。。
SELECT tbl_info.name, tbl_info.num, tbl_info.phone, tbl_info.faculty, tbl_info.qq, tbl_grade.name, tbl_class.name FROM tbl_info JOIN tbl_grade JOIN tbl_class ON tbl_info.grade_id = tbl_grade.id AND tbl_info.class_id = tbl_class.id
。。。