/**************************************************************
  SIMPLE ONE-TABLE VIEW
  IDs and locations of CS acceptances
**************************************************************/

create view CSA as
select ID, location
from Apply
where major = 'CS' and decision = 'Y';

/**************************************************************
  USE VIEW IN QUERY
  ID, name, GPA of students accepted to CS with GPA < 3.8
**************************************************************/

select Student.ID, name, GPA
from Student, CSA
where Student.ID = CSA.ID and GPA < 3.8;

/**************************************************************
  TEMPORARY TABLE IMPLEMENTATION
**************************************************************/

create temporary table T as
select ID, location
from Apply
where major = 'CS' and decision = 'Y';

select Student.ID, name, GPA
from Student, T
where Student.ID = T.ID and GPA < 3.8;

drop table T;

/**************************************************************
  AUTOMATIC QUERY REWRITE TO ELIMINATE VIEW
  (straightforward version)
**************************************************************/

select Student.ID, name, GPA
from Student, 
     (select ID, location from Apply
      where major = 'CS' and decision = 'Y') as CSA
where Student.ID = CSA.ID and GPA < 3.8;

/**************************************************************
  AUTOMATIC QUERY REWRITE TO ELIMINATE VIEW
  ("flattened" version)
**************************************************************/

select Student.ID, name, GPA
from Student, Apply
where major = 'CS' and decision = 'Y'
and Student.ID = Apply.ID and GPA < 3.8;

/**************************************************************
  VIEW LAYERING
  ID, name, GPA of students accepted to CS with GPA < 3.8
**************************************************************/

create view CSG as
select Student.ID, name, GPA
from Student, CSA
where Student.ID = CSA.ID and GPA < 3.8;

/**************************************************************
  DROP ORIGINAL VIEW
**************************************************************/

drop view CSA;
select * from CSG;

/**************************************************************
  MAKE ENTIRE JOIN INTO VIEW
  "Universal relation"
**************************************************************/

create view Mega as
select Student.ID, name, GPA, sizeHS,
       Campus.location, enrollment, rank, major, decision
from Student, Campus, Apply
where Student.ID = Apply.ID
and Campus.location = Apply.location;

/**************************************************************
  NO MORE JOINS IN QUERIES!
  High-GPA applications to CS major at low-ranked schools
**************************************************************/

select ID, name, GPA, location
from Mega
where GPA > 3.5 and major = 'CS' and rank > 2;

/**************************************************************
  AUTOMATIC QUERY REWRITE TO ELIMINATE VIEW
  ("flattened" version)
**************************************************************/

select Student.ID, name, GPA, Campus.location
from Student, Campus, Apply
where Student.ID = Apply.ID
and Campus.location = Apply.location
and GPA > 3.5 and major = 'CS' and rank > 2;

/**************************************************************
  AUTOMATIC QUERY REWRITE TO ELIMINATE VIEW
  (straightforward version)
**************************************************************/

select ID, name, GPA, location
from (select Student.ID, name, GPA, sizeHS,
        Campus.location, enrollment, rank, major, decision
      from Student, Campus, Apply
      where Student.ID = Apply.ID
      and Campus.location = Apply.location) as Mega
where GPA > 3.5 and major = 'CS' and rank > 2;

/**************************************************************
  DELETION FROM SIMPLE ONE-TABLE VIEW
**************************************************************/

create view CSA as
select ID, location
from Apply
where major = 'CS' and decision = 'Y';

select * from CSA;
select * from Apply;

delete from CSA where ID = 123;

/**************************************************************
  AUTOMATIC REWRITE
  Similar deletion for ID = 345
**************************************************************/

delete from Apply where ID = 345
and major = 'CS' and decision = 'Y';

/**************************************************************
  INSERTION INTO SIMPLE ONE-TABLE VIEW
**************************************************************/

create view CSEE as
select ID, location, major
from Apply
where major = 'CS' or major = 'EE';

insert into CSEE values (111, 'San Diego', 'CS');

/**************************************************************
  DISAPPEARING INSERTION
**************************************************************/

insert into CSEE values (222, 'Berkeley', 'psychology');

/**************************************************************
  NAIVE DISAPPEARING INSERTION
  Show CSA definition and Apply first
**************************************************************/

(create view CSA as
 select ID, location
 from Apply
 where major = 'CS' and decision = 'Y';)

insert into CSA values (333, 'Berkeley');

/**************************************************************
  WITH CHECK OPTION
**************************************************************/

create view CSEE2 as
select ID, location, major
from Apply
where major = 'CS' or major = 'EE'
with check option;

create view CSA2 as
select ID, location
from Apply
where major = 'CS' and decision = 'Y'
with check option;

insert into CSEE2 values (444, 'Berkeley', 'psychology');
insert into CSEE2 values (444, 'Berkeley', 'CS');

insert into CSA2 values (444, 'Berkeley');

/**************************************************************
  NON-UPDATABLE SINGLE-TABLE VIEW
  Due to aggregation
**************************************************************/

create view HSgpa(hs,a) as
select sizeHS, avg(gpa)
from Student
group by sizeHS;

delete from HSgpa where hs < 500;

insert into HSgpa values (3000, 3.0);

/**************************************************************
  NON-UPDATABLE SINGLE-TABLE VIEW
  Due to distinct
**************************************************************/

create view Majors as
select distinct major from Apply;

insert into Majors values ('chemistry');

delete from Majors where major = 'CS';

/**************************************************************
  NON-UPDATABLE SINGLE-TABLE VIEW
  Due to subquery referencing same table
**************************************************************/

create view Highest as
select * from Student
where GPA >= all (select GPA from Student);

delete from Highest where name = 'Amy';

/**************************************************************
  UPDATABLE SINGLE-TABLE VIEW WITH SUBQUERY
**************************************************************/

create view Bio as
select * from Student
where ID in (select ID from Apply where major like 'bio%');

delete from Bio where name = 'Bob';

insert into Bio values (555, 'Karen', 3.99, 1000);

/**************************************************************
  JOIN VIEW FOR UPDATES
  Also note schema after view name
**************************************************************/

create view Berk(sID,aID,name,major) as
select Student.ID, Apply.ID, name, major
from Student, Apply
where Student.ID = Apply.ID and location = 'Berkeley';

/**************************************************************
  UPDATE TO JOIN VIEW
**************************************************************/

update Berk set name = 'CS major' where major = 'CS';

/**************************************************************
  AUTOMATIC REWRITE
  Similar update for history major
  [[ Error then create temporary table ]]
**************************************************************/

update Student
set name = 'History major'
where (ID,name) in
  (select Student.ID, name
   from Student, Apply
   where Student.ID = Apply.ID and location = 'Berkeley'
   and major = 'CS');






/**************************************************************
  DISAPPEARING UPDATE
**************************************************************/

update Berk set aID = 666 where aID = 123;

/**************************************************************
  WITH CHECK OPTION
**************************************************************/

create view Berk2(sID,aID,name,major) as
select Student.ID, Apply.ID, name, major
from Student, Apply
where Student.ID = Apply.ID and location = 'Berkeley'
with check option;

update Berk2 set aID = 777 where aID = 678;

/**************************************************************
  INSERTS INTO JOIN VIEW
**************************************************************/

insert into Berk(sID,name) values (777, 'Lance');

insert into Berk2(sID,name) values (888, 'Mary');

insert into Apply values (888, 'Berkeley', 'history', 'Y');

insert into Berk2(sID,name) values (888,'Mary');

insert into Apply values (999, 'Santa Cruz', 'history', 'Y');

insert into Berk2(sID,name) values (999,'Nancy');

/**************************************************************
  CLEANUP
**************************************************************/

drop view CSA;
drop view CSG;
drop view Mega;
drop view CSEE;
drop view CSEE2;
drop view CSA2;
drop view HSgpa;
drop view Majors;
drop view Highest;
drop view Bio;
drop view Berk;
drop view Berk2;