Introductory SQL Exercises 2

—–
Answers to DB5 EXTRA Query exercises

ANSWERS FOR seq-exercise-sql_movie_query_extra

Q1 Answer:

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:

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

Q3 Answer:

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:

SELECT DISTINCT M.title FROM Reviewer Re, Rating Ra, Movie M
WHERE M.mID = Ra.mID AND Ra.rID = Re.rID AND Re.name <> ‘Chris Jackson’  Wrong — why?

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

SELECT M.title FROM Movie M
WHERE M.mID NOT IN
(SELECT M.mID FROM Movie M, Rating Ra, Reviewer Re
WHERE Re.name = ‘Chris Jackson’ AND Re.rID = Ra.rID AND Ra.mID = M.mID)

Q5 Answer:

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  Wrong — why?

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 */
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  Best answer — why?
Q6 Answer:

left it as a challenge, using construct found in Quiz Q-w2 key for finding minimal stars (similar to finding oldest vehicle)

Q8 Answer without HAVING or COUNT:

Q9 Answer without COUNT:

ANSWERS for seq-exercise-sql_social_query_extra

Q1 Answer:

Q2 Answer: