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 teaching
go 
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