Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Exercises
- --Q1 Find the titles of all movies directed by Steven Spielberg.
- select title from Movie where 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 year from Movie, Rating
- where Movie.mID = Rating.mID and
- stars > 3 order by year;
- --Q3 Find the titles of all movies that have no ratings.
- select distinct title from Movie, Rating
- where Movie.mID not in (select mID from Rating);
- --Q4 Find the names of all reviewers who have ratings with a NULL value for the date.
- select distinct name from Reviewer, Rating
- where Reviewer.rID = Rating.rID and
- Rating.ratingDate is NULL;
- --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.
- select distinct name as 'reviewer name', title as 'movie title', stars, ratingDate from
- Movie, Rating, Reviewer
- where
- Movie.mID = Rating.mID and Rating.rID = Reviewer.rID
- order by name, title, stars;
- --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.
- select name, title from Reviewer, Movie, Rating, Rating r2
- where Rating.mID=Movie.mID and Reviewer.rID=Rating.rID
- and Rating.rID = r2.rID and r2.mID = Movie.mID
- and Rating.stars < r2.stars and Rating.ratingDate < r2.ratingDate;
- --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.
- select title, max(stars) from Movie, Rating
- where Movie.mID = Rating.mID
- group by Rating.mID
- order by title;
- --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.
- select title, max(stars) - min(stars) as ratingSpread from Movie, Rating
- where Movie.mID = Rating.mID
- group by title
- order by ratingspread desc, title;
- --Q9 Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980.
- select smaller1980.second - bigger1980.first
- from (select avg( averages1) as first
- from (select title, avg(stars) as averages1 from Movie, Rating where Movie.mID=Rating.mID and year > 1980 group by title)) as bigger1980,
- (select avg( averages2) as second from (select title, avg(stars) as averages2 from Movie, Rating
- where Movie.mID=Rating.mID and year < 1980 group by title)) as smaller1980;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement