实验8 查询复习
一、 程序分析题
已知两个关系表：学生关系表Stu（SNO,SNAME,SAGE,SDEPT），各字段的意义依次为学号、姓名、年龄和所属院系；学生选课关系表SC(SNO,CNO,GRADE)，各字段的意义依次为学号、课程号和成绩
分析并写出以下各段T-SQL代码所能完成的查询功能
1. SELECT Stu.SNO , Stu.SNAME
From Stu, SC
Where Stu.SNO=SC.SNO AND SC.CNO=’06’
查询选修了课程号为06课程的所有学生的学号与姓名
2.SELECT Stu.SNO , Stu.SNAME, Stu.SDEPT, SC.CNO, SC.GRADE
From Stu, SC
Where Stu.SNO=SC.SNO
查询所有学生的学号、姓名、所属院系、选修的课程号与成绩
3.SELECT Stu.SNAME From Stu, SC
Where Stu.SNO=SC.SNO AND SC.CNO=’06’ AND SC.GRADE>95
查询选修了课程号为06课程且成绩超过95分的学生姓名
4.SELECT SNO From SC
Where SCORE=
(SELECT MAX(GRADE) FROM SC Where CNO=’06’)
查询与课程号为06的课程最高成绩相等的学生的学号
5.SELECT SNO ,SNAME,SDEPT,GRADE
From Stu
Where SDEPT IN
(SELECT SDEPT From Stu Where SNAME=’刘春鹅’)
查询与学生‘刘春鹅’在同一个院系的学生信息
6.SELECT SNAME
From Stu
Where NOT EXISTS
(SELECT * FROM SC Where SC.SNO=Stu.SNO and CNO=’06’)
以上代码的查询结果为：
查询没有选修课程号为06课程的学生姓名
7.SELECT SNO
From SC
Where CNO=’02’
Intersect
SELECT SNO
From SC
Where CNO=’06’
以上代码的查询结果为：
查询选修了课程号为02课程的学生学号集合与选修了课程号为06课程的学生学号集合的交集数据集
二、
--(1) 查询课程表中所有课程的课程编号和课程名称。
select * from Course
select courseid,coursename from course
--(2) 查询课程表课程编号为00100001课程的名称和学分。
select * from Course
select Coursename,Mark from Course where(Courseid='00100001')
--(3) 查询所有学分等于4的课程编号和课程名称。
select * from Course
select Courseid,Coursename from Course where (Mark=4)
--(4) 查询所有学分等于4的基础课的课程编号和课程名称。
select * from Course
select Courseid,Coursename from Course where Mark=4 and Type='基础课'
--(5) 查询成绩表中小于80分或大于90分的学生编号、课程编号和成绩。
select * from Score
select * from Score where Score>90 or Score<80
--(6) 查询成绩表中课程编号为00100001的学生编号、课程编号和成绩，结果按成绩升序排列。
select * from Score
select * from Score where Courseid='00100001' order by score
--(7) 查询成绩表中学生编号为10702001的所有成绩，取前3项。
select top 3 score,Studentid,courseid from score where Studentid='10702001'
--(8) 查询成绩表中学生编号为10701001的学生编号、课程编号和成绩，要求结果集中各栏标题分别为“学生编号”、“课程编号”和“成绩”。
select studentid as '学生编号',Courseid as '课程编号',Score as '成绩' from score where studentid='10701001'
--(9) 查询成绩表中课程编号为00100001的最高成绩。
select top 1 Score,Courseid from score where courseid='00100001'
--(10) 查询成绩表中各门课程的最高成绩，要求大于90分
select Courseid ,MAX(Score) from score
where score>90 group by courseid
--(11) 查询“高等数学”课程的所有学生编号和成绩。
select Studentid,score from score
where courseid=
(select Courseid from Course where Coursename='高等数学')
--(12) 查询电子信息系学生的学生编号、课程编号和成绩。
select studentid,courseid,score from score
where Studentid in
(select Studentid from Student
where classid in (select Classid from class
where departid=(select departid from Department where departname='电子信息系')))
--(13) 查询班级名称为电子200701、电子200702、机电200701、机电200702的学生的学生编号、课程编号和成绩。
select studentid,courseid,score from score
where Studentid in (select studentid from Student
where classid in (select classid from Class
where Classname in ('电子200701','电子200702','机电200701','机电200702')))
--(14) 查询20702班比20701班所有学生都小的学生的学生编号、课程编号和成绩。
select studentid,courseid,score from score
where Studentid in(
select studentid from Student
where Classid='20702' and (year(getdate())-year(birthday))<
(select min(year(getdate())-year(birthday)) from Student where
Classid='20701'))
--(15) 查询比所有10701班学生的平均成绩高的学生的学生编号、课程编号和成绩。
select score.Studentid,courseid,score
from score
where Score>(
select AVG(Score)
from Score
join Student on score.studentid=student.Studentid
join class on Student.Classid=class.Classid
where class.Classid='10701')
三、代码分析
1、SELECT Studentid,Studentname FROM Student
查询学生表中所有学生的学号和姓名
2、SELECT DATEPART(yy,GETDATE())-DATEPART(yy,Birthday) FROM Student
查询学生表中所有学生的年龄
说明：DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
语法：DATEPART(datepart,date)
date 参数是合法的日期表达式。
datepart 参数可以是下列的值：
datepart 缩写
年 yy, yyyy 季度 qq, q 月 mm, m
年中的日 dy, y 日 dd, d 周 wk, ww
星期 dw, w 小时 hh 分钟 mi, n
秒 ss, s 毫秒 ms 微妙 mcs
纳秒 ns
实例：
有 \"Orders\" 表如下所示：
OrderId ProductName OrderDate
1 'Computer' 2008-12-29 16:25:46.635
使用如下 SELECT 语句：
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1
结果：
OrderYear OrderMonth OrderDay
2008 12 29
3、SELECT Studentid, Studentname FROM Student WHERE Classid='10801'
查询学生表班级编号为\"10801\"的学生学号和姓名
4、SELECT Studentname,Classid FROM Student WHERE Studentname LIKE '李_'
查询所有姓李且名字是两个字的学生姓名和班级编号
5、SELECT Studentname,Tel FROM Student WHERE Classid='20702' OR Sex='女'
查询20702班的学生或所有班级的女学生的姓名和电话号码
6、SELECT Studentname,Classid FROM Student WHERE Studentname NOT LIKE '李%'
查询所有不姓李的学生姓名和班级编号
7、SELECT SC.Studentid, SC.Courseid, SC.Score FROM Score AS SC
WHERE SC.score<(SELECT AVG(SCA.score) FROM Score AS SCA
WHERE SC.Courseid=SCA.Courseid)
结果：查询成绩低于该门课程平均成绩的学生编号、课程编号和成绩
8、SELECT A.Studentid, A.Studentname FROM Student A
WHERE A.Classid=(SELECT B.Classid FROM Student B
WHERE B.Studentid='10701002' AND A.Studentid!=B.Studentid)
结果：查询学号为10701002的学生的同班同学的学号和姓名(输出结果中不包含10701002号同学本身）
等价于：
select Studentid,Studentname FROM Student
where studentid!='10701002'and Classid=(SELECT Classid FROM Student
WHERE Studentid='10701002')
9、SELECT Studentid, Studentname, Birthday FROM Student
WHERE NOT EXISTS(SELECT Classid FROM Class
WHERE Student.Classid=Class.Classid AND EXISTS(
SELECT Departid FROM Department
WHERE Class.Departid=Department.Departid AND Departname='电子工程系
'))
结果：查询不是“电子工程系”的学生的学号、姓名和出生日期
10、SELECT Classid, COUNT(*) FROM Student GROUP BY Classid
结果:查询各班级的人数
因篇幅问题不能全部显示，请点此查看更多更全内容