作业3－点评

基本情况总结 题目：第三章1～5题 难：4.4，4.5，5.4，5.6，5.7，5.10 中：3，4.1，4.2，4.3，5.5，5.8，5.9，5.11 易：1，2，5.1，5.2，5.3， P.S. dbhw2的答案已经上传至FTP，请大家自行下载更 正答案

概念性题目（第1，2题） 第1题 SQL语言5个特点 基本回答正确 第2题 SQL定义功能 包括CREATE ，ALTER和DROP

第3题 考核要点 共同问题 理解题设中隐含的完整性约束要求 考查DDL的运用 没有声明主码、外码 缺少必要的check 非空属性没有not null约束 声明了主码后，多余地添加unique，not null约束等 不知道如何声明包含多个属性的主码

第3题（续） 例1: 例2 create table P ( PNO char(5) primary key, PNAME char(10) not null, COLOR char(10), WEIGHT int check(weight > 0 ) ); 例2 create table SPJ ( SNO char(5) not null references S(SNO) , PNO char(5) not null references P(PNO), JNO char(5) not null references J(JNO), QTY int check(QTY > 0) primary key (SNO,PNO,JNO) 声明primary key之后还有not null或unique

第4、5题 考核要点 共同问题 理解题设的语义 SQL语句操作 没有认真审题，所答非所问 如5.6题，题设要求工程名称，而很多同学回答的却是工程号 对谓词any，all，exists理解不够深刻 缺乏理论知识 没有从语义来分析 何时需要distinct？

4.1 求供应工程J1零件的供应商号码 需要distinct 参考答案： select distinct SNO from SPJ where JNO = ‘J1’ 在这里需要去重；因为指定一个工程，一个供应商可能提供多种零件

4.2 求供应工程J1零件P1的供应商号码 需要distinct吗？ 参考答案： select SNO from SPJ where (JNO = 'J1'and PNO = 'P1'); 这里无需去重； 因为(SNO,JNO,PNO)是主码，确定了JNO和PNO，SNO只能是唯一的了

4.3 求供应工程J1零件为红色的供应商号码SNO 参考答案： select SPJ.SNO from SPJ , P where SPJ.JNO = 'J1‘ and P.PNO = SPJ.PNO and P.COLOR = '红';

4.4* select JNO from J where not exists (select * from S, P, SPJ 求没有使用天津供应商生产的红色零件的工程号 要包括没有使用任何零件的工程 不能从SPJ表里面找JNO，而是从J表中找 参考答案 select JNO from J where not exists (select * from S, P, SPJ where S.SNO=SPJ.SNO and S.CITY='天津' and SPJ.PNO=P.PNO and P.COLOR='红' and SPJ.JNO=J.JNO ) ; 如果没有使用任何零件，那么该条件不会被满足，嵌套的select不会得到结果，not exists返回true。

4.5* 变换后语义： 求至少使用了供应商S1所供应的全部零件的工程号 查询解释为： 形式化表示： 查询工程号为x的工程，对所有的零件y，只要S1供应商提供零件y， 则工程x就使用了y。 形式化表示： 用P表示谓词“供应商S1提供了零件y” 用q表示谓词“工程x使用了零件y” 则上述查询为: (∀y) p → q (∀y)p →q ≡ ¬ ∃y(p∧¬q) 变换后语义： 不存在这样的零件y，供应商S1生产了y，而工程x没有使 用。

4.5参考答案 select JNO from J where not exists ( select * from P 参考答案1 参考答案2 select JNO from J where not exists ( select * from P where PNO in ( select PNO from SPJ where SNO='S1') and not exists ( select * from SPJ where J.JNO=JNO and PNO=P.PNO) ) select distinct JNO from SPJ SPJX where not exists ( select * from SPJ SPJY where SPJY.SNO = ‘S1' and not exists ( select * from SPJ SPHZ where SPJX.JNO = SPJZ.JNO and SPJY.PNO = SPJZ.PNO )

5.4 找出工程项目J2使用的各种零件的名称及其数量 典型错误： 参考答案 应该是PNAME，有些同学写的是零件号PNO 没有对同类的零件进行group by ，再对重量QTY求和 参考答案 select PNAME , sum(QTY ) from P ,SPJ where SPJ.JNO = ‘J2’ and SPJ.PNO = P.PNO group by P.PNO, P.PNAME

5.6 找出使用上海产的零件的工程名称 主要问题： 应该是JNAME，而不是工程号码JNO 典型错误： select JNO from SPJ,S where SPJ.SNO = S.SNO and S.CITY = ‘上海’ select JNAME from J where JNO in ( select JNO where SPJ.SNO=S.SNO and CITY=’上海’ ) 参考答案1 参考答案2 select distinct J.JNAME from S, J, SPJ where SPJ.JNO = J.JNO and SPJ.SNO = S.SNO and S.CITY = ‘上海’

5.7 找出没有使用天津产的零件的工程号码 与4.4类似，需要包含没有使用任何零件的工程 select JNO from J where JNO not IN (select JNO from SPJ,S where SPJ.SNO = S.SNO and S.CITY = '天津' ) 参考答案1 参考答案2 select JNO from J where not exists ( select * from SPJ, S where SPJ.JNO = J.JNO and SPJ.SNO = S.SNO and S.CITY = ‘天津’)

5.10 从供应商关系中删除S2的纪录，并从供应情况关系中 删除相应的纪录 应该先删除SPJ表中的相应的记录，再删除S表中的相 应的记录！ 或： alter table SPJ add foreign key(SNO) references S(SNO) on delete cascade delete from S where SNO=’S2’

总结 成绩分布图 优秀作业：杨涛，王伟，于彦雷 ，张小雪 未交作业：蔡荀，李京哲 ，李三川，徐源

Tips 写SQL语句的时候注意格式工整，让人能够清晰看出 各个子句，最好不要所有的字句都写在一起 能用连接实现的就不要用嵌套查询，因为连接算法有 很多优化措施，这点在实验4中大家可以通过查询执行 计划来体会 书写工整很重要，不工整的书写会导致阅读者不明白 写的是什么，从而无法做出准确判断