1、42 对于教学数据库的三个基本表S（SNO,SNAME,AGE,SEX,SDEPT）SC（SNO,CNO,GRADE）C（CNO,CNAME，CDEPT,TNAME ）试用 SQL 的查询语句表达下列查询：检索 LIU 老师所授课程的课程号和课程名。检索年龄大于 23 岁的男学生的学号和姓名。检索至少选修 LIU 老师所授课程中一门课程的女学生姓名。检索 WANG 同学不学的课程的课程号。检索至少选修两门课程的学生学号。检索全部学生都选修的课程的课程号与课程名。检索选修课程包含 LIU 老师所授( 全部)课程的学生学号。参考答案： SELECT CNO，CNAMEFROM CWHERE TNA
2、MELIU ; SELECT SNO，SNAMEFROM S WHERE AGE23 AND SEXM; SELECT SNAME （联接查询方式）FROM S，SC，CWHERE S.SNO=SC.SNO AND SC.CNO=C.CNOAND SEX=F AND TNAME=LIU;或：SELECT SNAME (嵌套查询方式)FROM S WHERE SEXFAND SNO IN(SELECT SNO FROM SCWHERE CNO IN (SELECT CNO FROM C WHERE TNAME=LIU);或：SELECT SNAME (存在量词方式)FROM SWHERE SEX
3、FAND EXISTS（SELECT FROM SCWHERE SC.SNO=S.SNOAND EXISTS(SELECT *FROM CWHERE C.CNO=SC.CNOAND TNAME=LIU); SELECT CNOFROM CWHERE NOT EXISTS(SELECT *FROM S,SCWHERE S.SNO=SC.SNO AND SC.CNO=C.CNOAND SNAME=WANG);或：SELECT CNOFROM C WHERE CNO NOT IN (SELECT SC.CNO FROM S,SCWHERE S.SNO=SC.SNO AND SNAME=WANG);或
4、：SELECT CNOFROM CWHERE CNO NOT IN(SELECT CNOFROM SCWHERE SNO IN(SELECT SNOFROM SWHERE SNAME=WANG); SELECT DISTINCT X.SNOFROM SC AS X,SC AS YWHERE X.SNO=Y.SNO AND X.CNO=2; SELECT CNO,CNAMEFROM CWHERE NOT EXISTS(SELECT *FROM SWHERE NOT EXISTS(SELECT * FORM SCWHERE SC.SNO=S.SNO AND SC.CNO=C.CNO); SELEC
5、T DISTINCT SNOFROM SC AS XWHERE NOT EXISTS(SELECT *FORM CWHERE TNAME=LIUAND NOT EXISTS(SELECT *FROM SC AS YWHERE Y.SNO=X.SNO AND Y.CNO=C.CNO);43 设有两个基本表 R（A，B ，C）和 S（D ，E ，F ） ，试用 SQL 查询语句表达下列关系代数表达式： SELECT A FROM R; SELECT * FROM R WHERE B=17; SELECT * FROM R,S; SELECT A,F FROM R,S WHERE C=D;44 设有
6、两个基本表 R（A ，B ，C）和 S（A ，B，C） ，试用 SQL 查询语句表达下列关系代数表达式：1.(SELECT * FROM R)UNION(SELECT * FROM S);2.(SELECT * FROM R)INTERSECT(SELECT * FROM S);3.(SELECT * FROM R)EXCEPT(SELECT * FROM S);4.(SELECT A,B FROM R)NATURAL INNER JOIN(SELECT B,C FROM S);或：SELECT R.A, R.B, S.CFROM R,SWHERE R.B=S.B4.6 试用 SQL 查询语句
7、表达下列对教学数据库中三个基本表 S、SC 、C 的查询：1、统计有学生选修的课程门数。2、求选修 C4 课程的学生的平均年龄。3、求 LIU 老师所授课程的每门课程的学生平均成绩。4、统计每门课程的学生选修人数（超过 10 人的课程才统计） 。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。5、检索学号比 WANG 同学大，而年龄比他小的学生姓名。6、检索姓名以 WANG 打头的所有学生的姓名和年龄。7、在 SC 中检索成绩为空值的学生学号和课程号。8、求年龄大于女同学平均年龄的男学生姓名和年龄。9、求年龄大于所有女同学年龄的男学生姓名和年龄。参考答案：1、
8、SELECT COUNT（DISTINCT CNO）FROM SC ；2、 SELECT AVG（AGE ）FROM S，SCWHERE S.SNO=SC.SNO AND CNO=C4;3、 SELECT SC.CNO, CNAME, AVG(GRADE)FROM SC,CWHERE SC.CNO=C.CNO AND TNAME=LIUGROUP BY SC.CNO;4、 SELECT CNO,COUNT(SNO)FROM SC GROUP BY CNOHAVING COUNT(*)10ORDER BY 2 DESC, 1;5、 SELECT X.SNAMEFROM S AS X,S AS Y
9、WHERE Y.SNAME=WANGAND X.SNOY.SNO AND X.AGE(SELECT SNOFROM SWHERE SNAME=WANG)AND AGE(SELECT AGEFROM SWHERE SNAME=WANG);(此处要求学生姓名不重复 )6、 SELECT SNAME ，AGEFROM SWHERE SNAME LIKE WANG% 7、 SELECT SNO，CNOFROM SCWHERE GRADE IS NULL；8、 SELECT SNAME ，AGEFROM SWHERE SEXMAND AGE （ SELECT AVG（AGE）FROM SWHERE SE
10、XF ）9、 SELECT SNAME ，AGEFROM SWHERE SEXMAND AGE ALL（SELECT AGE FROM SWHERE SEXF ） ；或：把上述红色部分改为：AGE（SELECT MAX（AGE ）47 试用 SQL 更新语句表达对教学数据库中三个基本表 S、SC 、 C 的各个更新操作：1、往基本表 S 中插人一个学生元组（S9 ， WU，18） 。2、在基本表 S 中检索每一门课程成绩都大于等于 80 分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（SNO，SNAME ， SEX） 。3、在基本表 SC 中删除尚无成绩的选课元
11、组。4、把 WANG 同学的学习选课和成绩全部删去。5、把选修 MATHS 课不及格的成绩全改为空值。6、把低于总平均成绩的女同学成绩提高 5。7、在基本表 SC 中修改 C4 课程的成绩，若成绩小于等于 75 分时提高 5，若成绩大于 75 分时提高 4（用两个 UPDATE 语句实现） 。参考答案：1、 INSERT INTO S（SNO，SNAME ，AGE ）VALUES（S9 ， WU，18） ；2、 INSERT INTO STUDENTSELECT SNO，SNAME ，SEXFROM SWHERE 80 ALL（SELECT GRADEFROM SCWHERE SNO S.SN
12、O） ；3、 DELETE FROM SCWHERE GRADE IS NULL;4、 DELETE FROM SCWHERE SNO IN （SELECT SNOFROM SWHERE SNAME WANG ） ；5、 UPDATE SCSET GRADENULLWHERE GRADE 60AND CNO IN（SELECT CNOFROM CWHERE CNAMEMATHS ） ；6、 UPDATE SCSET GRADEGRADE1.05WHERE SNO IN （SELECT SNO FROM SWHERE SEXF ）AND GRADE（SELECT AVG（GRADE）FROM S
13、C ） ；7、 UPDATE SCSET GRADEGRADE1.04WHERE CNO C4 AND GRADE75；UPDATE SC SET GRADEGRADE1.05WHERE CNO C4 AND GRADE75；注：这两个语句顺序不能颠倒。48 假设某“仓库管理”关系模型有下列五个关系模式：零件 PART（PNO ，PNAME，COLOR，WEIGHT）项目 PROJECT（JNO，JNAME，DATE）供应商 SUPPLIER（SNO，SNAME ，SADDR）供应 P_P（JNO，PNO，TOTAL）采购 P_S（PNO，SNO，QUANTITY）试用 SQL DDL 语句定
14、义上述五个基本表，并说明主键和外键。试将 PROJECT、P_P、PART 三个基本表的自然联接定义为一个视图 VIEW1，PART,P_S、SUPPLIER 三个基本表的自然联接定义为一个视图 VIEW2。试在上述两个视图的基础上进行数据查询：i 检索上海的供应商所供应的零件的编号和名字。ii检索项目 J4 所用零件的供应商编号和名字。参考答案： CREATE TABLE PART(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO);CREATE TABLE PROJECT(JN
15、O CHAR(6),JNAME CHAR(12) NOT NULL,DATE DATE,PRIMARY KEY(JNO);CREATE TABLE SUPPLIER(SNO CHAR(8),SNAME CHAR(12) NOT NULL,SADDR VARCHAR(30),PRIMARY KEY(SNO);CREATE TABLE P_P(JNO CHAR(6),PNO CHAR(6),TOTAL INTEGER,PRIMARY KEY(JNO,PNO)FOREIGN KEY(JNO) REFERENCES PROJECT(JNO),FOREIGN KEY(PNO) REFERENCES PA
16、RT(PNO);CREATE TABLE P_S(PNO CHAR(6),SNO CHAR(6),QUANTITY INTEGER,PRIMARY KEY(PNO,SNO)FOREIGN KEY(PNO) REFERENCES PART(PNO),FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO); CREATE VIEW VIEW1AS SELECT A.JNO,JNAME,DATE,C.PNO,PNAME,COLOR,WEIGHT,TOTALFROM PROJECT A, P_P B, PART CWHERE A.JNO=B.JNO AND B.PNO=C.
17、PNO;CREATE VIEW VIEW2AS SELECT A.PNO, PNAME,COLOR,WEIGHT,C.SNO,SNAME,SADDR,QUANTITYFROM PART A, P_S B,SUPPLIER CWHERE A.PNO=B.PNO AND B.SNO=C.SNO; i. SELECT PNO,PNAMEFROM VIEW2WHERE SADDR LIKE 上海%;ii. SELECT SNO,SNAMEFROM VIEW1,VIEW2WHERE VIEW1.PNO=VIEW2.PNO AND JNO=J4;49 对于教学数据库中基本表 SC，已建立下列视图：CREA
18、TE VIEW SGRADE（S，C_NUM，AVG GRADE）AS SELECT SNO，COUNT（CNO ） ，AVG（GRADE）FROM SCGROUP BY SNO；试判断下列查询和更新是否允许执行。若允许，写出转换到基本表 SC 上的相应操作：SELECT * FROM S_GRADESELECT SNO，C_NUMRROM S_GRADEWHERE AVG_GRADE80；SELECT SNO，AVG GRADEFROM S_GRADE WHERE C_NUM （SELECT C_NUMFROM S_GRADEWHERE SNOS4 ）; UPDATE S_GRADESET
19、C_NUMC_NUM 1WRERE SNOS4 ；DELETE FROM S_GRADEWHERE C_NUM4； 参考答案：允许查询。相应操作如下：SELECT SNO，COUNT（CNO ） AS CNUM，AVG（GRADE ） AS AVG GRADEFROM SCGROUP BY SNO；允许查询。相应操作如下：SELECT SNO，COUNT（CNO） AS C_NUMRROM SCGROUP BY SNOHAVING AVG（GRADE）80；允许查询。相应操作如下：SELECT SNO，AVG（GRADE ） AS AVGGRADEFROM SC GROUP BY SNOHAVING COUNT（CNO ） （SELECT COUNT（CNO）FROM SCGROUP BY SNOHAVING SNOS4 ）; 不允许。CNUMJ 是对 SC 中的学生选修门数进行统计，在未更改 SC 表时，要在视图 SGRADE 中更改门数，是不可能的。不允许。在 SGRADE 中删除选修门数在 4 门以上的学生元组，势必造成 SC 中这些学生学习元组的删除，这未必是用户的原意，因此使用分组和聚合操作的视图，不允许用户执行更新操作。
