EXP10_CSE2004Lab_20BRS1064

create table movie_20BRS1064(
    mID integer,
    title varchar2(255),
    year_ integer,
    director varchar2(255)
);

create table reviewer_20BRS1064(
    rID integer,
    name varchar2(255)
);

create table rating_20BRS1064(
    rID integer,
    mID integer,
    stars integer,
    ratingDate date
);

insert into movie_20BRS1064 values(1001,'Fight Club',1999,'David Fincher');
insert into movie_20BRS1064 values(1002,'Pursuit of Happiness',2006,'Gabriele Muccino');
insert into movie_20BRS1064 values(1003,'Inception',2010,'Christopher Nolan');
insert into movie_20BRS1064 values(1004,'Intersteller',2014,'Christopher Nolan');
insert into movie_20BRS1064 values(1005,'The Martian',2015,'Ridley Scott');
insert into movie_20BRS1064 values(1006,'Men in BLack',2015,'Ridley Scott');
insert into movie_20BRS1064 values(1007,'Getting Fat in a Healthy Way',2015,'Kevork Aslanyan');
insert into movie_20BRS1064 values(1008,'Zathura: A Space Adventure',2005,'Jon Favreau');
insert into movie_20BRS1064 values(1009,'Your Name',2016,'Makoto Shinkai');
insert into movie_20BRS1064 values(1010,'127 Hours',2010,'Danny Boyle');

insert into reviewer_20BRS1064 values(2001,'Liam');
insert into reviewer_20BRS1064 values(2002,'Noah');
insert into reviewer_20BRS1064 values(2003,'Oliver');
insert into reviewer_20BRS1064 values(2004,'Elijah');
insert into reviewer_20BRS1064 values(2005,'William');
insert into reviewer_20BRS1064 values(2006,'James');
insert into reviewer_20BRS1064 values(2007,'Benjamin');
insert into reviewer_20BRS1064 values(2008,'Lucas');
insert into reviewer_20BRS1064 values(2009,'Henry');
insert into reviewer_20BRS1064 values(2010,'Alexander');

insert into rating_20BRS1064 values(2001,1001,4.5,TO_DATE('2011-06-05', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2002,1002,5,TO_DATE('2012-05-06', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2003,1003,3,TO_DATE('2010-02-07', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2004,1004,3.5,TO_DATE('2010-08-08', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2005,1005,2,TO_DATE('2015-10-09', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2006,1006,4,TO_DATE('2016-01-10', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2007,1007,4,TO_DATE('2015-12-11', 'YYYY-MM-DD'));
insert into rating_20BRS1064 values(2008,1008,4.5,TO_DATE('2013-06-12', 'YYYY-MM-DD'));

--1
desc movie_20BRS1064;
create table movie_ST as
select mID,title,year_,director
from movie_20BRS1064;
select * from movie_ST;

--2
create table rating_ST as
select mID,stars
from rating_20BRS1064;
select * from rating_ST;

--3
create table mov_ST as
select mID,title,year_,director
from movie_20BRS1064
where director = '%M';
select * from mov_ST;

--4
create view rating_view(mID,rID,stars,ratingDate) as
select mID,rID,stars,ratingDate
from rating_20BRS1064
where ratingDate>to_date('2011-01-20','YYYY-MM-DD');

--5
create or replace view highRating(mID,title,stars,ratingDate) as
select m.mID,m.title,r.stars,r.ratingDate
from movie_20BRS1064 m,rating_20BRS1064 r
where r.stars>3 and r.mID=m.mID;

--6
create view noRating(mID,title) as
select m.mID,m.title
from movie_20BRS1064 m,rating_20BRS1064 r
where not(m.mID= r.mID);

--7
select * from rating_view;
select * from highRating;
select * from noRating;

--8
update rating_view set stars=5;
select * from rating_view;
--9
drop view rating_view;
drop view highRating;
drop view noRating;

--10
create synonym mov
for movie_20BRS1064;
select * from mov;

--11
drop synonym mov;