该实验中所用数据表

 

1、查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

use student
select student.sno,sname,sdept,SC.cno,grade
from XSKC.student,XSKC.sc
where cno in
(select cno
from XSKC.course
where (cname='数学' or cname='大学英语') and sc.SNO = student.sno
)

2、查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

use student
select student.*
from XSKC.student
where sage not in
(select student.sage
from XSKC.student
where sNAME='张力'
)

 3、按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

use student
select student.sno,sname,sdept,已修学分 = SUM(ccredit) 
from XSKC.student,XSKC.course,XSKC.sc
where student.sno = sc.SNO and sc.cno = course.cno and grade>60
group by student.sno,sname,sdept

4、 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

use student
select student.sno,sname,sc.cno
from XSKC.student,XSKC.sc
where student.sno = sc.sno and sc.sno in(
select sc.sno 
from XSKC.sc,XSKC.student
where cno in(
select cno
from XSKC.sc,XSKC.student
where sNAME='张力' and sc.sno = student.sno
)
)

5、查询只被一名学生选修的课程的课程号、课程名；

use student
select sc.cno,cname
from XSKC.course,XSKC.sc
where sc.cno = course.cno and sc.cno in(
select cno
from XSKC.sc
group by cno
having count(sno)=1
)

6、  使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

use student
select student.sno,sname
from XSKC.student
where sno in(
select sno
from XSKC.sc
where cno in(
select cno
from XSKC.course
where cname='数据结构'
)
)

7、使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

use student
select sname,sage,sdept
from XSKC.student
where sdept<>'CS' and sage<any(
select sage
from XSKC.student
where sdept = 'CS'
)

8、使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

use student
select sname
from XSKC.student
where sdept<>'WM' and sage<ALL(
select sage
from XSKC.student 
where sdept = 'WM')

9、 使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；

use student
select student.sno,sname
from XSKC.student,XSKC.sc
where cno = '1' and student.sno = sc.SNO
intersect
select student.sno,sname
from XSKC.student,XSKC.sc
where cno = '2' and student.sno = sc.SNO

 补充题目：

1、 显示选修02号课程的成绩前两名的学生学号及成绩；

use student 
select top 2 sno,grade
from XSKC.sc
where cno = 2
order by grade desc

2、 显示选修各个课程的及格的人数，及格比率；

use student 
select cno,
及格人数=sum(
case 
when grade >= 60 then 1 else 0
end),
及格比率=100*sum(
case
when grade>=60 then 1 else 0
end)/10
from XSKC.sc
group by cno

3、显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

use student
select sdept as 院系名称,
男生人数=sum(case 
when ssex='男' then 1 else 0
end),
女生人数=sum(case
when ssex='女' then 1 else 0
end)
from XSKC.student
group by sdept

4、列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩； 

use student
select sno,平均成绩=avg(grade)
from XSKC.sc
group by sno
having sum(case
when grade>=60 then 1 else 0
end
)>=2

5、 显示选修课程数最多的学号及选修课程数最少的学号；

use student 
select sno,选修课程数=count(cno)
from XSKC.sc
group by sno
having count(cno)>=all(select count(cno) from XSKC.sc group by sno)
or count(cno)<=all(select count(cno) from XSKC.sc group by sno)