计算机软件实验报告 - 图文(4)

6. 查找各科成绩都 >= 85 分的学生(Sname, Class)；

Select Sname,Class from Stu where exists (Select * from Score where Stu.Sno=Score.Sno and Score.Cno='01' and Score.Grade>=85)

and exists(Select * from Score where Stu.Sno=Score.Sno and Score.Cno='02' and Score.Grade>=85 ) and exists(Select * from Score where Stu.Sno=Score.Sno and Score.Cno='03' and Score.Grade>=85 ) ;

7. 将课程号为“01”的课程名称修改为“软件技术”

Update Course set Cname='软件技术' where Cno='01';

Select Cno,Cname from Course;

8.修改一名学生的姓名、性别、年龄；

Update Stu set Sname='张三',Sex='m',age='22'where Sno='2561'; Select Sno,Sname,Sex,age from Stu order by Sno;

9.将成绩为55~59分的男生的成绩修改为60分

Update Score set Grade=60 where Sno in(Select Sno from Stu where Sex='m') and Grade between 55 and 59;

Select Sname,Cname,Grade from Stu,Course,Score where Stu.Sno=Score.Sno and Course.Cno=Score.Cno;

10.删除90年以后、80年以前出生的学生的所有信息(包括选课和成绩)

Delete Stu where Sno in(select Sno from Stu where BirthDay < '1980-01-01' or BirthDay>'1990-12-31') ;

Select Sname,Cname,Grade,BirthDay from Stu,Course,Score where Stu.Sno=Score.Sno and Course.Cno=Score.Cno;

11.删除一个班级的所有学生

Delete from Stu where Class='电子1001'; Select Class,Sname from Stu order by Sno;

12. 删除所有数据表和数据库 Drop database MYDB;