StanfordOnline - Databases: Relational Databases and SQL

SQL Movie - Rating Query Exercises Extras.sql

--- 1. Find the names of all reviewers who rated Gone with the Wind. 

SELECT DISTINCT name 
FROM Movie 
JOIN rating USING(mID)
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. 

SELECT name, title, stars FROM
Movie JOIN rating USING(mID)
JOIN reviewer using(rID)
WHERE name = director;


--- 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".) 

SELECT DISTINCT name 
FROM reviewer
UNION
SELECT distinct title 
FROM movie
ORDER BY 1;

--- 4. Find the titles of all movies not reviewed by Chris Jackson. 

SELECT title FROM movie
WHERE mID NOT IN (
  SELECT mID FROM
  rating 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.

SELECT DISTINCT Rw1.name, Rw2.name
FROM Movie
INNER JOIN Rating R1 USING(mID)
INNER JOIN Rating R2 USING(mID)  
INNER JOIN Reviewer Rw1 ON R1.rID = Rw1.rID
INNER JOIN Reviewer Rw2 ON R2.rID = Rw2.rID
WHERE R1.mId = R2.mId AND Rw1.name < Rw2.name
ORDER by Rw1.name;

--- alternative

SELECT DISTINCT Rw1.name, Rw2.name
FROM Rating R1, Rating R2, Reviewer Rw1, Reviewer Rw2
WHERE R1.mID = R2.mID
AND R1.rID = Rw1.rID
AND R2.rID = Rw2.rID
AND Rw1.name < Rw2.name
ORDER BY Rw1.name, Rw2.name;


--- 6. For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars.

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. 

SELECT title, avg(stars)
FROM movie INNER JOIN rating USING(mID)
GROUP BY title
ORDER BY avg(stars) DESC;


--- 8. Find the names of all reviewers who have contributed three or more ratings.

SELECT name
FROM Reviewer 
INNER JOIN Rating USING(rID)
GROUP BY name
HAVING COUNT()>=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.

SELECT title, director
FROM movie WHERE
director IN (
  SELECT director 
  FROM movie
  GROUP BY director
  HAVING count()>1
)
ORDER BY director, title;

--- alternative

SELECT title, director
FROM Movie M1
WHERE (SELECT COUNT(*) FROM Movie M2 WHERE M1.director = M2.director) > 1
ORDER BY director, title;


--- 10. Find the movie(s) with the highest average rating. Return the movie title(s) and average rating.

SELECT title, avg(stars)
FROM movie 
INNER JOIN rating USING(mID)
GROUP BY mID
HAVING avg(stars) = (
  SELECT max(avg_stars) 
  FROM (
    SELECT avg(stars) AS avg_stars 
    FROM rating 
    GROUP BY mID
    )
  );

--- 11. Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating.

SELECT title, avg(stars)
FROM movie 
INNER JOIN rating USING(mID)
GROUP BY mID
HAVING avg(stars) = (
  SELECT min(avg_stars) 
  FROM (
    SELECT avg(stars) AS avg_stars 
    FROM rating 
    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. 

SELECT director, title, MAX(stars)
FROM Movie
INNER JOIN Rating USING(mId)
WHERE director IS NOT NULL
GROUP BY director;

--- alternative

SELECT DISTINCT director, title, stars
FROM movie M1
INNER JOIN rating USING(mID)
WHERE stars = (
 SELECT MAX(stars) 
 FROM movie M2
 INNER JOIN rating USING(mID)
 GROUP BY director
 HAVING M2.director = M1.director
);

SQL Movie-Rating Modification Exercises.sql

--- 1. Add the reviewer Roger Ebert to your database, with an rID of 209.

INSERT INTO reviewer
VALUES(209, "Roger Ebert");

--- 2. 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 mID IN 
  (SELECT mID 
   FROM rating
   GROUP BY mID
   HAVING AVG(stars) >= 4
  )
;


--- 3. 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 mID IN (
  SELECT mID
  FROM movie
  WHERE year<1970 OR year>2000)
  AND stars<4;

SQL Social-Network Modification Exercises.sql

--- 1. It's time for the seniors to graduate. Remove all 12th graders from Highschooler.

DELETE 
FROM highschooler
WHERE grade=12;


--- 2. If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.

DELETE FROM likes
WHERE id2 IN (
  SELECT id2
  FROM friend F
  WHERE F.id1 = likes.id1
) AND id2 NOT IN (
  SELECT L2.id1
  FROM likes L2
  WHERE L2.id2 = likes.id1
);


--- 3. 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.

INSERT INTO Friend
SELECT DISTINCT F1.ID1, F2.ID2
FROM Friend F1, Friend F2
WHERE F1.ID2 = F2.ID1 AND F1.ID1 <> F2.ID2 AND F1.ID1 NOT IN (
  SELECT F3.ID1
  FROM Friend F3
  WHERE F3.ID2 = F2.ID2
);

INSERT INTO Friend
SELECT F1.ID1, F2.ID2
FROM Friend F1
INNER JOIN Friend F2 ON F1.ID2 = F2.ID1
WHERE F1.ID1 <> F2.ID2
EXCEPT
SELECT * FROM Friend;

SQL Social-Network Query Exercises Extras.sql

--- 1. 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.

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 H2.id=L1.id2 AND  H2.id = L2.id1 AND H3.id = L2.id2 AND H3.id <> H1.id;

--- 2. Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades. 

SELECT name, grade
FROM highschooler H1
WHERE grade NOT IN (
  SELECT grade
  FROM highschooler H2, friend
  WHERE friend.id1 = H1.id AND friend.id2=H2.id
);

--- 3. What is the average number of friends per student? (Your result should be just one number.) 

SELECT AVG(count)
FROM ( 
  SELECT COUNT(*) as count
  FROM friend
  GROUP BY id1
);

--- 4. 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. 

-- select id from Cassandra and pick all ids (id2) that are friends with Cassandra
-- then select all ids (id2) that are friends with them and ensuring that it is not Cassandra

SELECT COUNT(id2) FROM friend 
WHERE id1 IN (
  SELECT id2 
  FROM friend 
  WHERE id1 = (SELECT ID FROM Highschooler WHERE name = 'Cassandra') 
    AND id2 <> id1
);

--- 5. Find the name and grade of the student(s) with the greatest number of friends. 

SELECT name, grade
FROM highschooler, friend 
WHERE id=id1
GROUP BY id1
HAVING COUNT(*) = (
  SELECT MAX(count)
  FROM (SELECT COUNT(*) AS count FROM friend GROUP BY id1)
); 

SQL Social-Network Query Exercises.sql

--- 1. Find the names of all students who are friends with someone named Gabriel. 

SELECT H1.name 
FROM highschooler H1
INNER JOIN friend ON H1.id = friend.id1
INNER JOIN highschooler H2 ON friend.id2 = H2.id
WHERE H2.name="Gabriel";


--- 2. 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
INNER JOIN likes ON H1.id = likes.id1
INNER JOIN highschooler H2 on H2.id = likes.id2
WHERE (H1.grade >= H2.grade+2);


--- 3. 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 highschooler H1
INNER JOIN likes ON H1.id = likes.id1
INNER JOIN highschooler H2 ON H2.id = likes.id2
WHERE H1.id IN (
  SELECT id2 
  FROM likes 
  WHERE likes.id1 = H2.id
) AND
H1.name < H2.name
ORDER BY H1.name, H2.name;

--- Alternative

SELECT H1.name, H1.grade, H2.name, H2.grade
FROM highschooler H1, highschooler H2, likes L1, likes L2
WHERE (H1.ID = L1.ID1 AND H2.ID = L1.ID2) AND (H2.ID = L2.ID1 AND H1.ID = L2.ID2) AND H1.name < H2.name
ORDER BY H1.name, H2.name;


--- 4. 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 H
WHERE ID NOT IN (
  SELECT id1 FROM likes
  UNION
  SELECT id2 FROM likes
)
ORDER BY grade, name;



--- 5. 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 name, grade
FROM highschooler H1
WHERE id NOT IN (
  SELECT ID1
  FROM highschooler H2, friend
  WHERE H1.id = friend.id1 AND H2.id = friend.id2 AND H1.grade <> H2.grade
)
ORDER BY grade, name;


--- 6. 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 name, grade
FROM Highschooler H1
WHERE ID NOT IN (
  SELECT ID1
  FROM Friend, Highschooler H2
  WHERE H1.ID = Friend.ID1 AND H2.ID = Friend.ID2 AND H1.grade <> H2.grade
)
ORDER BY grade, name;


--- 7. 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 highschooler H1, highschooler H2, highschooler H3, likes L1, friend F1, friend F2
WHERE H1.id = L1.id1 AND H2.id = L1.id2 AND (
  H1.id NOT IN (SELECT ID1 FROM friend WHERE id2=H2.id)
  )
  AND H1.id = F1.id1 AND H2.id = F2.id1 AND F1.id2 = F2.id2
  AND H3.id = F1.id2
;

--- 8. Find the difference between the number of students in the school and the number of different first names. 

SELECT  COUNT(*)- COUNT(DISTINCT name)
FROM highschooler;


--- 9. Find the name and grade of all students who are liked by more than one other student. 

SELECT name, grade
FROM highschooler H
WHERE id IN (SELECT id2 FROM likes GROUP BY id2 HAVING COUNT()>1);