数据库原理实验（五）

CREATE VIEW IS1
AS 
select sno,sname,ssex,sage
from student
where sdept='IS';

CREATE VIEW IS2
AS 
select sno,sname,ssex,sage
from student
where sdept='IS'


create view IS3
AS
select student.sno,sname,grade
from student,sc
where student.sno=sc.sno and sdept='IS' and cno in(select cno from course where cname='数据库')

create view IS4
AS
select sno,sname,grade
from IS3
where grade>90

create view S_Birth
AS
select sno,sname,sage
from student

create view S_Max
AS
select cno,max(grade) as m
from sc
GROUP BY cno

DROP VIEW IS3

#SELECT sno,sname,sage
#FROM student
#WHERE Sdept='IS' AND sage<19
SELECT sno,sname,sage
FROM IS2
WHERE sage<19

select sno
from IS3
where grade=(select min(grade) from IS3 group by sno)

SELECT Cno,m
FROM S_Max
WHERE m>90

UPDATE IS1
SET sage=0
WHERE Ssex='男'

update S_Max
set max_grade=100
where cno=1
#不能执行，因为包含派生域或者常量域

DELETE 
FROM IS2
WHERE sname='张兰'