mysql创建零件供应商标_供应商-零件-工程数据库.doc

供应商-零件-工程数据库.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