数据库语言SQL作业解答.doc

SQL语言习题1. 关系数据模型如下：学生S(SNO,SN,SEX,AGE)课程C(CNO,CN,PCNO) PCNO为直接先行课号选课SC(SNO,CNO,GR) GR为课程考试成绩用SQL写出查询程序：选修课程“DB”的学生姓名SN。方法1. SELECT SN FROM S WHERE SNO IN ( SELECT SNO FROM SC WHERE CNO IN ( SELECT CNO /* = */ FROM C WHERE CN = DB ) );方法2. SELECT SN FROM S, SC, C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNOAND CN = “DB”;2. 关系数据模型如下：学生S(SNO,SN,SEX,AGE)课程C(CNO,CN,PCNO) PCNO为直接先行课号选课SC(SNO,CNO,GR) GR为课程考试成绩用SQL写出查询程序：查询课程名和它的直接先行课的课程名以及它的间接先行课的课程名。SELECT CN, CX.CN, CY.CN FROM C，C CX，C CY WHERE C.PCNO=CX.CNOAND CX.PCNO=CY.CNO例41找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno，Cno FROM SC xWHERE Grade = ( SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno );3. 关系数据模型如下 P84 例4.26学生S(SNO,SN,SEX,AGE)课程C(CNO,CN,PCNO) PCNO为直接先行课号选课SC(SNO,CNO,GR) GR为课程考试成绩用SQL写出查询程序：所有学生都选修的课程名CN。方法1.SELECT CN FROM C WHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS( SELECT * FROM SC WHERE SNO=S.SNO AND CNO=C.CNO);(x)P ($ x( P)变换后语义：不存在这样的学生x，该学生没有选修P。方法2. SELECT CN FROM C WHERE CNO IN ( SELECT CNO FROM SC GROUP BY CNO HAVING COUNT(*) = ( SELECT COUNT(*) FROM S );例46 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；变换后语义：不存在这样的课程x，该学生没有选修P。4. 假设学生一课程数据库关系模式如下：Student(Sno,Sname,Sage,Ssex);Course(Cno,Cname,Teacher);SC(Sno,Cno,Grade)。用SQL语句表达下列查询：(1) 找出刘老师所授课程的课程号和课程名;(2) 找出年龄小于22岁的女学生的学号和姓名。1. SELECT Cno，Cname FROM Course WHERE Teacher LIKE “刘” /* % _ */2. SELECT SNO, SNAME FROM StudentWHERE SAGE 903 SELECT Sno 教科书：P80* 例4.18 FROM SC SC1, SC SC2WHERE SC1.SnoSC2.Sno AND SC1.CnoC1 AND SC2.CnoC3 例51 查询既选修了课程1又选修了课程2的学生 SELECT Sno FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2 )；SELECT SnoFROM SC WHERE CnoC1 OR CnoC3 /* AND */6. 假设学生一课程数据库关系模式如下：Student(Sno,Sname,Sage,Ssex);Course(Cno,Cname,Teacher);SC(Sno,Cno,Grade)。用SQL语句表达下列查询：（1）求孙老师讲的每门课的学生平均成绩；（2）统计选修各门课的学生人数。输出课程号和人数。查询结果按人数降序排列，若人数相同，则按课程升序排列答案1. SELECT CNO, AVG(GRADE) ！ FROM SC, COURSEWHERE SC.CNO=COURSE.CNO AND TEACHER LIKE “孙老师” GROUP BY CNO2. SELECT CNO, COUNT(*) FROM SCGROUP BY CNOOREDR BY COUNT(*) DESC, CNO7. 假设学生一课程数据库关系模式如下：Student(Sno,Sname,Sage,Ssex);Course(Cno,Cname,Teacher);SC(Sno,Cno,Grade)。用SQL的更新语句表达对数据库的下列更新操作：（1）向学生关系Student中插入一个学生元组（990012，梅立松，20，女）。（2）从学生选课关系SC中删除夏春秋同学的所有元组。（3）在学生选课关系SC中，把英语课的成绩提高10%。答案1. INSERT INTO STUDENT VALUES （990012，梅立松，20，女）插入：子查询2. DELETE FROM SCWHERE SNO IN /* = */( SELECT SNO FROM STUDENT WHERE SNAME = “夏春秋”);3. UPDATE SC SET GRADE =1.1*GRADEWHERE CNO IN ( SELECT CNO FROM COURSE WHERE CNAME = “英语”);例7 将计算机科学系全体学生的成绩置零。UPDATE SCSET Grade=0WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；UPDATE SC SET Grade = 0WHERE SNO IN ( SELECT SNO FROM S