Answers to DB5 EXTRA Query exercises 

ANSWERS FOR seq-exercise-sql_movie_query_extra
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. 

Q1 Answer: Find the names of all reviewers who rated Gone with the Wind. 

SELECT DISTINCT Re.name
FROM Reviewer Re, Rating Ra, Movie M
WHERE Re.rID = Ra.rID AND Ra.mID = M.mID AND M.title = 'Gone with the Wind'

Q2 Answer: 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 Re.name, M.title, Ra.stars
FROM Reviewer Re, Rating Ra, Movie M
WHERE Re.name = M.director AND M.mID = Ra.mID AND Ra.rID = Re.rID

gaming the system: /* SELECT 'James Cameron', 'Avatar', 5 */


Q3 Answer: 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 Re.name AS Label /* don't need to rename attribute */
FROM Reviewer Re
UNION
SELECT M.title AS Label
FROM Movie M
ORDER BY Label /* or title or name */



Q4 Answer: Find the titles of all movies not reviewed by Chris Jackson. 

SELECT M.title FROM Movie M
EXCEPT 
SELECT M.title FROM Movie M, Rating Ra, Reviewer Re
WHERE Re.name = 'Chris Jackson' AND Re.rID = Ra.rID AND Ra.mID = M.mID



Q5 Answer: 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. 

/* wrong SELECT DISTINCT Re1.name, Re2.name 
FROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2
WHERE Re1.rID < Re2.rID AND 
      Re1.rID = Ra1.rID AND 
      Re2.rID = Ra2.rID AND 
      Ra1.mID = Ra2.mID
*/

SELECT DISTINCT Re1.name, Re2.name  
FROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2
WHERE 
      Re1.rID = Ra1.rID AND 
      Re2.rID = Ra2.rID AND 
      Ra1.mID = Ra2.mID AND
      Re1.name < Re2.name
      
/* consider possibility of same-named reviewers  — this is most right/complete */

SELECT DISTINCT Re1.name, Re2.name  
FROM Reviewer Re1, Reviewer Re2, Rating Ra1, Rating Ra2
WHERE Re1.rID <> Re2.rID AND 
      Re1.rID = Ra1.rID AND 
      Re2.rID = Ra2.rID AND 
      Ra1.mID = Ra2.mID AND
      Re1.name <= Re2.name
   

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

/* Entire query is going to involve Reviewer (name), 
   Movie (title), and Rating (stars) */
/* Simpler subproblem is to find the minimum number of stars */

SELECT Re.name, M.title, Ra.stars
FROM Reviewer Re, Movie M, Rating Ra
WHERE Re.rID = Ra.rID AND M.mID = Ra.mID AND 
     Ra.stars = (SELECT Ra.stars FROM Rating Ra
                 EXCEPT
                 SELECT Ra1.stars
                 FROM Rating Ra1, Rating Ra2
                 WHERE Ra1.stars > Ra2.stars)

/* NOT IN and EXCEPT can be used in similar circumstances */

SELECT Re.name, M.title, Ra.stars
FROM Reviewer Re, Movie M, Rating Ra
WHERE Re.rID = Ra.rID AND M.mID = Ra.mID AND 
      Ra.stars NOT IN (SELECT Ra1.stars
                              FROM Rating Ra1, Rating Ra2
                              WHERE Ra1.stars > Ra2.stars)  



Q7 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 M.title, AVG(Ra.stars) AVE
FROM Movie M, Rating Ra
WHERE M.mID = Ra.mID 
GROUP BY Ra.mID, M.title    /* group by mid because it defines the group, and technically */
ORDER BY AVE DESC, M.title   /* should include title because it appears alone in SELECT */

/* order of evaluation: FROM, WHERE, GROUP BY, SELECT, ORDER BY */



Q8 Answer without HAVING or COUNT: 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.) 

SELECT Re.name
FROM Reviewer Re, Rating Ra
WHERE Re.rID = Ra.rID
GROUP BY Re.rID, Re.name
HAVING COUNT(*) >= 3


SELECT DISTINCT Re.name
FROM Reviewer Re, Rating Ra1, Rating Ra2, Rating Ra3
WHERE Re.rID = Ra1.rID AND Re.rID = Ra2.rID AND Re.rID = Ra3.rID 
        AND (Ra1.mID <> Ra2.mID OR Ra1.stars <> Ra2.stars 
                 OR Ra1.ratingDate <> Ra2.ratingDate)
        AND (Ra1.mID <> Ra3.mID OR Ra1.stars <> Ra3.stars 
                 OR Ra1.ratingDate <> Ra3.ratingDate)
        AND (Ra3.mID <> Ra2.mID OR Ra3.stars <> Ra2.stars 
                 OR Ra3.ratingDate <> Ra2.ratingDate)



Q9 Answer without COUNT: 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. (As an extra challenge, try writing the query both with and without COUNT.) 

SELECT M.title, M.director
FROM Movie M
WHERE M.director IN 
      (SELECT M1.director 
       FROM Movie M1, Movie M2 
       WHERE M1.director = M2.director AND M1.mID <> M2.mID)
ORDER BY M.director, M.title

/* SELECT M.title, M.director
FROM Movie M
WHERE M.director IN
    (SELECT M.director
     FROM Movie M
     GROUP BY M.director
     HAVING COUNT(*) > 1)
ORDER BY M.director, M.title */

SELECT M.title, M.director
FROM Movie M
EXCEPT 
SELECT M.title, M.director
FROM Movie M
GROUP BY M.director
HAVING COUNT(*) <= 1
ORDER BY M.director, M.title 

/* incorrect */
/* SELECT M1.title, M1.director
FROM Movie M1, Movie M2
WHERE M1.director = M2.director AND M1.mID <> M2.mID
GROUP BY M1.director
HAVING COUNT(*) > 1 */


Q10 Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.) 

/* SELECT Temp.title, Temp.aveRatings
FROM 
   (SELECT M.mID, M.title, AVG(Ra.stars) AS aveRatings
   FROM Movie M, Rating Ra
   WHERE M.mID = Ra.mID
   GROUP BY M.mID, M.title) AS Temp
WHERE Temp.aveRatings = 4.5  */ /* a hack */

SELECT Temp.title, Temp.aveRatings
FROM 
   (SELECT M.mID, M.title, AVG(Ra.stars) AS aveRatings
   FROM Movie M, Rating Ra
   WHERE M.mID = Ra.mID
   GROUP BY M.mID, M.title) AS Temp
WHERE Temp.aveRatings = (SELECT MAX(Temp.aveRatings)
                         FROM Temp)  /* problem with scope */


SELECT Temp.title, Temp.aveRatings
FROM 
   (SELECT M.mID, M.title, AVG(Ra.stars) AS aveRatings
   FROM Movie M, Rating Ra
   WHERE M.mID = Ra.mID
GROUP BY M.mID) AS Temp
WHERE Temp.aveRatings = (SELECT MIN(Temp.aveRatings)  
                         FROM (SELECT AVG(Ra.stars) AS aveRatings
                               FROM Rating Ra
                               GROUP BY Ra.mID) AS Temp
                        )

SELECT M.title, AVG(Ra.stars) AS aveRatings
FROM Movie M, Rating Ra
WHERE M.mID = Ra.mID
GROUP BY M.mID
HAVING AVG(Ra.stars) = (SELECT MIN(Temp.aveRatings)
                        FROM (SELECT AVG(Ra.stars) AS aveRatings
                              FROM Rating Ra
                              GROUP BY Ra.mID) AS Temp
                           )

Q11 Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating. (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the lowest average rating and then choosing the movie(s) with that average rating.) 

/* same as problem 10, but MAX to MIN */
SELECT Temp.title, Temp.aveRatings
FROM 
   (SELECT M.mID, M.title, AVG(Ra.stars) AS aveRatings
   FROM Movie M, Rating Ra
   WHERE M.mID = Ra.mID
GROUP BY M.mID) AS Temp
WHERE Temp.aveRatings = (SELECT MIN(Temp.aveRatings)  
                         FROM (SELECT AVG(Ra.stars) AS aveRatings
                               FROM Rating Ra
                               GROUP BY Ra.mID) AS Temp
                        )

SELECT M.title, AVG(Ra.stars) AS aveRatings
FROM Movie M, Rating Ra
WHERE M.mID = Ra.mID
GROUP BY M.mID
HAVING AVG(Ra.stars) = (SELECT MIN(Temp.aveRatings)
                        FROM (SELECT AVG(Ra.stars) AS aveRatings
                              FROM Rating Ra
                              GROUP BY Ra.mID) AS Temp
                           )


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

/* This gives correct answer for sample DB, but its not right*/

SELECT M.director, M.title, MAX(stars)
FROM Movie M, Rating Ra
WHERE M.director IS NOT NULL AND M.mID = Ra.mID
GROUP BY M.director */ /* there can be multiple titles in each group */

/* Gives each Director  MAX stars -- a subproblem*/

/* SELECT M.director, MAX(stars)
FROM Movie M, Rating Ra
WHERE M.director IS NOT NULL AND M.mID = Ra.mID
GROUP BY M.director */


SELECT DISTINCT M.director, M.title, Ra.stars 
FROM Movie M, Rating Ra,
     (SELECT M.director, MAX(Ra.stars) AS stars
      FROM Movie M, Rating Ra
      WHERE M.director IS NOT NULL AND M.mID = Ra.mID
      GROUP BY M.director) As DirMax
WHERE M.director = DirMax.director AND M.mID = Ra.mID AND Ra.stars = DirMax.stars




ANSWERS for seq-exercise-sql_social_query_extra
Highschooler ( ID, name, grade )
English: There is a high school student with unique ID and a given first name in a certain grade.

Friend ( ID1, ID2 )
English: The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).

Likes ( ID1, ID2 )
English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present. 

Q1 Answer: 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 A.ID1, B.ID1
FROM Likes A, Likes B
WHERE A.ID2 = B.ID1 AND A.ID1 <> B.ID2
*/


SELECT HA.name, HA.grade, HB.name, HB.grade, HC.name, HC.grade
FROM Highschooler HA, Highschooler HB, Highschooler HC,
     (SELECT A.ID1 AS IDA, B.ID1 AS IDB, B.ID2 AS IDC
     FROM Likes A, Likes B
     WHERE A.ID2 = B.ID1 AND B.ID2 <> A.ID1) AS Temp
WHERE HA.ID = Temp.IDA AND HB.ID = Temp.IDB AND HC.ID = Temp.IDC


SELECT HA.name, HA.grade, HB.name, HB.grade, HC.name, HC.grade
FROM Likes A, Likes B, Highschooler HA, Highschooler HB, Highschooler HC
WHERE A.ID2 = B.ID1 AND HA.ID = A.ID1 AND HB.ID = B.ID1 AND HC.ID = B.ID2
      AND B.ID2 <> A.ID1


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


SELECT H.name, H.grade
FROM Highschooler H 
WHERE H.ID NOT IN (SELECT F.ID1 
                   FROM Friend F, Highschooler H1, Highschooler H2 
                   WHERE F.ID1 = H1.ID AND F.ID2 = H2.ID AND H1.grade = H2.grade)


/* correlated query */
SELECT H.name, H.grade
FROM Highschooler H 
WHERE H.ID NOT IN (SELECT F.ID1 
                   FROM Friend F, Highschooler H2 
                   WHERE F.ID1 = H.ID AND F.ID2 = H2.ID AND H.grade = H2.grade) 


SELECT H.name, H.grade
FROM Highschooler H 
EXCEPT
SELECT H.name, H.grade 
FROM Friend F, Highschooler H, Highschooler H2 
WHERE F.ID1 = H.ID AND F.ID2 = H2.ID AND H.grade = H2.grade


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

/* incorrect
SELECT AVG(Temp.Cnt)
FROM
(SELECT COUNT(*) AS Cnt
FROM Highschooler H, Friend F
WHERE H.ID = F.ID1   
GROUP BY F.ID1) AS Temp */

SELECT AVG(Temp.Cnt)
FROM
(SELECT COUNT(*) AS Cnt
FROM Friend F   /* we know that friend relation is commutative */
GROUP BY F.ID1) AS Temp


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. 

SELECT COUNT(*)
FROM
(SELECT DISTINCT F.ID2
FROM Friend F, Highschooler H
WHERE H.name = 'Cassandra' AND H.ID = F.ID1
UNION
SELECT DISTINCT F2.ID2
FROM Highschooler H, Friend F1, Friend F2
WHERE H.name = 'Cassandra' AND H.ID = F1.ID1 AND F1.ID2 = F2.ID1 AND F2.ID2 <> H.ID)



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

/* Subproblem
 SELECT F.ID1 AS ID, COUNT(F.ID2) AS Cnt
 FROM Friend F
 GROUP BY F.ID1
*/ 

SELECT H.name, H.grade
FROM Highschooler H, 
(SELECT F.ID1 AS ID, COUNT(F.ID2) AS Cnt
 FROM Friend F
 GROUP BY F.ID1) AS Temp
WHERE H.ID = Temp.ID 
GROUP BY H.name, H.grade
HAVING Temp.Cnt >= (SELECT MAX(Cnt) FROM (SELECT COUNT(F.ID2) AS Cnt 
                                          FROM Friend F GROUP BY F.ID1))