《数据库原理及应用》01章 03章 习题答案解读课件.ppt
习题,P19. 第9题,P19. 第10题,P35. 第11题 ；P68. 第4题,(1) 检索老师“李诚”所授课程的课程号和课程名。SELECT CNO,CNAMEFROM CWHERE TEACHER=李诚;,P35. 第11题 ；P68. 第4题,(2) 检索年龄大于20岁的男学生的学号和姓名。SELECT SNO,SNAMEFROM SWHERE AGE20 AND SEX=男;,P35. 第11题 ；P68. 第4题,(3) 检索考试成绩不及格的学生姓名和课程名。SELECT SNAME,CNAMEFROM S,SC,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SCORE60;,P35. 第11题 ；P68. 第4题,(4) 检索学号为“091201”的学生所学课程的课程名与成绩。SELECT CNAME,SCOREFROM SC,CWHERE C.CNO=SC.CNO AND SNO= 091201;,P35. 第11题 ；P68. 第4题,(5) 检索至少选修老师“李诚”所授课程中一门课的学生姓名。SELECT DISTINCT SNAMEFROM SC,C,SWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND TEACHER=李诚;,P35. 第11题 ；P68. 第4题,(6) 检索选修了“数据库原理”的所有学生的学号、姓名和成绩。SELECT S.SNO,SNAME,SCOREFROM SC,C,SWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CNAME=数据库原理;,P35. 第11题 ；P68. 第4题,(7) 检索学生“李伟”不学的课程的课程号。SELECT CNOFROM CWHERE CNO NOT IN ( SELECT CNO FROM SC,S WHERE S.SNO=SC.SNO AND SNAME=李伟 ) ;,P35. 第11题 ；P68. 第4题,(8) 检索至少选修两门课的学生学号。SELECT DISTINCT A.SNOFROM SC A, SC BWHERE A.SNO=B.SNO AND A.CNO!=B.CNO ;,P35. 第11题 ；P68. 第4题,(8) 检索至少选修两门课的学生学号。或：SELECT SNOFROM SCGROUP BY SNO HAVING COUNT(CNO)=2 ;,P35. 第11题 ；P68. 第4题,(9) 检索全部学生都选修的课程的课程号与课程名。,SELECT CNO,CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM SC WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO ),P35. 第11题 ；P68. 第4题,(10) 检索选修课程包含老师“李诚”所授全部课程的学生学号。,SELECT SNO FROM S WHERE NOT EXISTS ( SELECT * FROM C WHERE TEACHER= 李诚 AND NOT EXISTS ( SELECT * FROM SC WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO ),P68 第6题,针对第2章习题11题中的三个关系：学生关系S(学号SNO，姓名SNAME，年龄AGE，性别SEX)学生选课关系SC(学号SNO，课程号CNO，成绩SCORE)课程关系C(课程号CNO，课程名CNAME，任课教师TEACHER)试用SQL语句完成以下操作：,P68 第6题,(1) 检索尚未安排任课教师的课程号和课程名。SELECT CNO,CNAMEFROM CWHERE TEACHER IS NULL ;,P68 第6题,(2) 检索年龄在1922岁之间的学生的基本信息。SELECT *FROM SWHERE AGE BETWEEN 19 AND 22;,P68 第6题,(3) 检索每门课程的平均成绩，结果按平均成绩降序排列。SELECT CNO, AVG(SCORE)FROM SCGROUP BY CNOORDER BY AVG(SCORE) DESC,P68 第6题,(4) 检索至少15个同学选修的课程号和课程名。SELECT CNO, CNAMEFROM CWHERE CNO IN (SELECT CNO FROM SC GROUP BY CNO HAVING COUNT(SNO)=15),P68 第6题,(5) 检索选修了C01且成绩高于平均成绩的学号、姓名和成绩。结果按成绩降序排列。SELECT S.SNO, SNAME, SCOREFROM S, SCWHERE S.SNO=SC.SNO AND SC.CNO=C01 AND SCORE ( SELECT AVG(SCORE) FROM SC WHERE CNO=C01 )ORDER BY SCORE DESC,P68 第6题,(6) 向关系C中插一个新元组(C08，数据结构，高明)。INSERT INTO CVALUES( C08 ， 数据结构 ， 高明 );,P68 第6题,(7) 删除SC中尚无成绩的选课元组。DELETE FROM SCWHERE SCORE IS NULL;,P68 第6题,(8) 把选修“数据库原理”且不及格的学生成绩全改为60分。UPDATE SCSET SCORE=60WHERE SCORE60 AND CNO IN (SELECT CNO FROM C WHERE CNAME= 数据库原理);,P68 第6题,(9) 检索至少教授30名学生的教师姓名，并把检索结果写入另一个已存在的表T(TNAME)。INSERT INTO T SELECT TEACHER FROM SC,C WHERE SC.CNO=C.CNO GROUP BY TEACHER HAVING COUNT(SNO)=30,P68 第6题,(10) 建立一个平均成绩在90分以上的学号、姓名和平均成绩的视图Good_S。CREATE VIEW Good_S(SNO,SNAME,AVGSCORE) ASSELECT S.SNO, SNAME, AVG(SCORE)FROM S,SCWHERE S.SNO=SC.SNOGROUP BY S.SNO, SNAME HAVING AVG(SCORE)90,P36 第12题； P68. 第5题,已知四个关系：(1)供应商关系S(供应商编号SNO，供应商名称SN，所在城市CITY)(2)零件关系P(零件编号PNO，零件名称PN，颜色COLOR)(3)工程项目关系J(工程项目编号JNO，工程项目名称JN，所在城市CITY)(4)供应关系SPJ(供应商编号SNO，工程项目编号JNO，零件编号PNO，数量QTY)S（SNO，SN，CITY）P（PNO，PN，COLOR）J（JNO，JN，CITY）SPJ（SNO，PNO，JNO，QTY）,(1) 检索供应工程J01零件的供应商编号。(2) 检索北京供应商的编号和姓名。(3) 检索供应工程J01红色零件的供应商编号。(4) 检索供应商S02供应工程J02的零件编号、零件名称和数量。(5) 检索使用“上海宏光”供应零件的工程项目编号和工程项目名称。(6) 检索供应红色零件的供应商名称。(7) 检索没有使用上海供应商生产的红色零件的工程编号。(8) 检索提供全部零件的供应商名。(9) 检索至少用了S01供应商所供应的全部零件的工程编号。,(1) 检索供应工程J01零件的供应商编号。SELECT DISTINCT SNO FROM SPJWHERE JNO=J01;,(2) 检索北京供应商的编号和姓名。SELECT SNO,SNFROM SWHERE CITY=北京;,(3) 检索供应工程J01红色零件的供应商编号。,(3) 检索供应工程J01红色零件的供应商编号。SELECT SNO FROM SPJ, PWHERE SPJ.PNO=P.PNO AND COLOR=红 AND JNO=J01;,或：SELECT SNO FROM SPJWHERE PNO IN ( SELECT PNO FROM P WHERE COLOR=红) AND JNO=J01;,(4) 检索供应商S02供应工程J02的零件编号、零件名称和数量。SELECT SPJ.PNO,PN,QTYFROM SPJ,PWHERE SPJ.PNO=P.PNO AND SNO= S02 AND JNO=J02;,(5) 检索使用“上海宏光”供应零件的工程项目编号和工程项目名称。SELECT J.JNO,JNFROM S,SPJ,JWHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND SN=上海宏光;,(6) 检索供应红色零件的供应商名称。SELECT SNFROM S,SPJ,PWHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND COLOR=红;,(7) 检索没有使用上海供应商生产的红色零件的工程编号。,(7) 检索没有使用上海供应商生产的红色零件的工程编号。SELECT JNO FROM J WHERE JNO NOT IN ( SELECT JNO FROM SPJ, S, P WHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO AND CITY =上海 AND COLOR=红) ;,(7) 检索没有使用上海供应商生产的红色零件的工程编号。SELECT JNOFROM JWHERE NOT EXISTS ( SELECT * FROM SPJ,S, P WHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO AND J.JNO = SPJ.JNO AND CITY = 上海 AND COLOR = 红);,(7) 检索没有使用上海供应商生产的红色零件的工程编号。SELECT JNOFROM JWHERE NOT EXISTS ( SELECT * FROM SPJ WHERE J.JNO = SPJ.JNO AND SNO IN ( SELECT SNO FROM S WHERE CITY = 上海) AND PNO IN ( SELECT PNO FROM P WHERE COLOR = 红);,(8) 检索提供全部零件的供应商名。,(8) 检索提供全部零件的供应商名。SELECT SN FROM S WHERE NOT EXISTS （SELECT * FROM P WHERE NOT EXISTS ( SELECT * FROM SPJ WHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO ),(9) 检索至少用了S01供应商所供应的全部零件的工程编号。,(9) 检索至少用了S01供应商所供应的全部零件的工程编号。SELECT DISTINCT JNOFROM SPJ XWHERE NOT EXISTS (SELECT * FROM SPJ Y WHERE Y.SNO= S01 AND NOT EXISTS (SELECT * FROM SPJ Z WHERE Z.SNO=S01 AND Z.PNO=Y.PNO AND Z.JNO=X.JNO);,用SQL语句建立第2章习题12中的四个表。,(1)CREATE TABLE S (SNO CHAR(4), SN CHAR(15), CITY CHAR(15);(2)CREATE TABLE P (PNO CHAR(4), PN CHAR(15), COLOR CHAR(4) );,(3)CREATE TABLE J (JNO CHAR(4), JN CHAR(15), CITY CHAR(15) );(4)CREATE TABLE SPJ (SNO CHAR(4), JNO CHAR(4), PNO CHAR(4), QTY INT);,P68 第3题,用SQL语句建立第2章习题11中的三个表。（1） CREATE TABLE S ( SNO VARCHAR(9) PRIMARY KEY, SNAME VARCHAR(20), AGE SMALLINT, SEX CHAR(2) );,P68 第3题,（2）CREATE TABLE C ( CNO VARCHAR(4) PRIMARY KEY, CNAME VARCHAR(40) , TEACHER VARCHAR(20) );,P68 第3题,（3）CREATE TABLE SC ( SNO VARCHAR(9) , CNO VARCHAR(4) , SCORE INT, PRIMARY KEY (SNO,CNO), FOREIGN KEY(SNO) REFERENCES S(SNO), FOREIGN KEY(CNO) REFERENCES C(CNO), );,
