1、习题习题P19.P19.第第9 9题题职工职工部门部门产品产品属于属于销售销售n n1 1m mn n经理经理价格价格职工编号职工编号产品名产品名产品编号产品编号职工姓名职工姓名住址住址部门编号部门编号部门名称部门名称型号型号制造商制造商1 1所在城市所在城市名称名称制造商编号制造商编号制造制造n nP19.P19.第第1010题题学生学生系系教师教师属于属于1 1属于属于2 2n n1 1m mn n性别性别学号学号姓名姓名教师号教师号姓名姓名性别性别系号系号系名系名课程课程课程名课程名课程号课程号n n选修选修n n1 1m m年龄年龄讲授讲授职称职称P35.P35.第第1111题题 ；P
2、68.P68.第第4 4题题(1)检索老师检索老师“李诚李诚”所授课程的课程号和课程所授课程的课程号和课程名。名。SELECT CNO,CNAMEFROM CWHERE TEACHER=李诚李诚;)(,CTEACHERCNAMECNO李诚P35.P35.第第1111题题 ；P68.P68.第第4 4题题(2)检索年龄大于检索年龄大于20岁的男学生的学号和姓名。岁的男学生的学号和姓名。SELECT SNO,SNAMEFROM SWHERE AGE20 AND SEX=男男;)(20SSEXAGESNAMESNO男，P35.P35.第第1111题题 ；P68.P68.第第4 4题题(3)检索考试成
3、绩不及格的学生姓名和课程名。检索考试成绩不及格的学生姓名和课程名。SELECT SNAME,CNAMEFROM S,SC,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SCORE=2 ;P35.P35.第第1111题题 ；P68.P68.第第4 4题题(9)检索全部学生都选修的课程的课程号与课程检索全部学生都选修的课程的课程号与课程名。名。)SC(CNOSNO,CNAMECNOS)(SNO)CSC(,SNOCNAMECNOS)(SNO)CSELECT CNO,CNAME FROM C WHERE NOT EXISTS (SELECT *FROM S WHER
4、E NOT EXISTS (SELECT*FROM SC WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO)P35.P35.第第1111题题 ；P68.P68.第第4 4题题(10)检索选修课程包含老师检索选修课程包含老师“李诚李诚”所授全部所授全部课程的学生学号。课程的学生学号。C)()SC(,李诚TEACHERCNOCNOSNOSELECT SNO FROM S WHERE NOT EXISTS (SELECT *FROM C WHERE TEACHER=李诚李诚 AND NOT EXISTS (SELECT*FROM SC WHERE S.SNO=SC.SNO AN
5、D C.CNO=SC.CNO)P68 P68 第第6 6题题针对第针对第2章习题章习题11题中的三个关系：题中的三个关系：学生关系学生关系S(学号学号SNO，姓名，姓名SNAME，年龄，年龄AGE，性别，性别SEX)学生选课关系学生选课关系SC(学号学号SNO，课程号，课程号CNO，成绩，成绩SCORE)课程关系课程关系C(课程号课程号CNO，课程名，课程名CNAME，任课教师，任课教师TEACHER)试用试用SQL语句完成以下操作：语句完成以下操作：P68 P68 第第6 6题题(1)检索尚未安排任课教师的课程号和课程名。检索尚未安排任课教师的课程号和课程名。SELECT CNO,CNAME
6、FROM CWHERE TEACHER IS NULL;P68 P68 第第6 6题题(2)检索年龄在检索年龄在1922岁之间的学生的基本信息。岁之间的学生的基本信息。SELECT *FROM SWHERE AGE BETWEEN 19 AND 22;P68 P68 第第6 6题题(3)检索每门课程的平均成绩，结果按平均成绩检索每门课程的平均成绩，结果按平均成绩降序排列。降序排列。SELECT CNO,AVG(SCORE)FROM SCGROUP BY CNOORDER BY AVG(SCORE)DESCP68 P68 第第6 6题题(4)检索至少检索至少15个同学选修的课程号和课程名。个同学
7、选修的课程号和课程名。SELECT CNO,CNAMEFROM CWHERE CNO IN (SELECT CNO FROM SC GROUP BY CNO HAVING COUNT(SNO)=15)P68 P68 第第6 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 CN
8、O=C01)ORDER BY SCORE DESCP68 P68 第第6 6题题(6)向关系向关系C中插一个新元组中插一个新元组(C08，数据结数据结构构，高明高明)。INSERT INTO CVALUES(C08 ，数据结构数据结构，高明高明);P68 P68 第第6 6题题(7)删除删除SC中尚无成绩的选课元组。中尚无成绩的选课元组。DELETE FROM SCWHERE SCORE IS NULL;P68 P68 第第6 6题题(8)把选修把选修“数据库原理数据库原理”且不及格的学生成绩且不及格的学生成绩全改为全改为60分。分。UPDATE SCSET SCORE=60WHERE SCO
9、RE=30 P68 P68 第第6 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)90P36 P36 第第1212题；题；P68.P68.第第5 5题题已知四个关系：已知四个关系：(1)供应商关系供应商关系S(供应商编号供应商编号SNO，供应
10、商名称，供应商名称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零件的供应商编号。零件的供应商
11、编号。(2)检索北京供应商的编号和姓名。检索北京供应商的编号和姓名。(3)检索供应工程检索供应工程J01红色零件的供应商编号。红色零件的供应商编号。(4)检索供应商检索供应商S02供应工程供应工程J02的零件编号、零件名称的零件编号、零件名称和数量。和数量。(5)检索使用检索使用“上海宏光上海宏光”供应零件的工程项目编号和供应零件的工程项目编号和工程项目名称。工程项目名称。(6)检索供应红色零件的供应商名称。检索供应红色零件的供应商名称。(7)检索没有使用上海供应商生产的红色零件的工程编检索没有使用上海供应商生产的红色零件的工程编号。号。(8)检索提供全部零件的供应商名。检索提供全部零件的供应
12、商名。(9)检索至少用了检索至少用了S01供应商所供应的全部零件的工程编供应商所供应的全部零件的工程编号。号。(1)检索供应工程检索供应工程J01零件的供应商编号。零件的供应商编号。SELECT DISTINCT SNO FROM SPJWHERE JNO=J01;(SPJ)(01JJNOSNO(2)检索北京供应商的编号和姓名。检索北京供应商的编号和姓名。SELECT SNO,SNFROM SWHERE CITY=北京北京;(S)(,北京CITYSNSNO(3)检索供应工程检索供应工程J01红色零件的供应商编号。红色零件的供应商编号。SPJ)(P)(01红COLORJJNOSNO(SPJ)(P
13、)(01JJNOCOLORSNO红SPJ)(P(01红COLORJJNOSNOP)(SPJ)(01JJNOCOLORSNO红(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的零件编号、零的零件编号、零件名称和数量。件名
14、称和数量。SELECT SPJ.PNO,PN,QTYFROM SPJ,PWHERE SPJ.PNO=P.PNO AND SNO=S02 AND JNO=J02;P)(SPJ)(0202,JJNOSSNOQTYPNPNO(5)检索使用检索使用“上海宏光上海宏光”供应零件的工程项目供应零件的工程项目编号和工程项目名称。编号和工程项目名称。SELECT J.JNO,JNFROM S,SPJ,JWHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND SN=上海宏光上海宏光;J)SPJ (S)(,上海宏光SNJNJNO(6)检索供应红色零件的供应商名称。检索供应红色零件的供应
15、商名称。SELECT SNFROM S,SPJ,PWHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND COLOR=红红;S)SPJ (P)(红COLORSN(7)检索没有使用上海供应商生产的红色零件的检索没有使用上海供应商生产的红色零件的工程编号。工程编号。S)SPJ (P()(红上海COLORCITYJNOJNOJ(7)检索没有使用上海供应商生产的红色零件的检索没有使用上海供应商生产的红色零件的工程编号。工程编号。SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM SPJ,S,P WHERE S.SNO=SP
16、J.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
17、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)检索提供全部零件的供应商名。检索提供全部零件的供应商名。S)()(,PSPJPNOPNOSNOSN(8)检索提供全部零件的供应商名。检索提供全部零件的供应商名。SELECT SN FROM S WHERE NOT EXISTS （SELECT *FROM P WHERE NOT E
18、XISTS (SELECT*FROM SPJ WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)(9)检索至少用了检索至少用了S01供应商所供应的全部零件供应商所供应的全部零件的工程编号。的工程编号。)()(0101,SPJSPJSSNOPNOSSNOPNOJNO(9)检索至少用了检索至少用了S01供应商所供应的全部零件供应商所供应的全部零件的工程编号。的工程编号。SELECT DISTINCT JNOFROM SPJ XWHERE NOT EXISTS (SELECT*FROM SPJ Y WHERE Y.SNO=S01 AND NOT EXISTS (SELECT
19、*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),PN
20、O CHAR(4),QTY INT);P68 P68 第第3 3题题用用SQL语句建立第语句建立第2章习题章习题11中的三个表。中的三个表。（1）CREATE TABLE S (SNO VARCHAR(9)PRIMARY KEY,SNAME VARCHAR(20),AGE SMALLINT,SEX CHAR(2);P68 P68 第第3 3题题（2）CREATE TABLE C (CNO VARCHAR(4)PRIMARY KEY,CNAME VARCHAR(40),TEACHER VARCHAR(20);P68 P68 第第3 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),);
