SQL Movie-Rating Query Exercises, PostgreSQL

SQL Movie-Rating Query Exercises

drop table if exists HanW_movie;
drop table if exists HanW_reviewer;
drop table if exists HanW_rating;

create table HanW_movie("mid" int, "title" text, "year" int, "director" text);
create table HanW_reviewer("rid" int, "name" text);
create table HanW_rating("rid" int, "mid" int, "stars" int, "ratingdate" date);

insert into HanW_movie("mid","title","year","director") 
values
(101, 'Gone with the Wind', 1939, 'Victor Fleming'),
(102, 'Star Wars', 1977, 'George Lucas'),
(103, 'The Sound of Music', 1965, 'Robert Wise'),
(104, 'E.T.', 1982, 'Steven Spielberg'),
(105, 'Titanic', 1997, 'James Cameron'),
(106, 'Snow White', 1937, null),
(107, 'Avatar', 2009, 'James Cameron'),
(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

insert into HanW_reviewer("rid","name") 
values
(201, 'Sarah Martinez'),
(202, 'Daniel Lewis'),
(203, 'Brittany Harris'),
(204, 'Mike Anderson'),
(205, 'Chris Jackson'),
(206, 'Elizabeth Thomas'),
(207, 'James Cameron'),
(208, 'Ashley White');

insert into HanW_rating("rid", "mid", "stars", "ratingdate")
values
(201, 101, 2, '2011-01-22'),
(201, 101, 4, '2011-01-27'),
(202, 106, 4, null),
(203, 103, 2, '2011-01-20'),
(203, 108, 4, '2011-01-12'),
(203, 108, 2, '2011-01-30'),
(204, 101, 3, '2011-01-09'),
(205, 103, 3, '2011-01-27'),
(205, 104, 2, '2011-01-22'),
(205, 108, 4, null),
(206, 107, 3, '2011-01-15'),
(206, 106, 5, '2011-01-19'),
(207, 107, 5, '2011-01-20'),
(208, 104, 3, '2011-01-02');

/* 
Table Schema
HanW_movie("mid","title","year","director")
HanW_reviewer("rid","name") 
HanW_rating("rid", "mid", "stars", "ratingdate")
*/

-- Q1: Find the names of all reviewers who rated Gone with the Wind.
SELECT DISTINCT a.name
FROM HanW_reviewer a JOIN HanW_rating b on a.rid = b.rid JOIN HanW_movie c on b.mid = c.mid
WHERE c.title = 'Gone with the Wind'
;
-- Q2: For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.
SELECT a.name, c.title, b.stars
FROM HanW_reviewer a JOIN HanW_rating b on a.rid = b.rid JOIN HanW_movie c on b.mid = c.mid
WHERE a.name = c.director
;
-- Q3: Return all reviewer names and movie names together in a single list, alphabetized.
SELECT name
FROM HanW_reviewer
UNION ALL
SELECT title as name
FROM HanW_movie
ORDER BY name


run \| edit \| history \| help	-1