SQL数据库题库及答案.docxVIP

实验8 查询复习一、程序分析题已知两个关系表：学生关系表Stu（SNO,SNAME,SAGE,SDEPT），各字段的意义依次为学号、姓名、年龄和所属院系；学生选课关系表SC(SNO,CNO,GRADE)，各字段的意义依次为学号、课程号和成绩分析并写出以下各段T-SQL代码所能完成的查询功能SELECT Stu.SNO , Stu.SNAMEFrom 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.SNAMEFrom Stu, SCWhere Stu.SNO=SC.SNO AND SC.CNO=’06’ AND SC.GRADE95查询选修了课程号为06课程且成绩超过95分的学生姓名4.SELECT SNOFrom 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) 查询课程表课程编号程的名称和学分。select * from Courseselect Coursename,Mark from Course where(Courseid--(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 Score90 or Score80--(6) 查询成绩表中课程编号学生编号、课程编号和成绩，结果按成绩升序排列。select * from Scoreselect * from Score where Courseid order by score--(7) 查询成绩表中学生编号所有成绩，取前3项。select top 3 score,Studentid,courseid from score where Studentid-(8) 查询成绩表中学生编号学生编号、课程编号和成绩，要求结果集中各栏标题分别为“学生编号”、“课程编号”和“成绩”。select studentid as 学生编号,Courseid as 课程编号,Score as 成绩 from score where studentid-(9) 查询成绩表中课程编号最高成绩。select top 1 Score,Courseid from score where courseid-(10) 查询成绩表中各门课程的最高成绩，要求大于90分select Courseid ,MAX(Score)from