斯坦福大学公开讲座 DB5 SQL（2014/6/6）练习题

1-SQL-Movie-Rating-Query-Exercises.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第一部分：SQL Movie-Rating Query Exercises*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

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

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

/*练习题及其解答*/
/*---------- 1. SQL Movie-Rating Query Exercises ----------*/
-- Q1 Find the titles of all movies directed by Steven Spielberg. 
-- Q1 查找导演Steven Spielberg执导的全部影片的片名
select title from Movie where director="Steven Spielberg";

-- Q2 Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.
-- Q2 查找评价为4和5的电影的发布年，按照发布年顺序排列
select distinct year from Movie 
where mId in (select mID from rating where stars>3) order by year;

-- Q3 Find the titles of all movies that have no ratings. 
-- Q3 查找所有没有被评价的电影
select distinct title from Movie 
where mID in (select mID from Movie where mID not in (select mID from Rating));

select title from movie where mID not in (select mID from rating);

-- Q4 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. 
-- Q4 有些评论家没有提供评论日期，请找出这些评论家的姓名
select distinct name from Reviewer 
where rID in (select rID from Rating where ratingDate is null);

-- Q5 Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars. 
-- Q5 请列出详细的影评信息：评论家姓名、电影名、评价、评价日，按照评价者姓名、电影名、评价排序
select name,title,stars,ratingDate 
from Reviewer,Rating,Movie 
where Reviewer.rID=Rating.rID and Movie.mID=Rating.mID 
order by name, title, stars;

-- Q6 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. 
-- Q6 有的评论家对同一部电影评价了2次，而且第二次的评价高于第一次，请列出这些评论家及电影名
select name, title from Reviewer, Movie, Rating, Rating r2
where Rating.mID=Movie.mID and Reviewer.rID=Rating.rID 
  and Rating.rID = r2.rID and r2.mID = Movie.mID
  and Rating.stars < r2.stars and Rating.ratingDate < r2.ratingDate;

-- Q7 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. 
-- Q7 每部电影至少有一次评价，找出所有电影获得的最高评价，显示电影名和评价，按照电影名排序
select title,max(stars) from Movie, Rating 
where Movie.mID=Rating.mID 
group by Rating.mID order by title;

-- Q8 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. 
-- Q8 查找电影评价的偏差值，偏差值是指某电影获得的较高评价和较低评价的差值。按照偏差值降序、电影名升序排序
select title,max(stars)-min(stars) as spread from Movie, Rating 
where Movie.mID=Rating.mID group by title order by spread desc;

-- Q9 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.) 
-- Q9 查找1980年以前的电影的平均评价与1980年以后的电影的平均评价之差
select  down.av1980down - up.av1980up 
from	(select avg(avgst) as av1980up from 
		(select title, avg(stars) avgst
		 from Movie,Rating
		 where year>1980 and Movie.mID=Rating.mID group by title
		)
	) as up,
	(select avg(avgst) as av1980down from
                (select title, avg(stars) avgst
                 from Movie,Rating
                 where year<1980 and Movie.mID=Rating.mID group by title
                )
        ) as down;

2-SQL-Movie-Rating-Query-Exercises-Extras.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第二部分：SQL Movie-Rating Query Exercises Extras*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

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

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

/*练习题及解答*/
-- 1. Find the names of all reviewers who rated Gone with the Wind. 
-- 1. 查找评价了电影Gone with the Wind的评论家姓名
SELECT DISTINCT name
FROM Movie
INNER JOIN Rating USING(mId)
INNER JOIN Reviewer USING(rId)
WHERE title = "Gone with the Wind";


-- 2. 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. 
-- 2. 某评论家与该电影的导演同名，请列出评论家姓名、电影名、评价
SELECT name, title, stars
FROM Movie
INNER JOIN Rating USING(mId)
INNER JOIN Reviewer USING(rId)
WHERE director = name;


-- 3. Return all reviewer names and movie names together in a single list, 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".) 
-- 3. 列出所有评论家姓名和电影名，输出到一个列表中，按照字母顺序排序
SELECT title FROM Movie
UNION
SELECT name FROM Reviewer
ORDER BY name, title;


-- 4. Find the titles of all movies not reviewed by Chris Jackson. 
-- 4. 列出没有被Chris Jackson评价的所有电影
SELECT title
FROM Movie
WHERE mId NOT IN (
  SELECT mId
  FROM Rating
  INNER JOIN Reviewer USING(rId)
  WHERE name = "Chris Jackson"
);


-- 5. 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.
-- 5. 查找评审了同一部电影的2名评论家，返回2名评审家的姓名，去除重复的，不显示评论家自己的2次评价，一对评论家只显示一次，2名评论家按照字母顺序排序
select distinct name1,name2
from
  (select r1.rid rid1,r1.name name1,ra.mid m1 from reviewer r1,rating ra where r1.rID=ra.rid),
  (select r2.rid rid2,r2.name name2,ra.mid m2 from reviewer r2,rating ra where r2.rID=ra.rid)
where name1<name2 and m1=m2
order by name1,name2;


-- 6. For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars.
-- 6. 查找评价最低的电影，列出评价者、电影名、评价
SELECT name, title, stars
FROM Movie
INNER JOIN Rating USING(mId)
INNER JOIN Reviewer USING(rId)
WHERE stars = (SELECT MIN(stars) FROM Rating);


-- 7. 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. 
-- 7. 列出电影名及其平均评价，从高评价到低评价，评级相同时按照字母顺序
SELECT title, AVG(stars) AS average
FROM Movie
INNER JOIN Rating USING(mId)
GROUP BY mId
ORDER BY average DESC, title;


-- 8. Find the names of all reviewers who have contributed three or more ratings.
-- 8. 列出评价了3次或者更多次数的评论家姓名
SELECT name
FROM Reviewer
WHERE (SELECT COUNT(*) FROM Rating WHERE Rating.rId = Reviewer.rId) >= 3;

SELECT name
FROM Reviewer
INNER JOIN Rating USING(rId)
GROUP BY rId
HAVING COUNT(*) >= 3;

-- At least 3 ratings to different movies (Remainder to myself)

SELECT name
FROM Reviewer
WHERE (SELECT COUNT(DISTINCT mId) FROM Rating WHERE Rating.rId = Reviewer.rId) >= 3;


-- 9. Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title.
-- 9. 有些导演编导了2部以上的电影，请找到这些导演，列出他们编导的电影名及导演名，按照导演名、电影名排序
SELECT title, director
FROM Movie M1
WHERE (SELECT COUNT(*) FROM Movie M2 WHERE M1.director = M2.director) > 1
ORDER BY director, title;

SELECT M1.title, director
FROM Movie M1
INNER JOIN Movie M2 USING(director)
GROUP BY M1.mId
HAVING COUNT(*) > 1
ORDER BY director, M1.title;

select title,director
from movie
where director in
  (select director from movie group by director having count(*) > 1)
order by director,title; 
-- 10. Find the movie(s) with the highest average rating. Return the movie title(s) and average rating.
-- 10. 查找平均评价最高的电影，列出电影名及平均评价
SELECT title, AVG(stars) AS average
FROM Movie
INNER JOIN Rating USING(mId)
GROUP BY mId
HAVING average = (
  SELECT MAX(average_stars)
  FROM (
    SELECT title, AVG(stars) AS average_stars
    FROM Movie
    INNER JOIN Rating USING(mId)
    GROUP BY mId
  )
);

select title,maxavgstars
from movie inner join
  (select mid,max(avgstars) maxavgstars
  from 
    (select mid,avg(stars) avgstars
    from rating
    group by mid)) a
  on movie.mid=a.mid;

-- 11. Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating.
-- 11. 查找平均评价最低的电影，列出电影名及平均评价
SELECT title, AVG(stars) AS average
FROM Movie
INNER JOIN Rating USING(mId)
GROUP BY mId
HAVING average = (
  SELECT MIN(average_stars)
  FROM (
    SELECT title, AVG(stars) AS average_stars
    FROM Movie
    INNER JOIN Rating USING(mId)
    GROUP BY mId
  )
);


-- 12. For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL. 
-- 12. 查找各导演在他们执导的电影中获得最高评价的电影，列出导演名、电影名、评价，不考虑导演名为空的电影
SELECT director, title, MAX(stars)
FROM Movie
INNER JOIN Rating USING(mId)
WHERE director IS NOT NULL
GROUP BY director;

3-SQL-Social-Network-Query-Exercises.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第三部分：SQL Social-Network Query Exercises*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

/*练习题及其解答*/
/*----------- 1. SQL Social-Network Query Exercises  -----------*/
-- Q1 Find the names of all students who are friends with someone named Gabriel.
-- Q1 查找所有友人中有Gabriel的人
select distinct name from Highschooler 
where ID in 
  (select ID1 from Friend where ID2 in 
    (select ID from Highschooler where name="Gabriel"));

select h1.[name]
from highschooler h1,highschooler h2,friend f
where h1.id=f.id1 and f.id2=h2.id and h2.[name]='Gabriel';

-- Q2 For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like. 
-- Q2 查找喜欢比自己低2级以上人的人，列出姓名、年级、他们喜欢的人的姓名及年级
select distinct sName, sGrade, lName, lGrade
from (select h1.name as sName, h1.grade sGrade, h2.name as lName, h2.grade as lGrade, h1.grade-h2.grade as gradeDiff 
     from Highschooler h1, Likes, Highschooler h2
     where h1.ID=ID1 and h2.ID=ID2)
where gradeDiff>1;

select h1.name,h1.grade,h2.name,h2.grade
from highschooler h1,highschooler h2,likes l
where h1.id=l.id1 and l.id2=h2.id and h1.grade-h2.grade >1;
 
-- Q3 For every pair of students who both like each other, return the name and grade of both students. Include each pair only once, with the two names in alphabetical order.
-- Q3 查找互相喜欢的学生，列出双方的姓名及年级，每对学生只列一次，按照姓名顺序
select h1.name, h1.grade, h2.name, h2.grade  from Likes l1, Likes l2, Highschooler h1, Highschooler h2
where l1.ID1=l2.ID2 and l2.ID1=l1.ID2 and l1.ID1=h1.ID and l1.ID2=h2.ID and h1.name<h2.name;
 
-- Q4 Find all students who do not appear in the Likes table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.
-- Q4 查找没有出现在Likes表中的所有学生，列出姓名及年级，按照年级、姓名排序
select name,grade from Highschooler 
where ID not in (select ID1 from Likes union select ID2 from Likes) order by grade, name;
 
-- Q5 For every situation where student A likes student B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades. 
-- Q5 查找下面这些学生：学生A喜欢学生B,但是没有学生B的喜好信息（B不在Likes表的ID1中），列出学生的姓名及年级
select distinct H1.name, H1.grade, H2.name, H2.grade
from Highschooler H1, Likes, Highschooler H2
where H1.ID = Likes.ID1 and Likes.ID2 = H2.ID and H2.ID not in (select ID1 from Likes);
 
-- Q6 Find names and grades of students who only have friends in the same grade. Return the result sorted by grade, then by name within each grade. 
-- Q6 查找只有同年级朋友的学生，列出学生名及年级，按照年已和姓名排序
select name, grade from Highschooler
where ID not in (
  select ID1 from Highschooler H1, Friend, Highschooler H2
  where H1.ID = Friend.ID1 and Friend.ID2 = H2.ID and H1.grade <> H2.grade)
order by grade, name;
 
-- Q7 For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C. 
-- Q7 查找下列学生：学生A喜欢学生B但两人不是朋友，学生C是学生A和学生B的朋友，列出学生A,B,C的姓名和年级
select distinct H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
from Highschooler H1, Likes, Highschooler H2, Highschooler H3, Friend F1, Friend F2
where H1.ID = Likes.ID1 and Likes.ID2 = H2.ID and
  H2.ID not in (select ID2 from Friend where ID1 = H1.ID) and
  H1.ID = F1.ID1 and F1.ID2 = H3.ID and
  H3.ID = F2.ID1 and F2.ID2 = H2.ID;
 
-- Q8 Find the difference between the number of students in the school and the number of different first names.
-- Q8 查找学生数与学生名个数的差异
select st.sNum-nm.nNum from 
(select count(*) as sNum from Highschooler) as st,
(select count(distinct name) as nNum from Highschooler) as nm;
 
-- Q9 Find the name and grade of all students who are liked by more than one other student.
-- Q9 查找被1人以上学生喜欢的学生姓名及年级
select name, grade 
from (select ID2, count(ID2) as numLiked from Likes group by ID2), Highschooler
where numLiked>1 and ID2=ID;

4-SQL-Social-Network-Query-Exercises-Extras.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第四部分：SQL Social-Network Query Exercises Extras*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

/*练习题及解答*/
-- Q1 For every situation where student A likes student B, but student B likes a different student C, return the names and grades of A, B, and C.
-- Q1 查找满足下面条件的学生：学生A喜欢学生B，学生B喜欢学生C，学生C与学生A不相同，列出学生A,B,C的姓名及年级
select h1.name,h1.grade,h2.name,h2.grade,h3.name,h3.grade
from highschooler h1,highschooler h2,highschooler h3,likes l1,likes l2
where h1.id=l1.id1 and l1.id2=h2.id and l2.id1=h2.id and l2.id2=h3.id and h3.id <> h1.id;

-- Q2 Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades. 
-- Q2 查找这样的学生：他们的所有朋友都不是同一年级的，列出学生名及年级
select name, grade
from Highschooler h1
where id not in (select h1.id 
  from highschooler h1,Highschooler h2,friend f 
  where h1.id=f.id1 and f.id2=h2.id and h1.grade=h2.grade);
  
-- Q3 What is the average number of friends per student? (Your result should be just one number.) 
-- Q3 计算每个学生的平均朋友数
select avg(fnumber)
from (select count(*) fnumber
  from friend      
  group by id1);
  
-- Q4 Find the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend. 
-- Q4 查找满足条件的学生数，他是Cassandra的朋友，或者是Cassandra的朋友的朋友，不包括Cassandra。
select count(distinct(h1.id))
from highschooler h1,highschooler h2,highschooler h3,friend f1,friend f2
where (h1.id=f1.id1 and f1.id2=h2.id and h2.name='Cassandra' and h1.name <> 'Cassandra') or
  (h1.id=f1.id1 and f1.id2=h2.id and f2.id1=h2.id and f2.id2=h3.id and h3.name='Cassandra' 
  and h1.name <> 'Cassandra');
  
  -- Q5 Find the name and grade of the student(s) with the greatest number of friends
  -- Q5 查找朋友数最多的学生的姓名和年级
select name,grade
from friend f,highschooler h
where f.id1=h.id
group by id1
having count(*)=
  (select max(number)
  from
    (select id1,count(id1) number
    from friend
    group by id1));

5-SQL-Movie-Rating-Modification-Exercises.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第五部分：SQL Movie-Rating Modification Exercises*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

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

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

/*练习题及解答*/
-- Q1 Add the reviewer Roger Ebert to your database, with an rID of 209. 
-- Q1 追加一名评论家Roger Ebert,rID为209
insert into reviewer values(209,'Roger Ebert');

-- Q2 Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL. 
-- Q2 插入James Cameron对所有电影的5星评价，评价日不设置（NULL）
insert into Rating  
select rID,mID,5,null from Reviewer, Movie  
where name="James Cameron";

-- Q3 For all movies that have an average rating of 4 stars or higher, add 25 to the release year. (Update the existing tuples; don't insert new tuples.)
-- Q3 将所有平均评价为4星或更高的电影的发行年增加25（更新已有数据，不要追加数据），
update Movie set year=year+25 where
mID in (select mID from (select Rating.mID,avg(stars) as average  
  from Movie, Rating where Movie.mID=Rating.mID 
  group by Rating.mID, year) where average>=4);

-- Q4 Remove all ratings where the movie's year is before 1970 or after 2000, and the rating is fewer than 4 stars. 
-- Q4 删除电影发行年在1970年之前或者2000年之后，并且评价小于4星的所有评价
delete from rating
where mid in (select mid from movie where year<=1970 or year>=2000)
  and stars<4;

6-SQL-Social-Network-Modification-Exercises.sql

/*这是斯坦福大学网络公开课DB5 SQL的练习题解答*/
/*第六部分：SQL Social-Network Modification Exercises*/
/*数据环境准备*/
/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

/*练习题及解答*/
-- Q1 It's time for the seniors to graduate. Remove all 12th graders from Highschooler.
-- Q1 高年级毕业时期，删除所有12年级的学生
delete from Highschooler
where grade =12；

-- Q2 If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.
-- Q2 如果两名学生A和B是朋友，而且A喜欢B,但B不喜欢A，则删除这对喜欢。
delete from likes
where id1 in 
  (select likes.id1 
  from friend join likes using (id1) 
  where friend.id2 = likes.id2) 
  and not id2 in (select likes.id1 
    from friend join likes using (id1) 
    where friend.id2 = likes.id2)

-- Q3 For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. Do not add duplicate friendships, friendships that already exist, or friendships with oneself.
-- Q3 如果A是B的朋友，B是C的朋友，则追加A与C的朋友关系。不能重复追加、不能追加已经存在的关系、不能追加自己与自己的关系
insert into friend
select f1.id1, f2.id2
from friend f1 join friend f2 on f1.id2 = f2.id1
where f1.id1 <> f2.id2
except
select * from friend