第 10 页
SET GRADE=GRADE*1.04
WHERE C#=’4’AND GRADE>75; UPDATE SC
SET GRADE=GRADE*1.05
WHERE C#=’4’AND GRADE<=75;
注意：这两个UPDATE语句的顺序不能颠倒。
4-5 假设某“仓库管理”关系模型有下列五个关系模式： 零件PART(PNO,PNAME,COLOR,WEIGHT) 项目PROJECT(JNO,JNAME,JDATE)
供应商SUPPLIER(SNO,SNAME,SADDR) 供应P_P(JNO,PNO,TOTAL)
采购P_S(PNO,SNO,QUANTITY)
⑴ 试用SQL DDL语句定义上述五个基本表，并说明主键和外键。
⑵ 试将PROJECT、P_P、PART三个基本表的自然联接定义为一个视图VIEW1， PART、P_S、SUPPLIER三个基本表的自然联接定义为一个视图VIEW2。 ⑶ 试在上述两个视图的基础上进行数据查询： ① 检索上海的供应商所供应的零件的编号和名字。
② 检索项目J4所用零件的供应商编号和名字。 解：
⑴ CREATE TABLE PART
(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO));
CREATE TABLE PROJECT
(JNO 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 PART(PNO)); CREATE TABLE P_S
(PNO CHAR(6),SNO CHAR(8),QUANTITY INTEGER,PRIMARY KEY(PNO,SNO) FOREIGN KEY(PNO) REFERENCES PART(PNO),
FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO));
⑵ CREATE VIEW VIEWl
AS SELECT A.JNO,JNAME,DATE,C.PNO,PNAME,COLOR,WEIGHT,TOTAL FROM PROJECT A,P_P B，PART C
WHERE A.JNO=B.JNO AND B.PNO=C.PNO; CREATE VIEW VIEW2
AS SELECT A.PNO,PNAME,COLOR,WEIGHT,C.SNO,SNAME,SADDR,QUANTITY FROM PART A,P_S B,SUPPLIER C
WHERE A.PNO=B.PNO AND B.SNO=C.SNO; ⑶①
第 11 页
SELECT PNO,PNAME FROM VIEW2
WHERE SADDR LIKE ’上海％’; ② SELECT SNO,SNAME FROM VIEWl,VIEW2
WHERE VIEWl.PNO=VIEW2.PNO AND JNO=’J4’; 4-6 对于教务管理数据库中基本表SC，已建立下列视图： CREATE VIEW S_GRADE(SNO,C_NUM,AVG_GRADE) AS SELECT SNO,COUNT(CNO),AVG(GRADE) FROM SC
GROUP BY SNO;
试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC上的相应操 作： ⑴ SELECT * FROM S_GRADE ⑵ SELECT SNO,C_NUM FROM S_GRADE
WHERE AVG_GRADE>80; ⑶ SELECT SNO,AVG_GRADE FROM S_GRADE
WHERE C_NUM>(SELECT C_NUM FROM S_GRADE
SNO=’200912121’); ⑷ UPDATE S_GRADE SET C_NUM=C_NUM+1
WHERE SNO=’200915122’ ⑸ DELETE FROM S_GRADE WHERE C_NUM>4;
答：⑴ 允许查询。相应的操作如下：
SELECT SNO,COUNT(CNO) AS C_NUM,AVG(GRADE) AS AVG_GRADE FROM SC
GROUP BY SNO;
⑵ 允许查询。相应的操作如下：
SELECT SNO,COUNT(CNO)AS C_NUM FROM SC
GROUP BY SNO
HAVING AVG(GRADE)>80;
⑶ 允许查询。相应的操作如下：
SELECT SNO,AVG(GRADE) AS AVG_GRADE FROM SC
GROUP BY SNO
HAVING COUNT(CNO)>(SELECT COUNT(CNO) FROM SC
GROUP BY SNO
HAVING SNO=’200912121’);
第 12 页