实验四 数据查询
1、 简单查询
（1）查询数学系学生的姓名、年龄。
use Stu_Cy
select Sname,year(getdate())-year(Sbirth) age
from Student
where Sdept='数学'
（2）统计必修课程和选修课程的门数。
use Stu_Cy
select Ctype,count(Cno)
from Course
group by Ctype
（3）查询学生成绩在80到100之间的学生学号。
use Stu_Cy
select DISTINCT Sno
from SC
where Grade between 80 and 100
（4）查询以“数据_”开头的课程名称的课程情况。
use Stu_Cy
select *
from Course
where Cname like '数据库\_%' escape '\'
（5）查询学号是‘95001’、‘95002’、‘96001’的学生的学号、姓名、所在系。
use Stu_Cy
select Sno,Sname,Sdept
from Student
where Sno in(95001,95002,96001)
（6）查询已修学分位具前三名的学生的姓名和已修学分。
use Stu_Cy
select top 3 Sname,Spre
from Student
order by Spre DESC
（7）查询选课学生中平均成绩大于70分的学生学号及平均成绩（ave）（‘ave’作为新的列名），查询结果按平均成绩的降序排列。
use Stu_Cy
select Sno,AVG(Grade) ave
from SC
group by Sno
having AVG(Grade)>70
order by ave DESC
2、连接查询
（1）查询计算机系选修‘1’号课程的学生姓名、性别和成绩。
use Stu_Cy
select st.Sname,st.Ssex,sc.Grade
from Student st,SC sc
where st.Sno=sc.Sno and https://www.wendangku.net/doc/db8755765.html,o=1 and st.Sdept='计算机'
（2）查询选修“操作系统”课的学生学号、姓名。
use Stu_Cy
select st.Sno,st.Sname
from Student st,SC sc,Course c
where st.Sno=sc.Sno and https://www.wendangku.net/doc/db8755765.html,o=https://www.wendangku.net/doc/db8755765.html,o and https://www.wendangku.net/doc/db8755765.html,ame='操作系统'
（3）查询每门课程的选修情况，列出课程号、课程名、选修该课学生学号及成绩（外连接）。
use Stu_Cy
select https://www.wendangku.net/doc/db8755765.html,o,Cname,sc.Sno,Grade
from Course c
left outer join SC sc
on(https://www.wendangku.net/doc/db8755765.html,o=https://www.wendangku.net/doc/db8755765.html,o)
（4）查询每个班级的学生人数及其总平均成绩。
use Stu_Cy
select st.Sclass,count(distinct st.Sclass),AVG(sc.Grade)
from Student st,SC sc
where st.Sno=sc.Sno
group by st.Sclass
3、嵌套查询
（1）查询计算机系选修‘1’号课程的学生姓名、性别(请分别用一般嵌套查询和带exists的嵌套查询)。
use Stu_Cy
select Sname,Ssex
from Student
where Sno in (select Sno
from SC
where Cno='1')
and Sdept='计算机'
use Stu_Cy
select Sname,Ssex
from Student
where exists
(select *
from SC
where Sno=Student.Sno and Cno='1')
and Sdept='计算机'
（2）在选修‘2’号课程的学生中，查询成绩比‘2’号课程平均成绩高的学生的学号和成绩。
use Stu_Cy
select Sno,Grade
from SC
where Cno='2' and Grade>(select AVG(Grade)
from SC
where Cno='2')
（3）查询已修学分比计算机系学生中最高已修学分高的所有学生的学号、姓名、系别和已修学分。
use Stu_Cy
select Sno,
Sname,Sdept,Spre
from Student
where Spre>(select MAX(Spre)
from Student
where Sdept='计算机')
and Sdept<>'计算机'
（4）查询和李勇在同一个系并且年龄比李勇大的学生的学号、姓名、生日、所在系。
use Stu_Cy
select Sno,Sname,Sbirth,Sdept
from Student
where Sdept=(select Sdept
from Student
where Sname='李勇')
and Sbirth<(select Sbirth
from Student
where Sname='李勇')
and Sname<>'李勇'
（5）查询所有学生都选修的课程情况。
use Stu_Cy
select *
from Course
where not exists
(select *
from Student
where not exists
(select *
from SC
where Cno=https://www.wendangku.net/doc/db8755765.html,o and Sno=Student.Sno))
（6）查询至少选修了学号为‘96001’的学生选修的全部课程的学生的学号。
use Stu_Cy
select distinct Sno
from SC SCX
where not exists
(select *
from SC SCY
where SCY.Sno = '96001' and
not exists
(select *
from SC SCZ
where SCZ.Sno=SCX.Sno and
https://www.wendangku.net/doc/db8755765.html,o=https://www.wendangku.net/doc/db8755765.html,o));
4、集合查询
（1）查询学号为‘95001’或‘95002’的学生选修的课程号。
use Stu_Cy
select Cno
from SC
where Sno='95001'
UNION
select Cno
from SC
where Sno='95002'
（2）查询学号为‘95001’的学生选修而学号为‘95002’的学生没有选修的课程号。
use Stu_Cy
select Cno
from SC sc1
where Sno='95001' and not exists
(select *
from SC sc2
where Sno='95002' and Cno=https://www.wendangku.net/doc/db8755765.html,o)
（3）查询学号为‘95001’和‘95002’的学生都选修的课程号。
use Stu_Cy
select Cno
from SC sc1
where Sno='95001' and exists
(select *
from SC sc2
where Sno='95002' and Cno=https://www.wendangku.net/doc/db8755765.html,o)