Movie rating SQL queries - extras

movie_rating_query_extras.sql

-- Q1

SELECT name from Reviewer
WHERE Reviewer.rID IN
(SELECT Rating.rID from Rating
WHERE Rating.mID =
(SELECT mid from Movie
WHERE title = "Gone with the Wind"))

-- Q2

SELECT name,title,stars
FROM Movie
JOIN Rating on Rating.mID = Movie.mID
JOIN Reviewer on Reviewer.rID = Rating.rID
WHERE name = director

-- Q3

SELECT name from Reviewer
UNION ALL
SELECT title from Movie
ORDER BY 1 ASC

-- Q4

SELECT title FROM Movie
WHERE title NOT IN
(SELECT Movie.title FROM Reviewer 
JOIN Rating ON Rating.rID = Reviewer.rID
JOIN Movie ON Movie.mID = Rating.mID
WHERE Reviewer.name = "Chris Jackson")

-- Q5

SELECT x,y FROM
(SELECT DISTINCT V1.name as x, V2.name as y
FROM Rating AS R1, Rating AS r2
JOIN Reviewer as V1 on V1.rID = R1.rID
JOIN Reviewer as V2 on V2.rID = R2.rID
WHERE V1.name <> V2.name
AND R1.mID = R2.mID)
WHERE x < y

-- Q6

SELECT name, title, stars from Rating
JOIN Reviewer on Reviewer.rID = Rating.rID
JOIN Movie on Movie.mID = Rating.mID
WHERE stars = (SELECT min(stars) from Rating)

-- Q7

SELECT title, avg(stars) as avg_stars from Movie
JOIN Rating on Rating.mID = Movie.mID
GROUP BY Movie.mID
ORDER BY avg_stars DESC, title ASC

-- Q8

SELECT n FROM
(SELECT name as n, COUNT(name) as c from Reviewer
JOIN Rating on Rating.rID = Reviewer.rID
GROUP BY name)
WHERE c >= 3

-- Q9

SELECT Movie.title, Movie.director FROM Movie
JOIN (SELECT director d, COUNT(director) c from Movie
GROUP BY director
HAVING director IS NOT null AND c > 1) s on s.d = Movie.director
ORDER BY Movie.director, Movie.title

-- Q10

SELECT title, AVG(stars) from Movie
JOIN Rating on Rating.mID = Movie.mID
GROUP BY title
ORDER BY 2 DESC
LIMIT 1

-- Q11

SELECT title, AVG(stars) avg_rating from Movie
JOIN Rating on Rating.mID = Movie.mID
GROUP BY title
HAVING avg_rating = (SELECT a FROM 
(SELECT title, AVG(stars) a from Movie
JOIN Rating on Rating.mID = Movie.mID
GROUP BY title
ORDER BY 2 ASC
LIMIT 1))

-- Q12

SELECT director, title, max(stars) from Movie
JOIN Rating on Rating.mID = Movie.mID
WHERE director IS NOT null
GROUP BY director