实验四SQL语句

（2） 修改数据

将张星老师数据结构课的学生成绩全部加2分。

use 实验 update SC

set Grade = Grade + 2 where Cno in

(select Cno from Course

where Cname = '数据结构' );

（3） 删除数据

删除马朝阳同学的所有选课记录。

use 实验 delete from SC where Sno in

(select Sno from Student

where Sname = '马朝阳' );

2. 查询操作

（1） 单表查询

查询所有学生的信息；

use 实验 select * from Student

查询所有女生的姓名；

use 实验

select Sname from Student where Ssex = '女'

查询成绩在80 到89 分之间的所有学生选课记录，查询结果按成绩的降序排列；

use 实验 select * from SC

where Grade between 80 and 89 order by Grade desc;

查询各个系的学生人数；

use 实验

select Sdept,count(Sdept) count from Student group by Sdept;

（2） 连接查询

查询信息系年龄在21 岁以下（含21 岁）的女生姓名及其年龄。

use 实验

select Sname, Sage from Student, Dept

where Dname = '信息' and Sage <= 21 and Dept.Deptno = Student.Sdept;

（3） 嵌套查询

查询选课总学分在10 分以下的学生姓名；

use 实验

select Sname from Student where Sno in

(select Sno from Course, SC

where Course.Cno = SC.Cno group by Sno

having sum(Credit) < 10 );

查询各门课程取得最高成绩的学生姓名及其成绩；

use 实验

select Cno, Sname, Grade from Student, SC where SC.Cno in

(select Cno from SC group by Cno ) and

(select max(Grade) from SC group by Cno ) and

Grade in

Student.Sno = SC.Sno

order by Cno;

查询选修了101 学生选修的全部课程的学生学号；

use 实验 select Cno

from Course where Ctno = 101;

use 实验 select Sno from SC where Cno = 1

and Sno in

(select Sno from SC

where Cno = 4)；

查询选修了张星老师开设的全部课程的学生姓名。

use 实验 select Cno

from Teacher, Course

where Tname = '张星' and Tno = Ctno;

use 实验

select Sname from SC, Student

where SC.Sno = Student.Sno

and Cno = 1 and SC.Sno in

(select Sno from SC where Cno = 4)