《SQL-作业讲评》PPT课件.ppt

1、作业3讲评,数据库课程作业,第12题,考核点： 基本概念 了解SQL语言的特点及其功能 问题： 基本都能答对,第3题,考核点： 用SQL语句建立表 回答要点： 对于相同属性的定义要一致，如S表中的sno和SPJ表中的sno定义要一致。 问题： 基本没有问题,第3题DDL,考核要点 理解题设中隐含的完整性约束要求 考查DDL的运用 共同问题 没有声明主码 没有声明外码 缺少必要的check 非空属性，没有not null约束 声明了主码后，多余地添加unique，not null约束等 不知道如何声明包含多个属性的主码,第3题DDL（续）,例1 create table P ( PNO char

2、(5) primary key, PNAME char(10) not null, COLOR char(10), WEIGHT int check(weight 0 ) ),有了primary key ，还有not null 或unique,例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 (S

3、NO,PNO,JNO) );,References S,三个属性上都有或只有一个属性上有primary key,第4题,考核点： 将关系代数语言完成的查询转换成SQL语句。 共同问题： 书写SQL语句不规范，在第5题中也是这样，比如少了，或者少了AND等等。,第4题（续）,答题要点： (1)(2)(3)问题不大，很容易将关系代数对应到SQL语句，个别同学没有区分“=”和“in”。 (4)(5)主要考察对exists的使用，可以用连接或者嵌套子查询来完成。重点要从逻辑上分析语义来写出SQL查询。,第4题select from where,共同问题： 语法错误说明上机实践少 没有认真审题，所答非所

4、问 如5.6题，题设要求工程名称，而很多同学回答的却是工程号 设计SQL时没有考虑效率问题 只求可以运行正确 对谓词any，all，exists理解不够深刻 缺乏理论知识 没有从语义来分析 何时需要distinct？,典型语法错误,错误1： select * from p where pno = (select pno from spj where sno = s1 ) 错误2： Exit，,S ( s# ,sname,status,city) P ( p# ,pname,color,weight,city) J ( j# ,jname,city) SPJ (s# ,p# ,j# ,qty),

5、4.1,求供应工程J1零件的供应商号码 需要distinct ？ 参考答案： select distinct SNO from SPJ where JNO = J1,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),4.2,求供应工程J1零件P1的供应商号码 需要distinct吗？ 参考答案： select SNO from SPJ where (JNO = J1 and PNO = P1);,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAM

6、E,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),4.3,求供应工程J1零件为红色的供应商号码SNO select SPJ.SNO from SPJ , P where (SPJ.JNO = J1 and P.PNO = SPJ.PNO and P.COLOR = 红),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),4.3另,求供应工程J1零件为红色的供应商号码SNO SELECT SNO FROM S

7、PJ WHERE JNO=J1 AND PNO IN -找红色零件的零件号码pno (SELECT PNO FROM P -从P表中找 WHERE COLOR=红),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),第4题（续）,(4)找出没有使用天津供应商生产的红色零件的工程号JNO,注意：从J表入手，以包含那些尚未使用任何零件的工程号。,求没有使用天津供应商生产的红色零件的工程号JNO 关系代数： JNO(J)-JNO(SNO( CITY = “天津”)(S)

8、SNO,PNO,JNO(SPJ) PNO( COLOR=“红”)(P) JNO(J)是全部工程的工程号 减去的部分表示使用了天津供应商生产的红色零件的所有工程号。,求没有使用天津供应商生产的红色零件的工程号 没有使用任何零件的工程是不是？ select distinct JNO from SPJ where PNO NOT IN (select PNO from S ,P,SPJ where S.CITY =天津 AND S.SNO = SPJ.SNO AND P.COLOR =红 AND P.PNO = SPJ.PNO ),第4题（续）,(4)找出没有使用天津供应商生产的红色零件的工程号JN

9、O,嵌套子查询 SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO = J.JNO AND SNO IN (SELECT SNO FROM S WHERE CITY = 天津) AND PNO IN (SELECT PNO FROM P WHERE COLOR = 红 ) );,自然连接 SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM S,P,SPJ WHERE S.CITY = 天津 AND P.COLOR = 红 AND SPJ.JNO = J.JNO A

10、ND SPJ.SNO = S.SNO AND SPJ.PNO = P.PNO );,如果没有使用任何零件， 则此条件永假,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,分析： 本题语义：不存在这样的零件y，供应商S1供应了y，而工程x没有选用y。 即：只要“供应商S1供应了零件y”为真，则“工程x选用了零件y”为真。,求至少用了供应商S1所供应的全部零件的工程号JNO 考察：除关系 关系代数：

11、 JNO,PNO(SPJ) PNO( SNO=S1(SPJ),除操作在写关系演算表达式的关键：包含一个蕴含关系。,除法的SQL表示： select * from where not exists (select * from where not exists (select * from Where ) ),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,select * from where

12、not exists (select * from wherenot exists (select * from where );,第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,select * from SPJ X where not exists (select * from SPJ Y wherenot exists (select * from SPJ Z where );,第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,select * from SPJ X where not exists (select * from SPJ Y wh

13、erenot exists (select * from SPJ Z where Z.PNO = Y.PNO AND Z.JNO = X.JNO AND Y.SNO=S1 );,第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,SELECT DISTINCT JNO FROM SPJ X WHERE NOT EXISTS (SELECT * FROM SPJ Y WHERE SNO = S1 AND NOT EXISTS (SELECT * FROM SPJ Z WHERE Z.PNO = Y.PNO AND Z.JNO = X.JNO AND Y.SNO=S1 );,S(

14、SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),求至少用了供应商S1提供的全部零件的工程号JNO,而不是“求至少用了供应商S1所“能”供应的全部零件的工程号JNO。 这里隐含的语义是：某些零件虽然是S1”能够“提供的，但是工程并没有使用S1供应的零件，而是使用了其他供应商提供的该种零件。 在这种情况下，不加spj.sno=s1这个限制条件，就会可能将这些工程选择到结果中。,第5题,考核点： 熟练掌握SQL语句的查询和更新操作。 共同问题： 书写SQL语句不规范,第5题

15、,5.1，5.2较简单 5.3也比较简单，但需要取消重复 5.4 找出工程项目J2使用的各种零件的名称及其数量 典型错误： 把重量当作数量 没有对同类的零件进行group by ，再对数量QTY求和 使用不确切的属性进行分类,5.4,错例1： SELECT PNAME,QTY FROM P ,SPJ WHERE SPJ.PNO=P.PNO AND JNO=J2 错例2： SELECT PNAME , sum(QTY ) FROM P ,SPJ WHERE SPJ.JNO = J2 AND SPJ.PNO = P.PNO GROUP BY P.PNO,S(SNO,SNAME,STATUS,CIT

16、Y) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.4,参考答案 SELECT PNAME , sum(QTY ) FROM P ,SPJ WHERE SPJ.JNO = J2 AND SPJ.PNO = P.PNO GROUP BY P.PNAME,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.5比较简单,SELECT DISTINCT PNO FROM SPJ WHE

17、RE SNO IN (SELECT SNO FROM S WHERE CITY=上海),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.6，主要问题在于审题不清 题设要求工程名称，而不是工程号码 居然相当多的同学都犯了这个错 典型错误： SELECT JNO FROM SPJ,S WHERE SPJ.SNO = S.SNO AND S.CITY = 上海,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(

18、JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.6参考答案 SELECT JNAME FROM J WHERE JNO IN ( SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND CITY=上海 ) 也可以用连接方式求。,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.7找出没有使用天津产的零件的工程号码 与4.4类似，没有使用任何零件的工程是不是？ 典型错误： SELECT JNO FR

19、OM SPJ WHERE NOT EXISTS ( SELECT * FROM S,P WHERE S.SNO = SPJ.SNO AND S.CITY=天津 AND P.PNO = SPJ.PNO AND P.COLOR = 红 ),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.7参考答案（1）,SELECT JNO FROM J WHERE not EXISTS (SELECT FROM SPJ WHERE SPJ.JNO = J.JNO AND SNO

20、IN （SELECTSNO FROM S WHERE S.CITY = 天津） ),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.7参考答案（2）,SELECT JNO FROM J WHERE NOT EXISTS ( SELECT * FROM SPJ,S WHERE S.SNO = SPJ.SNO AND S.CITY=天津 AND J.JNO = SPJ.JNO ),S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR

21、,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),5.8比较简单 5.9由S5供给J4的零件P6改为由S3供应 典型错误： update SPJ set SNO = S3 where (PNO = P6 and JNO = J4); 参考答案： update SPJ set SNO = S3 where (SNO = S5 and PNO = P6 and JNO = J4);,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,Q

22、TY),5.10从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录 方法1：声明外码引用为级联删除。直接删除S表中的记录 alter table spj add foreign key (sno) references s(sno) on delete cascade DELETE FROM S WHERE SNO=S2,S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY),方法2：先删除SPJ表中的相应的记录，再删除S表中的相应的记录,DELECT FRO

23、M SPJ WHERE SNO=S2 DELETE FROM S WHERE SNO=S2 注意删除顺序,5.11 请将（S2，P4，J6，2000）插入供应情况关系 如果与5.10联系起来，则需要向S表插入记录（S2），然后才能插入,总结,需要多多上机体会 写出的SQL，应该先上机进行验证是否正确 部分同学需要更大的努力 他们的作业几乎全都是错误的； 优秀作业：,第6题,考核点： 基本表和视图的概念 两者之间的区别和联系 问题： 可以从课本上找到概念，关键是要理解,11视图的建立（1）,CREATE VIEW VSJ AS SELECT SNO,PNO,QTY FROM SPJ WHERE

24、JNO= (SELECT JNO FROM J WHERE JNAME=三建),视图的建立（2）,CREATE VIEW VSJ AS SELECT SNO,PNO,QTY FROM SPJ,J WHERE SPJ.JNO=J.JNO AND JNAME=三建,(1)查询找出三建工程项目使用的各种零件代码及其数量,SELECT PNO，sum(QTY) FROM VSJ Group by pno,2)找出供应商S1的供货情况,SELECT PNO,QTY /*S1供应三建工程的零件号和对应的数量*/ FROM VSJ WHERE SNO=S1,补充作业,（1）检索LIU老师所授课程的课程号和课

25、程名。 SELECT C#，CNAME FROM C WHERE TEACHER=LIU,学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE) 课程 C(C#,CNAME,TEACHER ),补充作业,（2）检索年龄大于23岁的男学生的学号和姓名。 SELECT S#，SNAME FROM S WHERE (AGE23) AND (SEX=M),学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE) 课程 C(C#,CNAME,TEACHER ),补充作业,（3）检索学号为S3学生所学课程的课程号与任课教师名。 SELECT CNAME

26、,TEACHER FROM SC,C WHERE SC.C#=C.C# AND S#=S3,学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE) 课程 C(C#,CNAME,TEACHER ),补充作业,（4）检索至少选修LIU老师所授课程中一门课程的女学生姓名。 SELECT SNAME FROM S WHERE SEX=F AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=LIU),学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE)

27、课程 C(C#,CNAME,TEACHER ),补充作业,（4）检索至少选修LIU老师所授课程中一门课程的女学生姓名。 SELECT SNAME FROM S,SC,C WHERE SEX=F AND SC.S#=S.S# AND SC.C#=C.C# AND TEACHER=LIU,学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE) 课程 C(C#,CNAME,TEACHER ),补充作业,（5）检索WANG同学不学的课程的课程号。 SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC WHERE S# IN (SELECT S# FROM S WHERE SNAME=WANG),学生 S(S#,SNAME,AGE,SEX) 学习 SC(S#,C#,GRADE) 课程 C(C#,CNAME,TEACHER ),补充作业,（5）检索WANG同学不学的课程的课程号。 SELECT C# FROM C WHERE NOT EXISTS (SELECT * FROM S,SC WHE