SQL3, PostgreSQL - rextester

SQL3

--PostgreSQL 9.6
--'\\' is a delimiter

drop table if exists yhao_highschooler;
drop table if exists yhao_friend;
drop table if exists yhao_likes;

create table yhao_highschooler("id" int, "name" varchar(30), "grade" int);
create table yhao_friend("id1" int, "id2" int);
create table yhao_likes("id1" int, "id2" int);

insert into yhao_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 yhao_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 yhao_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 yhao_movie;
drop table if exists yhao_reviewer;
drop table if exists yhao_rating1;

create table yhao_movie("mid" int, "title" text, "year" int, "director" text);
create table yhao_reviewer("rid" int, "name" text);
create table yhao_rating1("rid" int, "mid" int, "stars" int, "ratingdate" date);

insert into yhao_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 yhao_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 yhao_rating1("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 * FROM yhao_highschooler;
SELECT * FROM yhao_friend;
SELECT * FROM yhao_likes;
SELECT * FROM yhao_movie;
SELECT * FROM yhao_reviewer;
SELECT * FROM yhao_rating1;


run \| edit \| history \| help	0