Last active
January 23, 2020 05:58
-
-
Save fadziljusri/c34b5b313558001b599857f83f124ef5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- KENA BELANJA TONY ROMA'S NI HMMMMH 🤔 (AKU DAH SACRIFICE ALMOST HALF HOUR UTK KAU) | |
-- 1. Find the titles of all movies directed by Steven Spielberg. | |
SELECT title | |
FROM Movie | |
WHERE director = 'Steven Spielberg'; | |
-- 2. Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order | |
SELECT DISTINCT year | |
FROM Movie | |
INNER JOIN Rating ON Movie.mID = Rating.mID | |
WHERE stars IN (4, 5) | |
ORDER BY year; | |
-- 3. Find the titles of all movies that have no ratings | |
SELECT title | |
FROM Movie | |
WHERE mID NOT IN (SELECT mID FROM Rating); | |
-- 4. For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title. | |
SELECT title, MAX(stars) | |
FROM Movie | |
INNER JOIN Rating USING(mID) | |
GROUP BY mID | |
ORDER BY title; | |
-- 5. For each movie that has at least one rating, find the movie title and total number of stars, the highest star and the person who gave highest star. | |
SELECT title, | |
SUM(stars) AS total_stars, | |
MAX(stars) AS highest_stars, | |
name AS highest_stars_reviewer | |
FROM Movie | |
INNER JOIN Rating USING(mID) | |
INNER JOIN Reviewer USING(rID) | |
WHERE Movie.mID = Rating.mID | |
AND Rating.rID = Reviewer.rID | |
GROUP BY mID; | |
-- 6. For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie. | |
SELECT name, title | |
FROM Movie | |
INNER JOIN Rating R1 USING(mID) | |
INNER JOIN Rating R2 USING(rID, mID) | |
INNER JOIN Reviewer USING(rID) | |
WHERE R1.ratingDate < R2.ratingDate AND R1.stars < R2.stars; | |
-- 7. For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title. | |
SELECT title, (MAX(stars) - MIN(stars)) AS rating_spread | |
FROM Movie | |
INNER JOIN Rating USING(mID) | |
GROUP BY mID | |
ORDER BY rating_spread DESC, title; | |
-- 8. Find the names of reviewers for every director (one row per director with all reviewers) | |
SUSAHHHHHHH!!!!!!!!!! OK AIR TAK PAYAH BELANJA! TONY ROMA'S!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment