数据库理论与技术作业.docxVIP

2016-2017（2）湖北大学研究生课程考试试题课程编号07110003课程名称数据库理论与设计数据库高级设计技术开课单位任课教师姓名学号试卷说明：试卷满分100分，开卷考试，可在计算机上实际操作，考试时间180分钟。一、简单应用题（30分）根据学生-课程数据库的如下三个表: (1)学生表：Student（Sno, Sname, Ssex, Sage, Sdept），属性分别为：学号、姓名、性别、年龄、所在系；(2)课程表：Course(Cno, Cname, Cpno, Ccredit) ，属性分别为：课程号、课程名、先修课的课程号、学分；(3)学生选课表：SC(Sno, Cno, Grade) ，属性分别为：学号、课程号、成绩，成绩是百分制。完成如下任务：1、安排教学计划的老师需要知道Course中每门课程的课程号、课程名及其间接先修课的课程号和课程名，即他需要看到这样一个数据表：课程号、课程名、间接先修课课程号、间接先修课课程名，按课程号升序排序。若要在功能页面实现这个功能，请给出你的设计方案及对应的SQL语句（6分）。 select CONNECT_BY_ROOT(Cno) 课程号,CONNECT_BY_ROOT(Cname) 课程名,Cno 间接先修课程号,Cname 间接先修课程名 from Course where LEVEL 1 start with Cno in (select Cno from Course) connect by Cno=prior Cpno UNION select Cno,Cname,0,无 FROM Course where CPNO=02、教务处老师需要查看每门课程的课程名，选课人数，平均分，最高分，最低分。但程序开发人员只会写简单的SQL语句，你作为数据库设计人员针对该要求用SQL脚本的形式给出你的设计结果（6分）。SELECT Course.Cname 课程名,COUNT (Student.Sno) 选课人数, AVG(SC.Grade) 平均分, MAX(Grade) 最高分, MIN(Grade) 最低分FROM(CourseLEFT JOIN SC ON Course.Cno = SC.Cno)LEFT JOIN Student ON SC.Sno = Student.SnoGROUP BY Course.Cname;3、请给出查询“数据结构”课程的所有各级后继课程的课程号、课程名、先修课的课程号、学分的SQL语句（6分）。 SELECT * FROM Course START WITH Cname=数据结构 CONNECT BY PRIOR Cno= Cpno;4、某功能页面需要显示如下数据表格：学生课程成绩表学号姓名1号课程成绩2号课程成绩3号课程成绩请给出你的设计方案及对应的SQL语句（6分）。使用pivot实现。CREATE OR REPLACE procedure test_count(cur out sys_refcursor)asCursor cursor is select DISTINCT Cno from SC;ParastrSQL varchar2(6000); -- 主语句state varchar2(600);begin for name in cursor LOOP state:=state||name.CNO||,; end LOOP;state:=RTRIM(state,,); ParastrSQL :=select * from (select SC.Sno,Student.Sname,SC.Cno,SC.Grade from SC,Student WHERE SC.Sno=Student.Sno) pivot (SUM(Grade) for Cno in (||state||));open cur for ParastrSQL;end test_count;5、现在需要把百分制成绩转换为等级制成绩（100-90分为优秀、80-89分为良好、70-79分为中等、60-69分为较差、0-59分为不及格），查询结果为：学号、姓名、课程号、课程名、百分制成绩、等级制成绩，请给出相应的SQL语句（6分）。SELECT Student.Sno,Student.Sname,Course.Cno,Course.Cname,SC.Grade, (CASE WHEN Grade=90 THEN 优秀 WHEN Grade=80 THEN 良好 WHEN Grade=70 THEN 中等 WHEN Grade=60 THEN 较差 ELSE 不及格 END)GRANK FROM Student,Course,SC