· 9 years ago · Dec 03, 2016, 05:52 PM
1/* Delete the tables if they already exist */
2drop table if exists Movie;
3drop table if exists Reviewer;
4drop table if exists Rating;
5
6/* Create the schema for our tables */
7create table Movie(mID int, title text, year int, director text);
8create table Reviewer(rID int, name text);
9create table Rating(rID int, mID int, stars int, ratingDate date);
10
11/* Populate the tables with our data */
12insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
13insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
14insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
15insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
16insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
17insert into Movie values(106, 'Snow White', 1937, null);
18insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
19insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');
20
21insert into Reviewer values(201, 'Sarah Martinez');
22insert into Reviewer values(202, 'Daniel Lewis');
23insert into Reviewer values(203, 'Brittany Harris');
24insert into Reviewer values(204, 'Mike Anderson');
25insert into Reviewer values(205, 'Chris Jackson');
26insert into Reviewer values(206, 'Elizabeth Thomas');
27insert into Reviewer values(207, 'James Cameron');
28insert into Reviewer values(208, 'Ashley White');
29
30insert into Rating values(201, 101, 2, '2011-01-22');
31insert into Rating values(201, 101, 4, '2011-01-27');
32insert into Rating values(202, 106, 4, null);
33insert into Rating values(203, 103, 2, '2011-01-20');
34insert into Rating values(203, 108, 4, '2011-01-12');
35insert into Rating values(203, 108, 2, '2011-01-30');
36insert into Rating values(204, 101, 3, '2011-01-09');
37insert into Rating values(205, 103, 3, '2011-01-27');
38insert into Rating values(205, 104, 2, '2011-01-22');
39insert into Rating values(205, 108, 4, null);
40insert into Rating values(206, 107, 3, '2011-01-15');
41insert into Rating values(206, 106, 5, '2011-01-19');
42insert into Rating values(207, 107, 5, '2011-01-20');
43insert into Rating values(208, 104, 3, '2011-01-02');
44
45.mode column
46.headers ON
47
48
49/*---------- 1. SQL Movie-Rating Query Exercises ----------*/
50-- Q1 Find the titles of all movies directed by Steven Spielberg.
51select title from Movie where director="Steven Spielberg";
52
53-- Q2 Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.
54select distinct year from Movie
55where mId in (select mID from rating where stars>3) order by year;
56
57-- Q3 Find the titles of all movies that have no ratings.
58select distinct title from Movie
59where mID in (select mID from Movie where mID not in (select mID from Rating));
60
61-- Q4 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.
62select distinct name from Reviewer
63where rID in (select rID from Rating where ratingDate is null);
64
65-- Q5 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.
66select name,title,stars,ratingDate
67from Reviewer,Rating,Movie
68where Reviewer.rID=Rating.rID and Movie.mID=Rating.mID
69order by name, title, stars;
70
71-- Q6 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.
72select name, title from Reviewer, Movie, Rating, Rating r2
73where Rating.mID=Movie.mID and Reviewer.rID=Rating.rID
74 and Rating.rID = r2.rID and r2.mID = Movie.mID
75 and Rating.stars < r2.stars and Rating.ratingDate < r2.ratingDate;
76-- GROUP BY name, title HAVING count(*) = 1;
77
78-- Q7 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.
79select title,max(stars) from Movie, Rating
80where Movie.mID=Rating.mID
81group by Rating.mID order by title;
82
83-- Q8 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.
84select title,max(stars)-min(stars) as spread from Movie, Rating
85where Movie.mID=Rating.mID group by title order by spread desc;
86
87-- Q9 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.)
88select down.av1980down - up.av1980up
89from (select avg(avgst) as av1980up from
90 (select title, avg(stars) avgst
91 from Movie,Rating
92 where year>1980 and Movie.mID=Rating.mID group by title
93 )
94 ) as up,
95 (select avg(avgst) as av1980down from
96 (select title, avg(stars) avgst
97 from Movie,Rating
98 where year<1980 and Movie.mID=Rating.mID group by title
99 )
100 ) as down;
101
102
103/*---------- 2. SQL Movie-Rating Modification Exercises ----------*/
104-- Q1 Add the reviewer Roger Ebert to your database, with an rID of 209.
105insert into Reviewer values(209, 'Roger Ebert');
106
107-- Q2 Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL.
108insert into Rating
109select rID,mID,5,null from Reviewer, Movie
110where name="James Cameron";
111
112-- Q3 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.)
113update Movie set year=year+25 where
114mID in (select mID from (select Rating.mID,avg(stars) as average
115 from Movie, Rating where Movie.mID=Rating.mID
116 group by Rating.mID, year) where average>=4);