MovieRating SQL, PostgreSQL

MovieRating SQL

drop table if exists yuhaoz_highschooler;
drop table if exists yuhaoz_friend;
drop table if exists yuhaoz_likes;

create table yuhaoz_highschooler("id" int, "name" varchar(30), "grade" int);
create table yuhaoz_friend("id1" int, "id2" int);
create table yuhaoz_likes("id1" int, "id2" int);

insert into yuhaoz_highschooler("id","name","grade") 
values 
(1510, 'Jordan', 9),
(1689, 'Gabriel', 9),
(1381, 'Tiffany', 9),
(1709, 'Cassandra', 9),
(1101, 'Haley', 10),
(1782, 'Andrew', 10),
(1468, 'Kris', 10),
(1641, 'Brittany', 10),
(1247, 'Alexis', 11),
(1316, 'Austin', 11),
(1911, 'Gabriel', 11),
(1501, 'Jessica', 11),
(1304, 'Jordan', 12),
(1025, 'John', 12),
(1934, 'Kyle', 12),
(1661, 'Logan', 12);

insert into yuhaoz_friend("id1","id2") 
values 
(1510, 1381),
(1101, 1501),
(1510, 1689),
(1689, 1709),
(1381, 1247),
(1709, 1247),
(1689, 1782),
(1782, 1468),
(1782, 1316),
(1782, 1304),
(1468, 1101),
(1468, 1641),
(1101, 1641),
(1247, 1911),
(1247, 1501),
(1911, 1501),
(1501, 1934),
(1316, 1934),
(1934, 1304),
(1304, 1661),
(1661, 1025),
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101);

insert into yuhaoz_likes("id1","id2")
values 
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101),
(1101, 1381),
(1025, 1641);

drop table if exists yuhaoz_movie;
drop table if exists yuhaoz_reviewer;
drop table if exists yuhaoz_rating;

create table yuhaoz_movie("mid" int, "title" text, "year" int, "director" text);
create table yuhaoz_review("rid" int, "name" text);
create table yuhaoz_rating("rid" int, "mid" int, "stars" int, "ratingdate" date);

insert into yuhaoz_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 yuhaoz_review("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 yuhaoz_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');

select distinct title, director
from yuhaoz_movie
where director = 'Steven Spielberg';

select distinct ratingdate
from yuhaoz_movie a join yuhaoz_rating b on a.mid = b.mid
where stars>3 and b.ratingdate is not null
order by 1


run \| edit \| history \| help	0