Find the Names of All Reviewers Who Rated Gone With the Wind

Solutions to Stanford Movie, Reviewer and Rating Database problems

(Tables Info) — — — — — — — — — — — — — — — — — — — — — — — — — Table: Movie ( mID, title, year, managing director )

English language: There is a movie with ID number mID, a championship, a release year, and a director.

Table: Reviewer ( rID, proper noun )

English: The reviewer with ID number rID has a certain name.

Table: 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: Find the titles of all movies directed by Steven Spielberg.

SELECT m.title
FROM movie thousand
WHERE m.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 m.year
FROM picture thou
JOIN rating r
ON r.mID=thousand.mID
WHERE r.stars>=4
Gild Past m.yr;

Q3: Detect the titles of all movies that have no ratings.

SELECT m.championship
FROM movie one thousand
WHERE m.mID non in (SELECT r.mID FROM rating r);

Q4: Some reviewers didn't provide a date with their rating. Notice the names of all reviewers who have ratings with a NULL value for the date.

SELECT re.proper noun
FROM reviewer re
WHERE re.rID in (SELECT r.rID FROM rating r WHERE r.ratingDate is Zippo);

Q5. Write a query to return the ratings data in a more readable format: reviewer proper noun, movie championship, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie championship, and lastly past number of stars.

SELECT re.name, thou.championship, r.stars, r.ratingDate
FROM rating r
LEFT JOIN reviewer re ON r.rID = re.rID
LEFT Join movie m ON r.mID = m.mID
ORDER Past re.name, yard.title, r.stars;

Q6. For all cases where the same reviewer rated the same movie twice and gave it a college rating the second time, return the reviewer's name and the title of the moving picture.

SELECT re.proper name, m.title
FROM (SELECT r1.rID as rID, r1.mID every bit mID
FROM rating r1, rating r2
WHERE (r1.rID=r2.rID) AND (r1.mID=r2.mID) AND (r1.ratingDate<r2.ratingDate) AND (r1.stars<r2.stars)) AS c
LEFT JOIN reviewer re ON c.rID=re.rID
LEFT JOIN flick m ON c.mID=g.mID;

Q7: For each movie that has at least one rating, find the highest number of stars that the motion picture received. Render the movie championship and number of stars. Sort by movie championship.

SELECT thou.title, MAX(r.stars) Every bit max_rating
FROM rating r
LEFT Bring together movie m ON m.mID=r.mID
Grouping Past m.championship;

Q8. For each movie, return the title and the 'rating spread', that is, the difference between highest and everyman ratings given to that movie. Sort by rating spread from highest to lowest, then by motion picture title.

SELECT m.championship, (MAX(r.stars)-MIN(r.stars)) Equally rating_spread
FROM rating r
LEFT Join motion-picture show m ON r.mID=m.mID
GROUP BY m.title
Guild BY rating_spread DESC;

Q9: Find the difference betwixt the average rating of movies released before 1980 and the boilerplate rating of movies released after 1980. (Make certain to calculate the boilerplate rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't only calculate the overall average rating before and after 1980.)

SELECT (t1.avg_b1980 — t2.avg_a1980)
FROM (SELECT AVG(r1.avg_movie) equally avg_b1980
FROM (SELECT r.mID, AVG(r.stars) Every bit avg_movie
FROM rating r GROUP Past r.mID) equally r1
LEFT Bring together movie chiliad ON r1.mID=m.mID WHERE m.year<1980) as t1,
(SELECT AVG(r1.avg_movie) as avg_a1980
FROM (SELECT r.mID, AVG(r.stars) AS avg_movie
FROM rating r GROUP Past r.mID) as r1
LEFT Bring together movie m ON r1.mID=g.mID WHERE one thousand.year>1980) as t2;

Q10. Find the names of all reviewers who rated Gone with the Current of air.

SELECT Distinct re.name
FROM rating r
LEFT JOIN reviewer re ON r.rID=re.rID
LEFT JOIN film m ON r.mID=m.mID
WHERE m.title = 'Gone with the Air current';

Q11. For whatsoever rating where the reviewer is the aforementioned as the manager of the moving-picture show, return the reviewer name, movie title, and number of stars.

SELECT re.proper name, m.title, r.stars
FROM rating r
LEFT JOIN reviewer re ON r.rID=re.rID
LEFT JOIN motion-picture show m ON r.mID=thousand.mID
WHERE re.name=m.director;

Q12. Render all reviewer names and flick names together in a unmarried list, alphabetized. (Sorting by the first proper noun of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The".)

SELECT t.list_
FROM (SELECT Distinct re.name every bit list_
FROM reviewer re
Union ALL
SELECT Singled-out k.title equally list_
FROM movie k) every bit t
Club By t.list_;

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

SELECT Distinct m.championship
FROM movie m
WHERE m.mID not in (SELECT mID FROM rating WHERE rID = (SELECT rID FROM reviewer WHERE name='Chris Jackson'));

Q14. 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 gild.

SELECT DISTINCT t1.name, t2.name
FROM (SELECT r1.*,re1.name
FROM rating r1
LEFT Bring together reviewer re1 ON r1.rID=re1.rID
ORDER BY re1.name) t1,
(SELECT r1.*,re1.name
FROM rating r1
LEFT Join reviewer re1 ON r1.rID=re1.rID
Society BY re1.proper noun) t2
WHERE (t1.rID!=t2.rID) AND (t1.mID=t2.mID) and t1.name<t2.name;

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

SELECT re.proper name,grand.title,r.stars
FROM rating r
LEFT Bring together reviewer re ON r.rID=re.rID
LEFT JOIN movie 1000 ON r.mID=m.mID
WHERE r.stars in (SELECT MIN(stars) FROM rating);

Q16. List movie titles and average ratings, from highest-rated to everyman-rated. If 2 or more than movies have the same average rating, list them in alphabetical order.

SELECT grand.title, t1.avg_movie
FROM (SELECT mID, AVG(stars) as avg_movie
FROM rating
GROUP By mID) t1
LEFT Bring together movie chiliad ON t1.mID=m.mID
Society BY t1.avg_movie DESC,k.championship;

Q17. Find the names of all reviewers who have contributed three or more than ratings.

SELECT re.proper name
FROM reviewer re
WHERE re.rID in (SELECT rID FROM rating Group BY rID HAVING count(rID)>2);

Q18. Some directors directed more than than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director proper noun, and then movie title.

SELECT m.championship, m.director
FROM picture m
WHERE m.managing director in (SELECT managing director FROM movie GROUP Past managing director HAVING COUNT(title)>1)
Social club Past g.director,m.title;

Q19. Find the movie(south) with the highest average rating. Render the movie title(s) and average rating.

SELECT m.title, t1.avg_movie
FROM (SELECT mID, AVG(stars) Equally avg_movie
FROM rating
GROUP BY mID) t1
LEFT JOIN movie m ON t1.mID=m.mID
WHERE t1.avg_movie = (SELECT AVG(stars) As avg_movie
FROM rating
Group Past mID
Social club BY avg_movie DESC
LIMIT 1);

Q20. Detect the picture show(s) with the lowest boilerplate rating. Return the picture title(south) and boilerplate rating.

SELECT m.championship, t1.avg_movie
FROM (SELECT mID, AVG(stars) AS avg_movie
FROM rating
GROUP Past mID) t1
LEFT JOIN movie m ON t1.mID=m.mID
WHERE t1.avg_movie = (SELECT AVG(stars) As avg_movie
FROM rating
Grouping BY mID
Gild By avg_movie
LIMIT i);

Q21. For each director, return the director's proper noun together with the title(southward) 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 Nothing.

SELECT m1.director,m1.title,t2.max_stars
FROM flick m1
LEFT JOIN (SELECT mID, MAX(stars) as max_stars FROM rating GROUP BY mID) t2 ON m1.mID=t2.mID
WHERE m1.mID in (SELECT m.mID
FROM motion picture thousand
LEFT JOIN (SELECT mID, MAX(stars) equally max_stars FROM rating Group BY mID) t1 ON m.mID=t1.mID
Grouping Past m.managing director
HAVING MAX(max_stars)) AND m1.director is not NULL
ORDER BY m1.managing director;

Whatsoever doubts or feedback, please feel gratis to reach out to me at rkg18@knuckles.edu or rohitkmgupta@gmail.com.

stoutcumigho.blogspot.com

Source: https://medium.com/@rohitgupta_82488/solutions-to-stanford-movie-reviewer-and-rating-database-problems-e685ffd57edd

Share this post