SQL Server 的数据查询

1、在 SQL SERVER 2008 上附加 teaching 数据库,其中三张表的含义解释如下： 学生表dbo.student 有属性 sno、sname、spec、birthday、email、sex、scholarship, 分别代表学号、姓名、专业、生日、电子邮箱、性别、奖学金； 课程表 dbo.course 有属性 cno、cname、credit、teacher，分别代表课号、课 程名、学分、任课教师；
2、选课表 dbo.student_course 有属性 sno、cno、grade，分别代表学号、课号、 成绩。

use teaching go select sname from student,student_course where student.sno=student_course.sno and sex='男' and cno='10101' and grade>80

2、在 teaching 数据库中完成下列查询: （1）求选修’10101’号课程且成绩大于 80 分的所有男生的姓名；

use teaching go Select student.* from student Where sno in          (select sno           from student_course          Where cno='10102' and sno in                               (select sno                               from student_course                 Where cno='10104')) 或者： use teachinggo Select student.*  from student Where sno in      (select sc1.sno       from student_course sc1,student_course sc2       Where sc1.cno='10102' and sc2.cno='10104' and sc1.sno=sc2.sno) 

（3）求每个学生所选课程的平均成绩，并用查询结果来创建一个新的数据表 XSPJCJ(sno,sname,avggrade)；

use teaching go Select s.sno,sname,avg(grade) as avggrade Into XSPJCJ From student s,student_course sc Where s.sno=sc.sno  Group by s.sno,sname 

（4）求选修全部课程的所有学生的学号和姓名；

use teaching go Select s.sno,sname From student s,student_course sc Where s.sno=sc.sno Group by s.sno,sname having count(*)=(select count(cno) From course)

（5）求课程不及格学生的课号、课程名、学号、姓名及成绩；

use teaching go Select c.cno,cname,s.sno,sname,grade From student s,student_course sc,course c Where s.sno=sc.sno and sc.cno=c.cno and grade<60 

（6）查询选修’Java 程序设计’课程的学生学号和姓名；

use teaching go Select s.sno,sname From student s,student_course sc,course c Where s.sno=sc.sno and sc.cno=c.cno and cname='Java程序设计' 

（7）查询所有低于学生选课平均成绩的学生情况；

use teaching go Select distinct s.* From student s,student_course sc Where s.sno=sc.sno and grade <        (select avg(grade) from student_course) 

（8）检索’信管’专业的学生信息，包括学号、姓名、性别；

use teaching go select sno,sname,sex from student where spec='信管' 

（9）检索’网络’专业且有课程成绩不及格（<60）的学生信息，包括学号、姓 名、课程名和分数；

use teaching go select s.sno,sname,cname,grade from student s,student_course sc,course c where s.sno=sc.sno and sc.cno=c.cno and spec='网络' and grade<60

（10）检索有学生成绩为满分(100 分)的课程的课程号、课程名和学分。

use teaching go select distinct c.cno,cname,credit from student_course sc,course c where sc.cno=c.cno and grade=100