SQL数据库题库及答案.docx

实验8 查询复习一、 程序分析题已知两个关系表：学生关系表Stu（SNO,SNAME,SAGE,SDEPT），各字段的意义依次为学号、姓名、年龄和所属院系；学生选课关系表SC(SNO,CNO,GRADE)，各字段的意义依次为学号、课程号和成绩分析并写出以下各段T-SQL代码所能完成的查询功能1. SELECT Stu.SNO , Stu.SNAME From Stu, SCWhere Stu.SNO=SC.SNO AND SC.CNO=’06’查询选修了课程号为06课程的所有学生的学号与姓名2.SELECT Stu.SNO , Stu.SNAME, Stu.SDEPT, SC.CNO, SC.GRADEFrom Stu, SCWhere Stu.SNO=SC.SNO查询所有学生的学号、姓名、所属院系、选修的课程号与成绩3.SELECT Stu.SNAME From Stu, SCWhere Stu.SNO=SC.SNO AND SC.CNO=’06’ AND SC.GRADE>95查询选修了课程号为06课程且成绩超过95分的学生姓名4.SELECT SNO From SCWhere SCORE=(SELECT MAX(GRADE) FROM SC Where CNO=’06’)查询与课程号为06的课程最高成绩相等的学生的学号5.SELECT SNO ,SNAME,SDEPT,GRADEFrom StuWhere SDEPT IN(SELECT SDEPT From Stu Where SNAME=’刘春鹅’)查询与学生‘刘春鹅’在同一个院系的学生信息6.SELECT SNAMEFrom StuWhere NOT EXISTS(SELECT * FROM SC Where SC.SNO=Stu.SNO and CNO=’06’)以上代码的查询结果为：查询没有选修课程号为06课程的学生姓名7.SELECT SNOFrom SCWhere CNO=’02’IntersectSELECT SNOFrom SCWhere CNO=’06’以上代码的查询结果为：查询选修了课程号为02课程的学生学号集合与选修了课程号为06课程的学生学号集合的交集数据集二、--(1) 查询课程表中所有课程的课程编号和课程名称。

select * from Courseselect courseid,coursename from course--(2) 查询课程表课程编号为00100001课程的名称和学分select * from Courseselect Coursename,Mark from Course where(Courseid='00100001')--(3) 查询所有学分等于4的课程编号和课程名称select * from Courseselect Courseid,Coursename from Course where (Mark=4)--(4) 查询所有学分等于4的基础课的课程编号和课程名称select * from Courseselect Courseid,Coursename from Course where Mark=4 and Type='基础课'--(5) 查询成绩表中小于80分或大于90分的学生编号、课程编号和成绩select * from Scoreselect * from Score where Score>90 or Score<80 --(6) 查询成绩表中课程编号为00100001的学生编号、课程编号和成绩，结果按成绩升序排列。

select * from Scoreselect * 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 scorewhere 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 scorewhere Score>(select AVG(Score) from Score join Student on score.studentid=student.Studentidjoin class on Student.Classid=class.Classidwhere 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 季度 , q 月 mm, m 年中的日 dy, y 日 dd, d 周 wk, ww星期 dw, w 小时 hh 分钟 mi, n秒 ss, s 毫秒 ms 微妙 mcs纳秒 ns实例：有 "Orders" 表如下所示：OrderId ProductName OrderDate1 'Computer' 2008-12-29 16:25:46.635使用如下 SELECT 语句：SELECT DATEPART(yyyy,OrderDate) AS OrderYear,DATEPART(mm,OrderDate) AS OrderMonth,DATEPART(dd,OrderDate) AS OrderDayFROM OrdersWHERE OrderId=1结果：OrderYear OrderMonth OrderDay2008 12 293、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 SCWHERE SC.score<(SELECT AVG(SCA.score) FROM Score。