数据库系统教程1-7章课后答案（施伯乐）（第二版）(6)

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, SEX CHAR(1), ECITY CHAR(20), PRIMARY KEY(E#))； CREATE TABLE COMP

( C# CHAR(4) NOT NULL,

CNAME CHAR(20) NOT NULL, CITY CHAR(20),

（2003/9/21） （GJ-DA） （共2页） 目录--26

PRIMARY KEY(C#))；

CREATE TABLE WORKS

( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#),

FOREIGN KEY(E#) REFERENCES EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#))；

② SELECT E#, ENAME

FROM EMP

WHERE AGE>50 AND SEX='M'；

③ SELECT EMP.E#, ENAME

FROM EMP, WORKS

WHERE EMP.E#=WORKS.E# AND SALARY>1000； ④ SELECT A.E#, A.ENAME

FROM EMP A, WORKS B, WORKS C WHERE A.E#=B.E# AND B.E#=C.E#

AND B.C#='C4' AND C.C#='C8'；

⑤ SELECT A.E#, A.ENAME

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C#

AND CNAME='联华公司' AND SALARY>1000 AND SEX='M'；

⑥ SELECT E#, COUNT(C#) AS NUM, SUM(SALARY) AS SUM_SALARY

FROM WORKS GROUP BY E#； ⑦ SELECT X.E#

FROM WORKS X WHERE NOT EXISTS

(SELECT *

FROM WORKS Y

WHERE E#='E6'

AND NOT EXISTS

(SELECT *

FROM WORKS Z WHERE Z.E#=X.E# AND Z.C#=Y.C#))；

⑧ SELECT A.E#, A.ENAME

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C#

（2003/9/21） （GJ-DA）2页）--27

（共 目录 AND CNAME='联华公司'

AND SALARY<(SELECT AVG(SALARY)

FROM WORKS, COMP

WHERE WORKS.C#=COMP.C#

AND CNAME='联华公司'）；

⑨ UPDATE WORKS

SET SALARY=SALARY+100

WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50)； ⑩ DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)； DELETE FROM EMP WHERE AGE>60；

3.14 对第3.13题中的关系建立一个有关女职工信息的视图EMP_WOMAN，属性包括（E#，ENAME，

C#，CNAME，SALARY）。

然后对视图EMP_WOMAN操作，检索每一位女职工的工资总数。（假设每个职工可在多个公司兼职）

解： CREATE VIEW EMP_WOMAN

AS SELECT A.E#, A.ENAME, C.C#, CNAME, SALARY

FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C#

AND SEX='F'；

SELECT E#，SUM(SALARY) FROM EMP_WOMAN

GROUP BY E#；

3.15 在第1章中提到的仓库管理数据库中有五个基本表: 零件 PART(P#，PNAME，COLOR，WEIGHT) 项目 PROJECT（J#，JNAME，DATE） 供应商 SUPPLIER（S#，SNAME，SADDR） 供应 P_P(J#，P#，TOTAL) 采购 P_S(P#，S#，QUANTITY)

① 试用SQL DDL语句定义上述五个基本表，需说明主键和外键。

② 试将PROJECT、P_P、PART三个基本表的联接定义为一个视图VIEW1,将PART、P_S、SUPPLIER三个基本表的联接定义为一个视图VIEW2。

③ 试在上述两个视图的基础上进行查询操作： a）检索上海的供应商所供应的零件的编号和名称。 b）检索项目J4所用零件的供应商的编号和名称。 解：① CREATE TABLE PART

（P# CHAR(6),

PNAME CHAR(10) NOT NULL, COLOR CHAR(6), WEIGHT FLOAT(6), PRIMARY KEY(P#));

CREATE TABLE PROJECT

（2003/9/21） （GJ-DA） （共2页） 目录--28

（J# CHAR(6),

JNAME CHAR(12) NOT NULL, DATE DATE,

PRIMARY KEY(J#)); CREATE TABLE SUPPLIER

（S# CHAR(8),

SNAME CHAR(12) NOT NULL, SADDR VARCHAR(30), PRIMARY KEY(S#));

CREATE TABLE P_P

（J# CHAR(6), P# CHAR(6), TOTAL INTEGER,

PRIMARY KEY (J#, P#)

FOREIGN KEY(J#) REFERENCES PROJECT(J#), FOREIGN KEY(P#) REFERENCES PART(P#)); CREATE TABLE P_S

（P# CHAR(6), S# CHAR(8), QUANTITY INTEGER, PRIMARY KEY (P#, S#)

FOREIGN KEY(P#) REFERENCES PART(P#),

FOREIGN KEY(S#) REFERENCES SUPPLIER(S#));

② CREATE VIEW VIEW1

AS SELECT A.J#, JNAME, DATE, C.P#, PNAME, COLOR,

WEIGHT, TOTAL

FROM PROJECT A, P_P B, PART C WHERE A.J#=B.J# AND B.P#=C.P#;

CREATE VIEW VIEW2

AS SELECT A.P#, PNAME, COLOR, WEIGHT, C.S#, SNAME,

SADDR, QUANTITY

FROM PART A, P_S B, SUPPLIER C WHERE A.P#=B.P# AND B.S#=C.S#;

③ a) SELECT P#, PNAME

FROM VIEW2

WHERE SADDR LIKE ’上海%’; b) SELECT S#, SNAME FROM VIEW1, VIEW2

WHERE VIEW1.P#=VIEW2.P# AND J#=’J4’;

3.16 对于3.2题的教学数据库中基本表SC，建立一个视图： CREATE VIEW S_GRADE(S#，C_NUM，AVG_GRADE) AS SELECT S#，COUNT(C#)，AVG（GRADE） FROM SC

GROUP BY S#；

试判断下列查询和更新操作是否允许执行。如允许，写出转换到基本表SC上的相应操作。

（2003/9/21） （GJ-DA） （共2页） 目录--29

① SELECT * …… 此处隐藏：3259字，全部文档内容请下载后查看。喜欢就下载吧 ……