作业3－点评.ppt

1、作业3点评,基本情况总结,题目：第三章15题难：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约

2、束等 不知道如何声明包含多个属性的主码,第3题（续）,例1: create table P( PNO char(5) primary key, PNAME char(10) not null, COLOR char(10), WEIGHT int check(weight 0 );例2create 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

3、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,求供应

4、工程J1零件P1的供应商号码 需要distinct吗？参考答案：select SNO from SPJ where (JNO = J1and PNO = P1);,这里无需去重；因为(SNO,JNO,PNO)是主码，确定了JNO和PNO，SNO只能是唯一的了,4.3,求供应工程J1零件为红色的供应商号码SNO 参考答案：select SPJ.SNO from SPJ , P where SPJ.JNO = J1and P.PNO = SPJ.PNO and P.COLOR = 红;,4.4*,求没有使用天津供应商生产的红色零件的工程号 要包括没有使用任何零件的工程不能从SPJ表里面找JNO，而

5、是从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表示

6、谓词“供应商S1提供了零件y”用q表示谓词“工程x使用了零件y”则上述查询为: (y) p q(y)p q y(pq)变换后语义：不存在这样的零件y，供应商S1生产了y，而工程x没有使用。,4.5参考答案,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 JNOfrom SPJ

7、 SPJXwhere 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 ) ),参考答案1,参考答案2,5.4,找出工程项目J2使用的各种零件的名称及其数量 典型错误：应该是PNAME，有些同学写的是零件号PNO没有对同类的零件进行group by ，再对重量QTY求和参考答案select PNAME , sum(QTY )from P ,SPJwhere

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

9、SPJwhere SPJ.JNO = J.JNOand SPJ.SNO = S.SNOand S.CITY = 上海,5.7,找出没有使用天津产的零件的工程号码与4.4类似，需要包含没有使用任何零件的工程select JNOfrom Jwhere JNO not IN (select JNO from SPJ,S where SPJ.SNO = S.SNO and S.CITY = 天津),select JNOfrom Jwhere not exists ( select * from SPJ, S where SPJ.JNO = J.JNO and SPJ.SNO = S.SNO and S

10、.CITY = 天津),参考答案1,参考答案2,5.10,从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录 应该先删除SPJ表中的相应的记录，再删除S表中的相应的记录！或：alter table SPJ add foreign key(SNO) references S(SNO) on delete cascadedelete from S where SNO=S2,成绩分布图优秀作业：杨涛，王伟，于彦雷 ，张小雪未交作业：蔡荀，李京哲 ，李三川，徐源,总结,Tips,写SQL语句的时候注意格式工整，让人能够清晰看出各个子句，最好不要所有的字句都写在一起能用连接实现的就不要用嵌套查询，因为连接算法有很多优化措施，这点在实验4中大家可以通过查询执行计划来体会书写工整很重要，不工整的书写会导致阅读者不明白写的是什么，从而无法做出准确判断,