· 8 years ago · Nov 19, 2017, 08:58 AM
1create database MovieRating;
2use MovieRating;
3
4
5/* Delete the tables if they already exist */
6drop table if exists Movie;
7drop table if exists Reviewer;
8drop table if exists Rating;
9
10/* Create the schema for our tables */
11create table Movie(mID int, title text, year int, director text);
12create table Reviewer(rID int, name text);
13create table Rating(rID int, mID int, stars int, ratingDate date);
14
15/* Populate the tables with our data */
16insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
17insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
18insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
19insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
20insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
21insert into Movie values(106, 'Snow White', 1937, null);
22insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
23insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');
24
25insert into Reviewer values(201, 'Sarah Martinez');
26insert into Reviewer values(202, 'Daniel Lewis');
27insert into Reviewer values(203, 'Brittany Harris');
28insert into Reviewer values(204, 'Mike Anderson');
29insert into Reviewer values(205, 'Chris Jackson');
30insert into Reviewer values(206, 'Elizabeth Thomas');
31insert into Reviewer values(207, 'James Cameron');
32insert into Reviewer values(208, 'Ashley White');
33
34insert into Rating values(201, 101, 2, '2011-01-22');
35insert into Rating values(201, 101, 4, '2011-01-27');
36insert into Rating values(202, 106, 4, null);
37insert into Rating values(203, 103, 2, '2011-01-20');
38insert into Rating values(203, 108, 4, '2011-01-12');
39insert into Rating values(203, 108, 2, '2011-01-30');
40insert into Rating values(204, 101, 3, '2011-01-09');
41insert into Rating values(205, 103, 3, '2011-01-27');
42insert into Rating values(205, 104, 2, '2011-01-22');
43insert into Rating values(205, 108, 4, null);
44insert into Rating values(206, 107, 3, '2011-01-15');
45insert into Rating values(206, 106, 5, '2011-01-19');
46insert into Rating values(207, 107, 5, '2011-01-20');
47insert into Rating values(208, 104, 3, '2011-01-02');
48
49
50-- 1.Find the titles of all movies directed by Steven Spielberg.
51SELECT title AS 'Movies By Spielberg'
52FROM Movie
53WHERE director="Steven Spielberg";
54
55-- 2.Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.
56SELECT DISTINCT(year)
57FROM Movie
58WHERE mID in (SELECT mID
59 FROM rating
60 WHERE stars>3)
61ORDER BY (year);
62
63-- 3.Find the titles of all movies that have no ratings
64SELECT DISTINCT(title) AS 'Titles with no ratings'
65FROM Movie
66WHERE mID in (SELECT mID
67 FROM Movie
68 WHERE mID
69 NOT IN(SELECT mID
70 FROM Rating));
71
72-- 4.Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.
73SELECT DISTINCT(name) AS 'Name of Reviewer'
74FROM Reviewer
75WHERE rID IN (SELECT rID
76 FROM Rating
77 WHERE ratingDate is null);
78
79-- 5.Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars.
80SELECT name AS 'Reviewer Name',
81 title AS 'Movie Title',
82 stars AS 'Movie Stars Rating',
83 ratingDate AS 'Date of Rating'
84FROM Reviewer,Rating,Movie
85WHERE Reviewer.rID=Rating.rID AND Movie.mID=Rating.mID
86ORDER BY name, title, stars;
87
88-- 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.
89SELECT name AS 'Name of Reviewer', title AS 'Movie Title'
90FROM Reviewer, Movie, Rating rt1, Rating rt2
91WHERE (rt1.mID=Movie.mID)
92 AND (Reviewer.rID=rt1.rID)
93 AND (rt1.rID = rt2.rID)
94 AND (rt2.mID = Movie.mID)
95 AND (rt1.stars < rt2.stars)
96 AND (rt1.ratingDate < rt2.ratingDate);
97
98-- 7.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.
99SELECT title,
100MAX(stars)
101FROM Movie, Rating
102WHERE Movie.mID=Rating.mID
103GROUP BY(Rating.mID)
104ORDER BY(title);
105
106-- 8.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.
107SELECT title,
108MAX(stars)-MIN(stars) AS spread
109FROM Movie, Rating
110WHERE (Movie.mID=Rating.mID)
111GROUP BY (title)
112ORDER BY (spread) DESC;
113
114-- 9.Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)
115SELECT
116 Avg(CASE WHEN m.Year < 1980 THEN a.stars ELSE Null End) -
117 Avg(CASE WHEN m.Year >= 1980 THEN a.stars ELSE Null End)
118FROM (SELECT mID,
119 AVG(stars) stars
120 FROM rating
121 GROUP BY mid) a INNER JOIN movie m ON m.mid = a.mid;
122
123-- 10.For all movies that have an average rating of 4 stars or higher, add 25 to the release year. (Update the existing tuples; don't insert new tuples.)
124UPDATE movie
125SET year = (year + 25)
126WHERE movie.mid
127IN(SELECT rating.mid
128 FROM rating
129 GROUP BY mID
130 HAVING avg(Stars) >= 4);