My answer for SQL_Quiz of Stanford Database course

SQL_Quiz

These quizs are very interesting and helpful. It's for query and data manipulation. Two small databases used to demonstrate basic query syntax, various subquery syntax and some DML.

SQL_Quiz1.sql

/*---------- SQL Movie-Rating Query Exercises (core set) ----------*/
-- Q1 Find the titles of all movies directed by 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.
SELECT distinct year 
from Movie inner join Rating using(mid)
where stars between 4 and 5
order by year asc;
 
-- Q3 Find the titles of all movies that have no ratings. 
select title
from Movie
where Movie.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. 
select distinct Reviewer.name
from Rating join Reviewer using(rID)
Where Rating.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. 
select name, title, stars, ratingDate
from Rating natural join Reviewer natural join Movie
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. 
select DISTINCT name, title
from Rating R1 natural join Movie natural join Reviewer
where R1.mID in( select mID as counter from Rating R2 where R2.rID = R1.rID group by R2.mID having count(*) = 2) 
  and (select stars from Rating R3 where R3.rID = R1.rID and R1.mID = R3.mID order by R3.ratingDate LIMIT 1) 
  < (select stars from Rating R3 where R3.rID = R1.rID and R1.mID = R3.mID order by R3.ratingDate DESC LIMIT 1);
 
-- 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. 
select title,max(stars)
from Movie natural join Rating
group by 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. 
select title,max(stars)-min(stars) as spread
from Movie natural join Rating
group by mID
order by spread DESC, title;
 
-- 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.) 
select downav-upav
from (select avg(av) as upav
    from (select avg(stars) as av
      from Movie natural join Rating
      where year > 1980
      group by mID) as u ) as up,
  (select avg(av) as downav
    from (select avg(stars) as av
      from Movie natural join Rating
      where year < 1980
      group by mID) as d
) as down
 
 

SQL_Quiz3.sql

/*---------- SQL Movie-Rating Modification Exercises ----------*/
-- Q1 Add the reviewer Roger Ebert to your database, with an rID of 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.
nsert into Rating
select (select rID from Reviewer where name = 'James Cameron') as rID, mID, 5, null
from Movie;
 
-- 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.)
update Movie
set year = year + 25
where (select avg(stars) from Rating where Rating.mID = Movie.mID group by Rating.mID) >= 4;

-- Q4 Remove all ratings where the movie's year is before 1970 or after 2000, and the rating is fewer than 4 stars.
delete from Rating
where ( (select year from Movie where Movie.mID = Rating.mID) not between 1970 and 2000) and stars < 4;

SQL_Quiz4.sql

/*---------- SQL Social-Network Query Exercises (core set) ----------*/
-- Q1 Find the names of all students who are friends with someone named Gabriel.
select name
from Highschooler
where ID in (select ID2 from Friend F
	where F.ID1 in (select ID from Highschooler where 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. 
select H1.name, H1.grade, H2.name, H2.grade
from Highschooler H1 join Highschooler H2 join Likes 
on H1.ID = ID1 and H2.ID = ID2 and H1.grade -2 >= H2.grade;
 
-- 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.
select H1.name, H1.grade, H2.name, H2.grade
from Likes L1 join Likes L2 join Highschooler H1 join Highschooler H2
on L1.ID2 = L2.ID1 and L2.ID2 = L1.ID1 and L1.ID1 = H1.ID and L1.ID2 = H2.ID
where 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.
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.
select H1.name, H1.grade, H2.name, H2.grade
from Likes join Highschooler H1 join Highschooler H2
on ID1 = H1.ID and ID2 = H2.ID
where ID2 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. 
select H1.name, H1.grade from Friend F1 join Highschooler H1 join Highschooler H2
on F1.ID1 = H1.ID and F1.ID2 = H2.ID
group by F1.ID1
having max(H1.grade <> H2.grade) = 0
order by H1.grade, H1.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.
select H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
from Likes L join Friend F1 join Friend F2 join Highschooler H1 join Highschooler H2 join Highschooler H3
on L.ID1 = H1.ID and L.ID2 = H2.ID and F1.ID2 = H3.ID
where not exists (select * from Friend F where L.ID1 = F.ID1 and L.ID2 = F.ID2) 
	and F1.ID1 = L.ID1 and F1.ID2 = F2.ID1 and F2.ID2 = L.ID2;
	
-- Q8 Find the difference between the number of students in the school and the number of different first names. 
select (select count(*) from Highschooler) - (select count(distinct name) from Highschooler);
 
-- Q9 Find the name and grade of all students who are liked by more than one other student. 
select H2.name, H2.grade
from Likes join Highschooler H1 join Highschooler H2
on ID1 = H1.ID and ID2 = H2.ID
group by ID2
having count(*) > 1;

SQL_Quiz6.sql

/*---------- SQL Social-Network Modification Exercises ----------*/
-- Q1 It's time for the seniors to graduate. Remove all 12th graders from Highschooler.
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.
delete from Likes
where (not exists (select * from Likes L where L.ID1 = Likes.ID2 and L.ID2 = Likes.ID1) ) and exists (select * from Friend F where F.ID1 = Likes.ID1 and F.ID2 = Likes.ID2);
--wired: must use Likes.*
 
-- 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. (This one is a bit challenging; congratulations if you get it right.)
insert into Friend
select distinct F1.ID1, F2.ID2
from Friend F1 join Friend F2
on F1.ID2 = F2.ID1 and F1.ID1 <> F2.ID2
where not exists (select * from Friend F where F.ID1 = F1.ID1 and F.ID2 = F2.ID2);

SQL_Quiz_db1.sql

/* 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');

SQL_Quiz_db2.sql

/* 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);