Quiz (edX StanfordOnline Databases: Relational Databases and SQL)

출처

https://www.edx.org/learn/relational-databases/stanford-university-databases-relational-databases-and-sql

StanfordOnline: Databases: Relational Databases and SQL | edX

 

Quiz

테이블, 데이터 확인: https://courses.edx.org/asset-v1:StanfordOnline+SOE.YDB-SQL0001+2T2020+type@asset+block/moviedata.html

 

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.v

select name, title
from (
select Reviewer.name, Movie.title, 
	RANK() over (PARTITION BY name, title ORDER BY Rating.stars) as rank_stars,
	RANK() over (PARTITION BY name, title ORDER BY Rating.ratingDate) as rank_date
from (Reviewer join Rating on Reviewer.rID = Rating.rID) join Movie on Rating.mID = Movie.mID
)
where rank_stars = 2 and rank_date = 2;

 

 

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 join Rating
on Movie.mID = Rating.mID
group by Movie.mID
order by title;

 

 

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 join Rating
on Movie.mID = Rating.mID
group by Movie.mID
order by rating_spread desc, title;

 

 

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.)

WITH movieAvg as (
	select Movie.mID, Movie.year, Avg(rating.stars) as avg_rating
	from movie join rating on movie.mId = rating.mID
	group by movie.mID
)
select abs(before.avg- after.avg)
from (select avg(avg_rating) as avg from movieAvg 
	  where year < 1980) as before,
	  (select avg(avg_rating) as avg from movieAvg 
	  where year > 1980) as after;

이건 틀린 쿼리인데 어떻게 고쳐야할지 모르겠다.

 

Find the names of all reviewers who rated Gone with the Wind.

select distinct name
from Reviewer join Rating 
on Reviewer.rID = Rating.rID
where Rating.mID = (select mID from Movie where title='Gone with the Wind');

 

 

Return all reviewer names and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The".)

select name as value from Reviewer
UNION
select title as value from Movie
order by value;

 

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

select title
from Movie
where mID not in (select Movie.mID
from (Movie join Rating on Movie.mID=Rating.mID) join Reviewer on Reviewer.rID=Rating.rID
where name='Chris Jackson');

 

Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title. (As an extra challenge, try writing the query both with and without COUNT.)

with filtered as (
	select director
	from Movie
	group by movie.director
	having count(*)>1
)
select title, director
from Movie
where Movie.director in (select director from filtered)
order by director, title;

 

Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

with joined as (
	select title, avg(stars) as avg
	from (Movie join Rating on Movie.mID=Rating.mID) join Reviewer on Reviewer.rID=Rating.rID
	group by Movie.mID
)
select title, avg
from joined J1
where not exists (select * from joined J2 where J1.avg < J2.avg);

select Movie.title, AVG(Rating.stars) as avg
from Movie
join Rating on Movie.mID=Rating.mID
join Reviewer on Rating.rID=Reviewer.rID
group by Movie.mID
order by avg desc
LIMIT 1;

 

Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating. (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the lowest average rating and then choosing the movie(s) with that average rating.)

with movie_avg as (
	select Movie.title, AVG(Rating.stars) as avg
	from (Movie join Rating on Movie.mID=Rating.mID) join Reviewer on Reviewer.rID=Rating.rID
	group by Movie.mID
)
select title, avg
from movie_avg
where avg = (select min(avg) from movie_avg);

 

For each director, return the director's name together with the title(s) 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 NULL.

with joined as (
	select director, title, stars
	from Movie join Rating on Movie.mID=Rating.mID
)
select distinct director, title, stars
from joined J1
where stars = (select max(stars) from joined J2 where J2.director=J1.director)