实验四SQL语句.doc

（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 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)