stanford公开课sql基础题

发表于 更新于 分类于
本文字数： 16k 阅读时长 ≈ 15 分钟
stanford公开课sql基础题

sql基础题

一、课件题目

  Suppose you’ve started a new movie-rating website, and you’ve been collecting data on reviewers’ ratings of various movies. There’s not much data yet, but you can still try out some interesting queries. Here’s the schema:

  Movie ( mID, title, year, director ) There is a movie with ID number mID, a title, a release year, and a director.

  Reviewer ( rID, name ) The reviewer with ID number rID has a certain name.

  Rating ( rID, mID, stars, ratingDate ) [rID, mID are foreign keys] The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

  Write SQL statements to answer the following questions:

  1. Find the titles of all movies directed by Steven Spielberg.
  2. Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order of rating.
  3. Find the titles of all movies that have no ratings.
  4. Some reviewers didn’t provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.
  5. Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate.
  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.
  7. For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title. (hint: use GROUP BY)
  8. List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.
  9. Find the names of all reviewers who have contributed three or more ratings.
  10. Find the names of all reviewers who rated Gone with the Wind.
  11. For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.
  12. Return all reviewer names and movie names together in a single column, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing “The”.)
  13. Find the titles of all movies not reviewed by Chris Jackson.
  14. For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don’t pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order.
  15. For each rating that is the lowest (fewest stars) in the database, return the reviewer name, movie title, and number of stars of such ratings.
  16. Find the reviewer who reviewed all movies.
  17. Find the movies reviewed by Sarah Martinez and not reviewed by Chris Jackson. (hint: use NOT IN)
  18. Find the movies that have been reviewed by at least two reviewers. Please write an SQL with only one SELECT and without GROUP BY.
  19. Find the movies that have been reviewed by at least two reviewers. Please write an SQL with subquery.
  20. Find the reviewers who do not review any movie.

  Note: When you use XAMPP+MySQL to check the correctness of your solution, please be noted that EXCEPT is not supported in MySQL.

二、建表、数据

  基于SqlServer。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
--创建数据库
create database standford;

use standford;

--创建表
create table movie(
    mid int primary key ,
    title varchar(30),
    year int,
    director varchar(30)
);

create table reviewer(
    rid int primary key ,
    name varchar(30)
);

create table rating(
    mid int,
    rid int,
    stars int,
    ratingDate date
);

--外键约束
ALTER TABLE rating 
  WITH CHECK ADD CONSTRAINT fk_rating_movie_mid FOREIGN KEY (mid) references movie(mid);
ALTER TABLE rating 
  WITH CHECK ADD CONSTRAINT fk_rating_reviewer_rid FOREIGN KEY (rid) references reviewer(rid);

--填充数据
insert into movie(mid,title,year,director) values (101,'Gone with the Wind',1939,'Victor Fleming');
insert into movie(mid,title,year,director) values (102,'Star Wars',1977,'George Lucas');
insert into movie(mid,title,year,director) values (103,'The Sound of Music',1965,'Robert Wise');
insert into movie(mid,title,year,director) values (104,'E.T.',1982,'Steven Spielberg');
insert into movie(mid,title,year,director) values (105,'Titanic',1997,'James Cameron');
insert into movie(mid,title,year,director) values (106,'Snow White',1937,null);
insert into movie(mid,title,year,director) values (107,'Avatar',2009,'James Cameron');
insert into movie(mid,title,year,director) values (108,'Raiders of the Lost Ark',1981,'Steven Spielberg');

insert into reviewer(rid,name) values (201,'Sarah Martinez');
insert into reviewer(rid,name) values (202,'Daniel Lewis');
insert into reviewer(rid,name) values (203,'Brittany Harris');
insert into reviewer(rid,name) values (204,'Mike Anderson');
insert into reviewer(rid,name) values (205,'Chris Jackson');
insert into reviewer(rid,name) values (206,'Elizabeth Thomas');
insert into reviewer(rid,name) values (207,'James Cameron');
insert into reviewer(rid,name) values (208,'Ashley White');


insert into rating(rid,mid,stars,ratingDate) values (201,101,2,'2011-01-22');
insert into rating(rid,mid,stars,ratingDate) values (201,101,4,'2011-01-27');
insert into rating(rid,mid,stars,ratingDate) values (202,106,4,null);
insert into rating(rid,mid,stars,ratingDate) values (203,103,2,'2011-01-20');
insert into rating(rid,mid,stars,ratingDate) values (203,108,4,'2011-01-12');
insert into rating(rid,mid,stars,ratingDate) values (203,108,2,'2011-01-30');
insert into rating(rid,mid,stars,ratingDate) values (204,101,3,'2011-01-09');
insert into rating(rid,mid,stars,ratingDate) values (205,103,3,'2011-01-27');
insert into rating(rid,mid,stars,ratingDate) values (205,104,2,'2011-01-22');
insert into rating(rid,mid,stars,ratingDate) values (205,108,4,null);
insert into rating(rid,mid,stars,ratingDate) values (206,107,3,'2011-01-15');
insert into rating(rid,mid,stars,ratingDate) values (206,106,5,'2011-01-19');
insert into rating(rid,mid,stars,ratingDate) values (207,107,5,'2011-01-20');
insert into rating(rid,mid,stars,ratingDate) values (208,104,3,'2011-01-02');

三、作答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
--1. Find the titles of all movies directed by Steven Spielberg.  
select title
from movie
where director = 'Steven Spielberg';

--2. Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order of rating.  
select year
from movie
left join (select max(stars) as maxStars,mid from rating group by mid) maxRating
on movie.mid = maxRating.mid
where exists(select 1 
             from rating
			 where movie.mid = rating.mid
			   and rating.stars in (4,5))
order by (select max(stars) from rating where movie.mid = rating.mid group by mid),year;

--对应
select *
from movie
left join rating
on movie.mid = rating.mid
left join (select max(stars) as maxStars,mid from rating group by mid) maxRating
on movie.mid = maxRating.mid
where rating.stars in (4,5)
order by maxStars,year;

--3. Find the titles of all movies that have no ratings.  
select title
from movie
where not exists(select 1
             from rating
			 where movie.mid = rating.mid);

--4. Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.  
select name
from reviewer
where exists(select 1
             from rating
			 where reviewer.rid = rating.rid
			   and ratingDate is null);

--5. Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. 
select reviewer.name as reviewerName,
       movie.title as movieTitle,
	   rating.stars,
	   rating.ratingDate
from rating
left join movie on movie.mid = rating.mid
left join reviewer on reviewer.rid = rating.rid;

--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.  
select reviewer.name as reviewerName,
       movie.title as movieTitle,ratingDate,stars
from rating
left join movie on movie.mid = rating.mid
left join reviewer on reviewer.rid = rating.rid
left join (select max(ratingDate) as maxDate,max(stars) as maxStars,mid,rid
           from rating
		   group by mid,rid) maxSelect
on rating.rid = maxSelect.rid and rating.mid = maxSelect.mid
where rating.ratingDate = maxDate and rating.stars = maxStars
  and exists(select 1
             from rating r
	         group by mid,rid
	         having rating.rid = r.rid
	            and rating.mid = r.mid
	            and count(*) = 2);

--7. For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title. (hint: use GROUP BY)  
select movie.title,maxStars
from movie
left join (select max(stars) as maxStars,mid from rating group by mid) maxRating
on movie.mid = maxRating.mid
where exists(select 1 from rating where movie.mid = rating.mid group by mid having count(*) > 0)
order by movie.title;

--8. List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.  
select movie.title,AVG(stars) as avgStars
from rating
left join movie on movie.mid = rating.mid
group by movie.mid,movie.title
order by AVG(stars) desc,movie.title;

--9. Find the names of all reviewers who have contributed three or more ratings.  
select name
from reviewer
where exists(select 1
             from rating
			 where rating.rid = reviewer.rid
	         group by rid
			 having count(*) > 2);

--10. Find the names of all reviewers who rated Gone with the Wind. 
select name
from reviewer
where exists(select 1
             from rating
			 left join movie on rating.mid = movie.mid
			 where rating.rid = reviewer.rid
	           and title = 'Gone with the Wind');

--11. For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars. 
--查询导演作为评论人的电影，返回评论人名和电影名
select distinct reviewer.name as reviewerName,
       movie.title as movieTitle
from rating
left join movie on movie.mid = rating.mid
left join reviewer on reviewer.rid = rating.rid
where reviewer.name = movie.director;

--12. Return all reviewer names and movie names together in a single column, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The".) 
--把所有评论人名和电影名放入一列返回，按评论人名和标题名首字母排序，不需要对姓氏进行特殊处理或删除“the”
select title from movie
union
select name from reviewer
order by title;

--13. Find the titles of all movies not reviewed by Chris Jackson. 
--查询所有未受Chris Jackson评论的电影名
select title
from movie
where not exists(select 1
             from rating
			 left join reviewer on reviewer.rid = rating.rid
			 where rating.mid = movie.mid
	           and name = 'Chris Jackson'); 

--14. For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don't pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order. 
--当有两个评论人给同个电影评论时返回两个评论人名，删除重复项，按姓名排序

select * 
from(select case when reviewer1.name <= reviewer2.name then reviewer1.name else reviewer2.name end a, --去除顺序相关
            case when reviewer1.name <= reviewer2.name then reviewer2.name else reviewer1.name end b
            from rating r1
            join reviewer reviewer1 on r1.rid = reviewer1.rid
			join rating r2 on r1.mid = r2.mid and r1.rid != r2.rid --配对
			join reviewer reviewer2 on r2.rid = reviewer2.rid) m
group by a,b --分组去重
order by a,b; --按姓名排序

--顺序相关去重 select * from tb1 group by A, B having count(*) >0;
--顺序不相关去重 select aa,bb from (select case when a<=b then a else b end aa,case when a<=b then b else a end bb from tbl) tt group by aa,bb;

--15. For each rating that is the lowest (fewest stars) in the database, return the reviewer name, movie title, and number of stars of such ratings. 
--查询库中的最低评分，返回评论人名，电影名和评分
select reviewer.name as reviewerName,
       movie.title as movieTitle,
	   stars
from rating
left join movie on movie.mid = rating.mid
left join reviewer on reviewer.rid = rating.rid
where rating.stars = (select min(stars) from rating);

--16. Find the reviewer who reviewed all movies. 
select name
from reviewer
where (select count(*)
       from (SELECT distinct rid,mid
             from rating
             where reviewer.rid = rating.rid ) a ) --统计当前用户观看电影数
      = (select count(*) from movie); --统计电影总数

--17. Find the movies reviewed by Sarah Martinez and not reviewed by Chris Jackson. (hint: use NOT IN) 
--查询Sarah Martinez有评论而Chris Jackson未评论的电影
select title
from movie
where mid in (select mid from rating,reviewer where rating.rid = reviewer.rid and reviewer.name = 'Sarah Martinez')
  and mid not in (select mid from rating,reviewer where rating.rid = reviewer.rid and reviewer.name = 'Chris Jackson');

--18. Find the movies that have been reviewed by at least two reviewers. Please write an SQL with only one SELECT and without GROUP BY. 
select distinct a.mid
from rating as a
join rating as b 
on a.mid = b.mid 
and a.rid != b.rid;

--19. Find the movies that have been reviewed by at least two reviewers. Please write an SQL with subquery. 
select mid,title
from movie
where (select count(*) from rating where movie.mid = rating.mid) > 1;

--20. Find the reviewers who do not review any movie. 
select name
from reviewer
where (select count(*) from rating where reviewer.rid = rating.rid) = 0;

四、答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
1.SELECT DISTINCT title FROM movie Where director='Steven Spielberg';

2.SELECT DISTINCT Year,stars From movie, rating WHERE movie.mID=rating.mID and (rating.stars = 4 OR rating.stars= 5) ORDER BY rating.stars ASC;

3.SELECT DISTINCT title from movie where mID not in (SELECT mID from rating )
(mysql does not support except)(SELECT title FROM movie) EXCEPT (SELECT title FROM movie, rating  WHERE movie.mID = rating.mID)

4.SELECT DISTINCT Name FROM reviewer, rating WHERE reviewer.rID=rating.rID and rating.ratingDate is null;

5.SELECT DISTINCT name, title, stars, ratingDate FROM movie, rating, reviewer WHERE movie.mID = rating.mID AND reviewer.rID = rating.rID;
5.SELECT DISTINCT name, title, stars, ratingDate FROM movie join rating join reviewer ON movie.mID = rating.mID AND reviewer.rID = rating.rID;

6.SELECT DISTINCT name, title From movie , reviewer, rating, rating as rating2 WHERE rating.mID=movie.mID and reviewer.rID=rating.rID and rating.rID=rating2.rID and rating.mID = rating2.mID and rating.stars<rating2.stars and rating.ratingDate<rating2.ratingDate;

7.SELECT DISTINCT title , MAX(stars) From movie,rating WHERE movie.mID=rating.mID GROUP BY movie.mID Order by movie.title;

8.SELECT DISTINCT title, AVG (stars) from movie, rating WHERE movie.mID = rating.mID GROUP BY title ORDER BY AVG (stars) DESC, title ASC;

9.SELECT DISTINCT name From ((SELECT reviewer.name, count(rating.mID) From reviewer, rating Where reviewer.rID = rating.rID Group by reviewer.rID Having count(rating.mID) > 2) as r);
9.SELECT DISTINCT name From rating as r1, rating as r2, rating as r3, reviewer where reviewer.rID=r1.rID and r1.rID = r2.rID and r2.rID = r3.rID and (r1.mID <> r2.mID or r1.ratingDate<>r2.ratingDate) and (r2.mID<>r3.mID or r2.ratingDate<>r3.ratingDate) and (r1.mID<> r3.mID or r1.ratingDate<>r3.ratingDate);

10.SELECT DISTINCT name From movie, reviewer, rating Where movie.mID=rating.mID and reviewer.rID=rating.rID and movie.title='Gone with the Wind';

11.SELECT DISTINCT name, stars, title From movie, reviewer, rating Where reviewer.name=movie.director AND movie.mID=rating.mID and reviewer.rID=rating.rID;

12.SELECT DISTINCT name From (SELECT name From reviewer Union SELECT title as name From movie) as a Order by name

13.SELECT DISTINCT M.title from movie M, rating R where M.mID = R.mID and M.title not in (SELECT movie.title from movie,reviewer,rating where movie.mID= rating.mID and reviewer.rID = rating.rID and name ='Chris Jackson')

14.SELECT DISTINCT re1.name, re2.name FROM rating r1, rating r2, reviewer re1, reviewer re2 WHERE r1.rID != r2.rID AND r1.mID = r2.mID AND re1.rID = r1.rID AND re2.rID = r2.rID AND re1.name < re2.name;

15.SELECT DISTINCT name,title,stars FROM reviewer,movie,rating WHERE stars in (SELECT min(stars) From rating) AND reviewer.rid = rating.rid and movie.mid= rating.mid;

16.SELECT DISTINCT R.rID, R.name FROM reviewer R WHERE NOT EXISTS (SELECT mID FROM movie WHERE mID not in (SELECT mID FROM rating WHERE rID = R.rID));
(mysql does not support except)SELECT R.rID, R.name FROM reviewer R WHERE NOT EXISTS (SELECT mID FROM movie EXCEPT(SELECT mID FROM reviewer, rating WHERE reviewer.rID = rating.rID));

17.SELECT DISTINCT M.* from movie M, rating R, reviewer V where M.mID = R.mID and V.rID = R.rID and name ='Sarah Martinez' and title not in (SELECT title from movie M, rating R, reviewer V where M.mID = R.mID and V.rID = R.rID and name ='Chris Jackson')
(mysql does not support except)(SELECT M.* from movie M, rating R, reviewer V where M.mID = R.mID and V.rID = R.rID and name ='Tom') EXCEPT (SELECT * from movie M, rating R, reviewer V where M.mID = R.mID and V.rID = R.rID and name ='Jerry')

18.SELECT DISTINCT title From reviewer, rating, movie, rating as rating1 WHERE movie.mID=rating.mID and reviewer.rID=rating.rID and rating.rID!=rating1.rID and rating.mID = rating1.mID;

19.SELECT * from movie M where M.mID in (SELECT R.mID from rating R GROUP BY R.mID having count(rID) >= 2);

20.SELECT DISTINCT reviewer.name From reviewer where reviewer.name not in (SELECT reviewer.name From reviewer,rating Where reviewer.rID=rating.rID);
(mysql does not support except)SELECT reviewer.name From reviewer Except (SELECT reviewer.name From reviewer,rating Where reviewer.rID=rating.rID);

参考博客和文章书籍等：

stanford-sql课件第二章英文版

因博客主等未标明不可引用，若部分内容涉及侵权请及时告知，我会尽快修改和删除相关内容