《《SQL作业讲评》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《SQL作业讲评》PPT课件.ppt（57页珍藏版）》请在三一办公上搜索。
1、作业3讲评,数据库课程作业,第12题,考核点：基本概念了解SQL语言的特点及其功能问题：基本都能答对,第3题,考核点：用SQL语句建立表回答要点：对于相同属性的定义要一致，如S表中的sno和SPJ表中的sno定义要一致。问题：基本没有问题,第3题DDL,考核要点理解题设中隐含的完整性约束要求考查DDL的运用共同问题没有声明主码没有声明外码 缺少必要的check 非空属性，没有not null约束 声明了主码后，多余地添加unique，not null约束等 不知道如何声明包含多个属性的主码,第3题DDL（续）,例1create table P(PNO char(5)primary key,PN
2、AME char(10)not null,COLOR char(10),WEIGHT int check(weight 0),有了primary key，还有not null 或unique,例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 0)primary key(SNO,PNO,JNO);,References S,三个属性上都有或只有一个
3、属性上有primary key,第4题,考核点：将关系代数语言完成的查询转换成SQL语句。共同问题：书写SQL语句不规范，在第5题中也是这样，比如少了，或者少了AND等等。,第4题（续）,答题要点：(1)(2)(3)问题不大，很容易将关系代数对应到SQL语句，个别同学没有区分“=”和“in”。(4)(5)主要考察对exists的使用，可以用连接或者嵌套子查询来完成。重点要从逻辑上分析语义来写出SQL查询。,第4题select from where,共同问题：语法错误说明上机实践少 没有认真审题，所答非所问如5.6题，题设要求工程名称，而很多同学回答的却是工程号 设计SQL时没有考虑效率问题只求
4、可以运行正确对谓词any，all，exists理解不够深刻缺乏理论知识没有从语义来分析 何时需要distinct？,典型语法错误,错误1：select*from pwhere 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),4.1,求供应工程J1零件的供应商号码需要distinct？参考答案：select distinct SNO from SPJ where JNO=J1,S
5、(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,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),4.3,求供应工程J1零件为红色的供应商号码SNO select SPJ.SNO f
6、rom 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 SPJ WHERE JNO=J1 AND PNO IN-找红色零件的零件号码pno(SELECT PNO FROM P-从P表中找 WHERE COLOR=红),S(SNO,SNAME,STATUS,CITY)P(PNO
7、,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),第4题（续）,(4)找出没有使用天津供应商生产的红色零件的工程号JNO,注意：从J表入手，以包含那些尚未使用任何零件的工程号。,求没有使用天津供应商生产的红色零件的工程号JNO关系代数：JNO(J)-JNO(SNO(CITY=“天津”)(S)SNO,PNO,JNO(SPJ)PNO(COLOR=“红”)(P)JNO(J)是全部工程的工程号减去的部分表示使用了天津供应商生产的红色零件的所有工程号。,求没有使用天津供应商生产的红色零件的工程号 没有使用任何零件的工程是不是？select
8、 distinct JNO from SPJ where PNO NOT IN(select PNOfrom S,P,SPJwhere S.CITY=天津 AND S.SNO=SPJ.SNO AND P.COLOR=红 AND P.PNO=SPJ.PNO),第4题（续）,(4)找出没有使用天津供应商生产的红色零件的工程号JNO,嵌套子查询SELECT JNOFROM JWHERE NOT EXISTS(SELECT*FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN(SELECT SNO FROM S WHERE CITY=天津)AND PNO IN(SELECT
9、PNO FROM P WHERE COLOR=红);,自然连接SELECT JNOFROM JWHERE NOT EXISTS(SELECT*FROM S,P,SPJ WHERE S.CITY=天津 AND P.COLOR=红 AND SPJ.JNO=J.JNO AND 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提供的全部
10、零件的工程号JNO,分析：本题语义：不存在这样的零件y，供应商S1供应了y，而工程x没有选用y。即：只要“供应商S1供应了零件y”为真，则“工程x选用了零件y”为真。,求至少用了供应商S1所供应的全部零件的工程号JNO考察：除关系关系代数：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,COLO
11、R,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),第4题（续）,(5)求至少用了供应商S1提供的全部零件的工程号JNO,select*from where 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)求
12、至少用了供应商S1提供的全部零件的工程号JNO,select*from SPJ X where not exists(select*from SPJ Y wherenot 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 JNOFROM SPJ XWHERE NOT EXISTS(SELECT*FROM SPJ Y WHERE SNO=S1 AND NOT EXISTS(SELECT*FROM S
13、PJ Z WHERE Z.PNO=Y.PNO AND Z.JNO=X.JNOAND Y.SNO=S1);,S(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这个限制条件，就会可能将这些工程选择到结果中
14、。,第5题,考核点：熟练掌握SQL语句的查询和更新操作。共同问题：书写SQL语句不规范,第5题,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,SPJWHERE SPJ.JNO=J2 AND SPJ.PNO=P.PNOGROUP BY P.
15、PNO,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),5.4,参考答案SELECT PNAME,sum(QTY)FROM P,SPJWHERE SPJ.JNO=J2 AND SPJ.PNO=P.PNOGROUP 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 PNOFROM
16、SPJWHERE 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,SWHERE SPJ.SNO=S.SNO AND S.CITY=上海,S(SNO,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEIGHT)J(JNO,JNAM
17、E,CITY)SPJ(SNO,PNO,JNO,QTY),5.6参考答案SELECT JNAME FROM JWHERE 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 JNOFROM SPJWHERE NOT EXIST
18、S(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 JNOFROM JWHERE not EXISTS(SELECT FROM SPJ WHERE SPJ.JNO=J.JNO AND SNOIN（SELECTSNOFROM SWHERE S.CITY=天津）),S(SNO
19、,SNAME,STATUS,CITY)P(PNO,PNAME,COLOR,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),5.7参考答案（2）,SELECT JNOFROM JWHERE 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,WEIGHT)J(JNO,JNAME,CITY)SPJ(SNO,PNO,JNO,QTY),5.8比较简单5.9由S5供给J
20、4的零件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,QTY),5.10从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录 方法1：声明外码引用为级联删除。直接删除S表中的记录alter table spj add fo
21、reign key(sno)references s(sno)on delete cascadeDELETE 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表中的相应的记录,DELECTFROM SPJWHERE SNO=S2DELETEFROM SWHERE SNO=S2注意删除顺序,5.11 请将（S2，P4，J6，2000）插入供应情况关系 如果与5.10联系起来，则需要向S表
22、插入记录（S2），然后才能插入,总结,需要多多上机体会写出的SQL，应该先上机进行验证是否正确部分同学需要更大的努力他们的作业几乎全都是错误的；优秀作业：,第6题,考核点：基本表和视图的概念两者之间的区别和联系问题：可以从课本上找到概念，关键是要理解,11视图的建立（1）,CREATE VIEW VSJASSELECT SNO,PNO,QTYFROM SPJWHERE JNO=(SELECT JNOFROM JWHERE JNAME=三建),视图的建立（2）,CREATE VIEW VSJASSELECT SNO,PNO,QTYFROM SPJ,JWHERE SPJ.JNO=J.JNO AND
23、 JNAME=三建,(1)查询找出三建工程项目使用的各种零件代码及其数量,SELECT PNO，sum(QTY)FROM VSJGroup by pno,2)找出供应商S1的供货情况,SELECT PNO,QTY/*S1供应三建工程的零件号和对应的数量*/FROM VSJWHERE SNO=S1,补充作业,（1）检索LIU老师所授课程的课程号和课程名。SELECT C#，CNAME FROM C WHERE TEACHER=LIU,学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),补充作业,（2）检索年龄大于23岁的男
24、学生的学号和姓名。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,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老师所授课程中一门课程的女
25、学生姓名。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)课程 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#,SNAM
26、E,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*F
27、ROM S,SC WHERE S.S#=SC.S#AND SC.C#=C.C#AND SNAME=WANG),学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),补充作业,（6）检索至少选修两门课程的学生学号。SELECT DISTINCT X.S#FROMSC X,SC Y WHERE X.S#=Y.S#AND X.C#Y.C#Notice:对表SC进行自连接，X，Y是SC的两个别名。,学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),补充作业
28、,（7）检索全部学生都选修的课程的课程号与课程名。SELECT C#,CNAME FROM C WHERE NOT EXISTS(SELECT*FROM S WHERE S#NOT IN(SELECT*FROM SC WHERE SC.C#=C.C#),学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),补充作业,其中，“不选这门课的同学”可以表示为：,学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),SELECT*FROM S WHERE S#NOT IN（SELECT*FROM SC WHERE SC.C#=C.C#),SELECT*FROM S WHERE NOT EXISTS（SELECT*FROM SC WHERE S.S#=C.S#AND SC.C#=C.C#),或者,补充作业,（8）检索选修课程包含LIU老师所授课的学生学号。SELECT DISTINCT S#FROM SC WHERE C#IN(SELECT C#FROM C WHERE TEACHER=LIU),学生 S(S#,SNAME,AGE,SEX)学习 SC(S#,C#,GRADE)课程 C(C#,CNAME,TEACHER),
