实验五答案

实验五：

实验过程：

1.插入如下学生记录（学号：95030，姓名：李莉，年龄：18）insert into student(Sno,Sname,Sage) values('95030','李莉',18);

2.插入如下选课记录（95030，1）insert into sc(Sno,Cno) values('08001','007');

3.计算机系学生年龄改成20 update student set Sage=20 where Sdept='cs';

4.数学系所有学生成绩改成0 update student set Sage=0 where Sdept='ma';

5.把低于总平均成绩的女同学成绩提高5分update sc set Grade=Grade+5 where grade<(select avg(grade) from sc)and sno in(select sno from student where ssex='女');

6.修改2号课程的成绩，若成绩小于75提高5%，成绩大于75提高4%（两个语句实现，注意顺序）update sc set grade=grade*1.05 where cno='002' and grade<75; update sc set grade=grade*1.04 where cno='002' and grade>75;

7.删除95030学生信息delete from sc where sno='95030';

8.删除sc表中无成绩记录delete from sc where grade is null;

9.删除张娜的选课记录delete from sc where sno = (select sno from student where sname='张那');

10.删除数学系所有学生选课记录delete from sc where sno in (select sno from student where sdept='ma');

11.删除不及格的学生选课记录delete from sc where sno in (select sno from sc where grade<60);

12.查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往已经存在的基本表stu（sno，sname，ssex）中insert into stu(Sno,Sname,Ssex)select sno,sname,ssex from student where sno in (select sno from sc group by sno having min(grade)>80); select sno,sname,ssex into xtu from student where sno in (select sno from sc group by sno having min(grade)>80);

13.把所有学生学号和课程号连接追加到新表中select sno+cno as newline into xdu from sc;

14.所有学生年龄增加1 update student set sage=sage+1;

15.统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中select sname,sdept into newtable from student where sno in (select sno from sc where grade<60 group by sno having count(grade)>=3); insert into ntable select sname,sdept from student where sno in (select sno from sc where grade<60 group by sno having count(grade)>=3);