第3章SQL_作业讲评 (2)

如果没有使用任何零件， 则此条件永假
第4题（续）
(5)求至少用了供应商S1提供的全部零件的工程号JNO
分析： 本题语义：不存在这样的零件y，供应商S1供
应了y，而工程x没有选用y。 即：只要“供应商S1供应了零件y”为真，则
“工程x选用了零件y”为真。
求至少用了供应商S1所供应的全部零件的工程 号JNO
FROM
P ,SPJ
WHERE SPJ.JNO = ‘J2’

AND SPJ.PNO = P.PNO
GROUP BY P.PNAME
S(SNO,SNAME,STATUS,CITY)
5.5比较简单
P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
嵌套子查询
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 = ‘红’ )
SPJ(SNO,PNO,JNO,QTY)
求供应工程J1零件P1的供应商号码
需要distinct吗？
参考答案：
select SNO
from SPJ
where (JNO = 'J1‘ and PNO = 'P1');
4.3
S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY)
作业3－讲评
数据库课程作业
第1－2题
考核点：
基本概念 了解SQL语言的特点及其功能
问题：
基本都能答对
第3题
考核点：
用SQL语句建立表
回答要点：
对于相同属性的定义要一致，如S表中的sno和 SPJ表中的sno定义要一致。
问题：
基本没有问题
第3题－DDL
考核要点
这里隐含的语义是：某些零件虽然是S1”能够“提供的， 但是工程并没有使用S1供应的零件，而是使用了其他供 应商提供的该种零件。
在这种情况下，不加spj.sno=‘s1’这个限制条件，就会可 能将这些工程选择到结果中。
第5题
考核点：
熟练掌握SQL语句的查询和更新操作。
共同问题：
书写SQL语句不规范
第5题
5.1，5.2较简单 5.3也比较简单，但需要取消重复 5.4
找出工程项目J2使用的各种零件的名称及其数量 典型错误：
把重量当作数量 没有对同类的零件进行group by ，再对数量QTY求和 使用不确切的属性进行分类
5.4
S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT)
考察：除关系 关系代数：
∏JNO,PNO(SPJ) ÷∏PNO( SNO=‘S1’(SPJ))
除操作在写关系演算表达式的关键：包含一个蕴含关系。
除法÷的SQL表示：
S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY)
SPJ(SNO,PNO,JNO,QTY)
求供应工程J1零件为红色的供应商号码SNO
SELECT SNO FROM SPJ
WHERE JNO=‘J1’
AND PNO IN --找红色零件的零件号码pno
(SELECT PNO

FROM P --从P表中找

WHERE COLOR=‘红’)
SPJ(SNO,PNO,JNO,QTY)
求供应工程J1零件的供应商号码
需要distinct ？
参考答案：
select distinct SNO
from SPJ
where JNO = 'J1’
S(SNO,SNAME,STATUS,CITY)
4.2
P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY)

AND SPJ.PNO = P.PNO
GROUP BY P.PNO
5.4
参考答案
S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY)
SELECT PNAME , sum(QTY )
(select * from
where not exists
(select * from
where
));
第4题（续）
(5)求至少用了供应商S1提供的全部零件的工程号JNO
select * from SPJ X where not exists (select * from SPJ Y where not exists (select * from SPJ Z where ));
∏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
第4题（续）

(5)求至少用了供应商S1提供的全部零件的工程号JNO
select * from SPJ X where not exists
(select * from SPJ Y where not exists
(select * from SPJ Z
where Z.PNO = Y.PNO
AND Z.JNO = X.JNO
AND Y.SNO=‘S1’
));
S(SNO,SNAME,STATUS,CITY)
第4题（续）
P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
(5)求至少用了供应商S1提供的全部零件的工程号JNO
SELECT DISTINCT JNO FROM SPJ X WHERE NOT EXISTS
求供应工程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)
4.3另
P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY)
(4)(5)主要考察对exists的使用，可以用连接或 者嵌套子查询来完成。重点要从逻辑上分析语义 来写出SQL查询。
第4题－select from where
共同问题：
语法错误――说明上机实践少 没有认真审题，所答非所问
如5.6题，题设要求工程名称，而很多同学回答的却是工程 号
create table P
( PNO char(5) primary key,
PNAME char(10) not null,
COLOR char(10),
WEIGHT int check(weight > 0 )
)
References S
例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) );
(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’ ));
• 求至少用了供应商S1提供的全部零件的工 程号JNO,而不是“求至少用了供应商S1所 “能”供应的全部零件的工程号JNO。
select * from

where not exists
(select * from
•
where not exists

(select * from

Where )
)
第4题（续）
(5)求至少用了供应商S1提供的全部零件的工程号JNO
select * from
where not exists
J(JNO,JNAME,CITY)
错例1：
SPJ(SNO,PNO,JNO,QTY)
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'