供应商-零件-工程数据库.doc
数据库原理——SPJ
PAGE 1
PAGE 2
供应商-零件-工程数据库:
S{S#,SNAME,STATUS,CITY}
PRIMARY KEY {S#}
P{P#,PNAME,COLOR,WEIGHT,CITY}
PRIMARY KEY {P#}
J{J#,JNAME,CITY}
PRIMARY KEY {J#}
SPJ{S#,P#,J#,QTY}
PRIMARY KEY {S#,P#,J#}
FOREIGN KEY {S#} REFERENCE S
FOREIGN KEY {P#} REFERENCE P
FOREIGN KEY {J#} REFERENCE J
求所有工程的信息
SELECT *
FROM J;
求在伦敦的所有工程的信息
SELECT *
FROM J
WHERE CITY = 'London';
求为工程J1提供零件的供应商的号码
SELECT S#
FROM SPJ
WHERE J#='J1';
求数量在300-750之间的发货
SELECT *
FROM SPJ
WHERE QTY BETWEEN 300 AND 750
求所有的零件颜色/城市对。注意:这里及以后所说的“所有”特指在数据库中
SELECT DISTINCT COLOR, CITY
FROM P;
求出所有的供应商号/零件号/工程号三元组。其中所指的供应商、零件和工程在同一个城市
SELECT S.S#, P.P#, J.J#
FROM S, P, J
WHERE S.CITY=P.CITY
AND S.CITY=J.JCITY;
求出所有的供应商号/零件号/工程号三元组。其中所指的供应商、零件和工程在不同一个城市
SELECT S.S#, P.P#, J.J#
FROM S, P, J
WHERE NOT(S.CITY=P.CITY AND S.CITY=J.JCITY);
或WHERE S.CITY<>P.CITY OR S.CITY<>J.JCITY;
求出所有的供应商号/零件号/工程号三元组。其中所指的供应商、零件和工程三者中的任意两个都不在同一个城市
SELECT S.S#, P.P#, J.J#
FROM S, P, J
WHERE S.CITY<>P.CITY AND S.CITY<>J.JCITY AND P.CITY<>J.JCITY;
求由伦敦供应商提供的零件信息
SELECT *
FROM P
WHERE P# IN
(SELECT P#
FROM SPJ,S
WHERE SPJ.S#=S.S#
AND S.CITY = 'London');
求由伦敦供应商为伦敦工程供应的零件号
SELECT DISTINCT P#
FROM SPJ,S,J
WHERE SPJ.S#=S.S# AND SPJ.J#=J.J#
AND S.CITY = 'London' AND J.CITY = 'London';
求满足下面要求的城市对,要求在第一个城市的供应商为第二个城市的工程供应零件
SELECT DISCINCT S.CITY, J.CITY
FROM SPJ, S, J
WHERE SPJ.S#=S.S# AND SPJ.J#=J.J#
SELECT DISCINCT S.CITY, J.CITY
FROM S, J
WHERE EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.S#=S.S# AND SPJ.J#=J.J#);
求供应商为工程供应的零件号,要求供应商和工程在同一城市
SELECT DISTINCT P#
FROM SPJ, S,J
WHERE SPJ.S#= S.S# AND SPJ.J#=J.J#
AND S.CITY = J.CITY;
求至少被一个与该工程不在同一城市的供应商供应零件的工程号
SELECT DISTINCT J.J#
FROM SPJ, J, S
WHERE SPJ.J# = J.J# AND SPJ.S# = S.S#
AND J.CITY<>S.CITY;
求被不在同一城市的供应商供应零件的工程号(供应商来自不同的城市)
SELECT J.J#
FROM J
WHERE (SELECT COUNT(DISTINCT S.CITY)
FROM SPJ,S
WHERE SPJ.J#=J.J# AND SPJ.S#=S.S#)>1;
SELECT J.J#
FROM J,SPJ,S
WHERE SPJ.J#=J.J# AN