FROM S
WHERE SNAME=’WANG’) AND AGE FROM S WHERE SNAME=’WANG’)； ⑥ SELECT S#, C# FROM SC WHERE GRADE IS NULL； ⑦ SELECT SNAME, AGE FROM S WHERE SNAME LIKE ’L%’； ⑧ SELECT SNAME, AGE FROM S WHERE SEX=’M’ AND AGE>(SELECT AVG(AGE) FROM S WHERE SEX=’F’)； ⑨ SELECT SNAME, AGE FROM S WHERE SEX=’M’ AND AGE>ALL(SELECT AGE FROM S WHERE SEX=’F’)； 3.8 对于下面的关系R和S，试求出下列各种联接操作的执行结果： ① R NATURAL INNER JOIN S ② R NATURAL RIGHT OUTER JOIN S ③ R RIGHT OUTER JOIN S USING（C） ④ R INNER JOIN S ⑤ R FULL OUTER JOIN S ON false R A B C S B C D a1 b1 c1 b1 c1 d1 a2 b2 c2 b2 c2 d2 a3 b3 c3 b4 c4 d4 解： ① A B C D ② A B C D ③ A R.B C S.B D b1 a1 b1 c1 d1 a1 b1 c1 d1 a1 b1 c1 d1 b2 a2 b2 c2 d2 a2 b2 c2 d2 a2 b2 c2 d2 b4 null b4 c4 d4 null null c4 d4 ④ A R.B R.C S.B S.C D ⑤ A R.B R.C S.B S.C D a1 b1 c1 b1 c1 d1 a1 b1 c1 null null null a1 b1 c1 b2 c2 d2 a2 b2 c2 null null null a1 b1 c1 b4 c4 d4 a3 b3 c3 null null null c1 a2 b2 c2 b1 c1 d1 null null null b1 d1 c2 a2 b2 c2 b2 c2 d2 null null null b2 d2 c4 a2 b2 c2 b4 c4 d4 null null null b4 d4 a3 b3 c3 b1 c1 d1 a3 b3 c3 b2 c2 d2 a3 b3 c3 b4 c4 d4 3.9 SQL2提供CASE表达式操作，这个操作类似于程序设计语言中的多分支选择结构，其句 法如下： CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …… WHEN 条件n THEN 结果n ELSE 结果m END 如果自上而下“条件i”首先被满足，那么这个操作返回值“结果i”（可以是某个表达式的值）；如果没有一个条件被满足，那么返回值“结果m”。 在基本表SC（S#，C#，GRADE）中，GRADE值是百分制。如果欲转换成“成绩等第”，则规则如下：若GRADE＜40则等第为F，若40≤GRADE＜60则等第为C，若60≤GRADE＜80则等第为B，若80≤GRADE则等第为A。试写出下列两个查询语句： ① 检索每个学生的学习成绩，成绩显示时以等第（SCORE）形式出现。 ② 检索每个等第的学生人次数。 解：① SELECT S#，C#，CASE WHEN GRADE >= 80 THEN 'A' WHEN GRADE >= 60 THEN 'B' WHEN GRADE >= 40 THEN 'C' ELSE 'F' END AS SCORE FROM SC； ② SELECT SCORE，COUNT（S#） FROM （SELECT S#，C#，CASE WHEN GRADE >= 80 THEN 'A' WHEN GRADE >= 60 THEN 'B' WHEN GRADE >= 40 THEN 'C' ELSE 'F' END FROM SC） AS RESULT（S#，C#，SCORE） GROUP BY SCORE； 3.10 用第3.9题给出的CASE操作在下列更新语句中完成SC表中的元组更新： ① 若课程号为C5则增加6分，若课程号为C8则增加10分，其他一律增加5分。 ② 若C4课程的成绩低于该门课平均成绩时，提高5%，否则提高4%。 解：① UPDATE SC SET GRADE = GRADE + CASE WHEN C# ='C5' THEN 6 WHEN C# ='C8' THEN 10 ELSE 5 END ； ② UPDATE SC SET GRADE = GRADE * CASE WHEN GRADE <（SELECT AVG（GRADE） FROM SC WHERE C# ='C4'） THEN 1.05 ELSE 1.04 END WHERE C# ='C4'； 3.11 设零件之间有组合联系，其关系模式如下： PART（P#，PNAME，SUBP#，TOTAL） 其属性表示零件编号、零件名称、所需子零件编号及数量。设临时关系W（P#，SUBP#）的属性分别表示零件编号、这种零件的直接或间接子零件编号。 ① 试写出表示关系W的规则。 ② 写出计算W的递归查询语句。 解：① W(x, y)←PART(x, g, y, h) W(x, y)←W(x, z)∧ W(z, y) ② WITH RECURSIVE W(P#, SUBP#) AS (SELECT P#, SUBP# FROM PART) UNION (SELECT W1.P#, W2.SUBP# FROM W AS W1, W AS W2 WHERE W1.SUBP#=W2.P#) SELECT * FROM W； 3.12 试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C的更新操作： ① 往关系C中插一个课程元组（'C8'，'VC++'，'BAO'）。 ② 检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）。 ③ 在SC中删除尚无成绩的选课元组。 ④ 把选修LIU老师课程的女同学选课元组全部删去。 ⑤ 把MATHS课不及格的成绩全改为60分。 ⑥ 把低于所有课程总平均成绩的女同学成绩提高5％。 ⑦ 在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5％，若成绩大于70 分时提高4％（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。 ⑧ 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。 解：① INSERT INTO C VALUES('C8'，'VC++'，'BAO')； ② INSERT INTO FACULTY(TNAME) SELECT DISTINCT TEACHER FROM (SELECT TEACHER, C.C#, AVG(GRADE) FROM S, SC WHERE SC.C#=C.C# GROUP BY TEACHER, C.C#) AS RESULT(TEACHER, C#, AVG_GRADE) AS X WHERE 80<=ALL(SELECT AVG_GRADE FROM RESULT AS Y WHERE Y.TEACHER=X.TEACHER)； ③ DELETE FROM SC WHERE GRADE IS NULL； ④ DELETE FROM SC WHERE S# IN(SELECT S# FROM S WHERE SEX='F') AND C# IN(SELECT C# FROM C WHERE TEACHER='LIU')； ⑤ UPDATE SC SET GRADE=60 WHERE GRADE<60 AND C# IN(SELECT C# FROM C WHERE CNAME='MATHS')； ⑥ UPDATE SC SET GRADE=GRADE*1.05 WHERE S# IN(SELECT S# FROM S WHERE SEX='F') AND GRADE<(SELECT AVG(GRADE) FROM SC)； ⑦ 用两个UPDATE语句实现： UPDATE SC SET GRADE=GRADE*1.04 WHERE C#='C4' AND GRADE>70； UPDATE SC SET GRADE=GRADE*1.05 WHERE C#='C4' AND GRADE<=70； （这两个UPDATE语句的顺序不能颠倒。） 用一个UPDATE语句实现： UPDATE SC SET GRADE=GRADE*CASE WHEN GRADE>70 THEN 1.04 ELSE 1.05 END WHERE C#='C4'； ⑧ UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE<(SELECT AVG(GRADE) FROM SC)； 3.13 设数据库中有三个关系： 职工表 EMP（E#，ENAME，AGE，SEX，ECITY）， 其属性分别表示职工工号、姓名、年龄、性别和籍贯。 工作表 WORKS（E#，C#，SALARY）， 其属性分别表示职工工号、工作的公司编号和工资。 公司表 COMP（C#，CNAME，CITY）， 其属性分别表示公司编号、公司名称和公司所在城市。 试用SQL语句写出下列操作： ① 用CREATE TABLE语句创建上述三个表，需指出主键和外键。 ② 检索超过50岁的男职工的工号和姓名。 ③ 假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。 ④ 假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。 ⑤ 检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。 ⑥ 假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。 ⑦ 工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。 ⑧ 检索联华公司中低于本公司平均工资的职工工号和姓名。 ⑨ 在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。 ⑩ 在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。 解：① CREATE TABLE EMP ( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT,