实验四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 o = o
group by Sno
having sum(Credit)< 10
);
查询各门课程取得最高成绩的学生姓名及其成绩；
use实验
select Cno, Sname, Grade
from Student, SC
where o in
(select Cno
from SC
group by Cno
)and
Grade in
(select max(Grade)
from SC
group by Cno
)and
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)。