--Step 1: Delete the table in case it has one before!

DROP TABLE movies;
DROP TABLE friends;
DROP TABLE review;

--Step 2:Create table

CREATE TABLE movies
(
    movie_id    serial primary key,
    movie_name        VARCHAR(100) not null
);

CREATE TABLE friends
(
    friends_id    serial primary key,
    friends_name        VARCHAR(100) not null
);

CREATE TABLE review
(
    movie_id int not null,
	friends_id int not null,
	ranking int
);

--Step 3: Insert data in 2 different ways

--way 1
insert into movies(movie_name) VALUES ('SpiderMan');
insert into movies(movie_name) VALUES ('Sing2');
insert into movies(movie_name) VALUES ('Uncharted');
insert into movies(movie_name) VALUES ('Moonfall');
insert into movies(movie_name) VALUES ('Blacklight');
insert into movies(movie_name) VALUES ('Marry Me')

insert into friends(friends_name) VALUES ('Victor');
insert into friends(friends_name) VALUES ('Pak');
insert into friends(friends_name) VALUES ('Daisy');
insert into friends(friends_name) VALUES ('Mike');
insert into friends(friends_name) VALUES ('Greg')

--way2
COPY review (movie_id,friends_id,ranking)
from '/Users/jaylee/607wk2hw/moviedata.csv'
delimiter ','
csv header;

--final step: join the result together for R use
CREATE TABLE movieresult as
SELECT review.*,movies.movie_name,friends.friends_name
FROM review
	left join movies on movies.movie_id = review.movie_id
	left join friends on friends.friends_id = review.friends_id