Notes

create database university;


CREATE TABLE IF NOT EXISTS Student
( snum integer, sname char(30), major char(25),level char(2),age int,
PRIMARY KEY (snum)
);
CREATE TABLE IF NOT EXISTS Faculty
( fid integer, fname char(30), dept_id integer,
PRIMARY KEY (fid)
);
CREATE TABLE IF NOT EXISTS Class
(cname char(40),meets_at char(20),room char(10), fid integer REFERENCES Faculty.fid
);
CREATE TABLE IF NOT EXISTS Enrolled
(
snum integer REFERENCES Student.snum, cname char(40) REFERENCES Class.cname
);

INSERT INTO STUDENT values (051135593,'Maria White','English','SR',21),
(060839453,'Charles Harris','Architecture','SR',22),(099354543,'Susan Martin','Law','JR',20),
(112348546,'Joseph Thompson','Computer Science','SO',19),(115987938,'Christopher Garcia','Computer Science','JR',20),
(132977562,'Angela Martinez','History','SR',20),
(269734834,'Thomas Robinson','Psychology','SO',18),
(280158572,'Margaret Clark','Animal Science','FR',18),
(301221823,'Juan Rodriguez','Psychology','JR',20),
(318548912,'Dorthy Lewis','Finance','FR',18),
(320874981,'Daniel Lee','Electrical Engineering','FR',17),
(322654189,'Lisa Walker','Computer Science','SO',17),
(348121549,'Paul Hall','Computer Science','JR',18),
(351565322,'Nancy Allen','Accounting','JR',19),
(451519864,'Mark Young','Finance','FR',18),
(455798411,'Luis Hernandez','Electrical Engineering','FR',17),
(462156489,'Donald King','Mechanical Engineering','SO',19),
(550156548,'George Wright','Education','SR',21),
(552455318,'Ana Lopez','Computer Engineering','SR',19),
(556784565,'Kenneth Hill','Civil Engineering','SR',21),
(567354612,'Karen Scott','Computer Engineering','FR',18),
(573284895,'Steven Green','Kinesiology','SO',19),
(574489456,'Betty Adams','Economics','JR',20),
(578875478,'Edward Baker','Veterinary Medicine','SR',21);

INSERT INTO Faculty values(142519864,'Ivana Teach',20),
(242518965,'James Smith',68),
(141582651,'Mary Johnson',20),
(011564812,'John Williams',68),
(254099823,'Patricia Jones',68),
(356187925,'Robert Brown',12),
(489456522,'Linda Davis',20),
(287321212,'Michael Miller',12),
(248965255,'Barbara Wilson',12),
(159542516,'William Moore',33),
(090873519,'Elizabeth Taylor',11),
(486512566,'David Anderson',20),
(619023588,'Jennifer Thomas',11),
(489221823,'Richard Jackson',33),
(548977562,'Ulysses Teach',20);

INSERT INTO Class VALUES('Data Structures','MWF 10','R128',489456522),
('Database Systems','MWF 12:30-1:45','1320 DCL',142519864),
('Operating System Design','TuTh 12-1:20','20 AVW',489456522),
('Archaeology of the Incas','MWF 3-4:15','R128',248965255);

INSERT INTO Enrolled VALUES (112348546,'Database Systems'),
(115987938,'Database Systems'),
(348121549,'Database Systems'),
(322654189,'Database Systems'),
(552455318,'Database Systems'),
(455798411,'Operating System Design');

#Task One
create view v1 as
select fname from faculty
where fid not in(select fid from class);

#drop view v1;

select * from v1;

# Task Two
create view v2 as
select sname from student join enrolled
using(snum) where cname in (select cname from class
join faculty using(fid)
where fname='Linda Davis');
select * from v2;

#Task Three
create view stdVu as
select * from student;

select * from stdVu;

# Task Four
alter table student
add course varchar(255);
#Task Five: After changing base table column will be added to original table but view will remain unchanged

#Task Six
create or Replace view v2 as
select sname from student join enrolled
using(snum) where level='JR' and cname in (select cname from class
join faculty using(fid)
where fname='Ivana teach');

#Task 7
create or Replace view v3 as
select sname from student
where major='Computer Science';

select * from v3;

#Task 8
create view v4 as
select cname from class
join faculty using(fid)
where dept_id=68 and fname='John Williams';

select * from v4;

#Task 9;
select distinct age
from student join enrolled
using(snum)
where cname= 'Database Systems'
order by age desc;

#Task 10;
select fname from faculty f
join class c using(fid)
where c.cname in (select e.cname from enrolled e
join student s using(s.snum) where s.sname='Christopher Garcia');

#Task 11
select s.snum,s.sname from student s
join enrolled e using(snum) join class c
using(cname) where fid in(select fid from faculty
where fname='Christopher Davis' and fname='Linda Davis');


#task 12;
drop view v1;
drop view v2;

#Task 13;
update stdVu
set sname='Maria Black'
where snum=51135593;

# we could not update table if it contain aggregate functions,subquery,distinct,union etc but we can update table using joins if it do not contain