(实验报告模板)实验二数据库

实验报告学院（系）名称：计算机与通信工程学院
【实验过程记录（源程序、测试用例、测试结果及心得体会等）】
2、
1）update SC set Grade=Grade-5 where'数据库'=
(select Cname from Course where o=o)
select*from SC;
2）update SC set Grade=0 where'CS'=
(select Sdept from Student Where Student.Sno=SC.Sno and'2'= (select Cno from Course where o=o))
select*from SC;
3）update SC set Grade=85 where'李勇'=
(select Sname from Student where Student.Sno=SC.Sno and'数据库'= (select Cname from Course where o=o))
select*from SC;
4）delete from SC where Cno=2 and Grade=NUll;
select*from SC;
5）delete from Course where Cno not in(select Cno from SC);
select*from Course;
6）delete from SC where'CS'=
(select Sdept from Student where Student.Sno=SC.Sno and'数据库'= (select Cname from Course where o=o));
select*from SC;
7）create table Ssexcount(Sdept char(20),Ssex char(2),Scount smallint) insert into Ssexcount(Sdept,Ssex,Scount)
select Sdept,Ssex,COUNT(Sno)from Student group by Sdept,Ssex;
select*from Ssexcount; Select*from Student;
8）create table Snocno(Sno char(9),Coursecount smallint,Savg float) insert into Snocno(Sno,Coursecount,Savg)
select Sno,COUNT(Cno),AVG(Grade)from SC group by sno having
avg(grade)>=80; select*from Snocno; select*from sc;
9)create view IS_Student(Sno,Sname,Sdept)as select Sno,Sname,Sdept from Student
where not exists(select * from SC where Sno=Student.Sno); select*from IS_Student;
10)create view IS_S2(Sname,Cname,Grade)
as select Sname,Cname,Grade from Student,Course,SC
where Student.Sno=SC.Sno and o=o;
select*from IS_S2;
11)create view IS_S3(Cno,Ccount,Avggrade,Maxgrade)
as select Cno,COUNT(Sno),A VG(Grade),MAX(Grade)
from SC group by Cno;
select Ccount,Avggrade,Maxgrade from IS_S3where Cno='1';
select Ccount,Avggrade,Maxgrade from IS_S3where Cno='3';
12)create view IS_S4(Cno,Grade) as select Cno,Grade from SC
where Cno='2' and Grade> (select avg(Grade) from SC where Cno='2'); select*from IS_S4;
13)create role user1 grant select on Student to user1;
14)revoke select on Student from user1;。