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中大家可以通过查询执行计划来体会书写工整很重要,不工整的书写会导致阅读者不明白写的是什么,从而无法做出准确判断,