数据库---[复习2]---数据查询---设有一个SPJ数据库，包括S、P、J及SPJ4个关系模式··· ···

问题重述

设有一个SPJ数据库，包括S、P、J及SPJ4个关系模式：
问题：

1. 找出所有供应商的姓名和所在城市
2. 找出所有零件的名称，颜色，重量
3. 找出使用供应商S1所供应零件的工程号码
4. 找出工程项目J2使用的各种零件的名称及数量
5. 找出上海厂商供应的所有零件号码
6. 找出使用上海产的零件的工程名称
7. 找出没有使用天津产的零件的工程号码
8. 求供应工程J1零件的不重复的供应商号码sno
9. 求供应工程J1零件P1的供应商号码sno
10. 求供应工程J1零件为红色的供应商号码sno

附加项：

1. 求没有使用天津供应商生产的红色零件的工程号jno
2. 求至少用了供应商S1所供应的全部零件的工程号Jno

数据表

S表：

S(SNO,SNAME,STATUS,CITY)
供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。

SNO	SNAME	STATUS	CITY
S1	精益	20	天津
S2	盛锡	10	北京
S3	东方红	30	北京
S4	丰泰盛	20	天津
S5	为民	30	上海

P表：

P(PNO,PNAME,COLOR,WEIGHT)
零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。

PNO	PNAME	COLOR	WEIGHT
P1	螺母	红	12
P2	螺栓	绿	17
P3	螺丝刀	蓝	14
P4	螺丝刀	红	14
P5	凸轮	蓝	40
P6	齿轮	红	30

J表：

J(JNO,JNAME,CITY)
工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。

JNO	JNAME	CITY
J1	三建	北京
J2	一汽	长春
J3	弹簧厂	天津
J4	造船厂	天津
J5	机车厂	唐山
J6	无线电厂	常州
J7	半导体厂	南京

SPJ表：

SPJ(SNO,PNO,JNO,QTY)
供应商情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

SNO	PNO	JNO	QTY
S1	P1	J1	200
S1	P1	J3	100
S1	P1	J4	700
S1	P2	J4	700
S1	P2	J2	100
S2	P3	J1	400
S2	P3	J2	200
S2	P3	J4	500
S2	P3	J5	400
S2	P5	J1	400
S2	P5	J2	100
S3	P1	J1	200
S3	P3	J1	200
S4	P5	J1	100
S4	P6	J3	300
S4	P6	J4	200
S5	P2	J4	100
S5	P3	J1	200
S5	P6	J2	200
S5	P6	J4	500

创建及插入数据表的SQL语句在文末附录1

问题解析

1. 找出所有供应商的姓名和所在城市

SELECT SNAME,CITY
FROM s;

运行结果：

2. 找出所有零件的名称，颜色，重量

SELECT PNAME,COLOR,WEIGHT
FROM P;

运行结果：

3. 找出使用供应商S1所供应零件的工程号码

SELECT JNO
FROM SPJ
WHERE SNO='S1' ;

运行结果：

4. 找出工程项目J2使用的各种零件的名称及数量

SELECT PNAME,QTY
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND SPJ.JNO='J2';

运行结果：

5. 找出上海厂商供应的所有零件号码

SELECT PNO
FROM S,SPJ
WHERE SPJ.SNO=S.SNO AND S.CITY = '上海'；

运行结果：

6. 找出使用上海产的零件的工程名称

SELECT JNO
FROM SNO,PNO
WHERE S.SNO=SPJ.SNO AND S.CITY = '上海'；
或
SELECT JNO
FROM J
WHERE JNO IN (SELECT JNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND S.CITY = '上海')；

运行结果：

7. 找出没有使用天津产的零件的工程号码

SELECT JNO
FROM J
WHERE JNO NOT IN(SELECT JNO
FROM  SPJ,S
WHERE S.SNO=SPJ.SNO AND S.CITY='天津'）；

运行结果：

8. 求供应工程J1零件的不重复的供应商号码sno

SELECT  DISTINCT SNO
FROM SPJ
WHERE SPJ.JNO = 'J1' ;

运行结果：

9. 求供应工程J1零件P1的供应商号码sno

SELECT SNO
FROM SPJ
WHERE SPJ.JNO='J1' AND SPJ.PNO = 'P1';

运行结果：

10. 求供应工程J1零件为红色的供应商号码sno

SELECT SNO
FROM P,SPJ
WHERE SPJ.PNO=P.PNO AND SPJ.JNO = 'J1' AND P.COLOR='红'；

运行结果：

附加项

1. 求没有使用天津供应商生产的红色零件的工程号jno

SELECT JNO
FROM J
WHERE JNO NOT IN (SELECT JNO
FROM P,SPJ,S
WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY='天津' AND P.COLOR = '红 ' ;

运行结果：

2. 求至少用了供应商S1所供应的全部零件的工程号Jno

此题中用到了EXISTS谓词的子查询，可查看带有EXISTS谓词的子查询—查询语句中含有‘至少’条件的查询文章。

SELECT DISTINCT JNO
FROM J
WHERE NOT EXISTS(
SELECT *
FROM SPJ SPJY
WHERE SPJY.SNO = 'S1' AND NOT EXISTS(
SELECT *
FROM SPJ SPJZ
WHERE J.JNO = SPJZ.JNO AND 
SPJZ.PNO = SPJY.PNO
AND SPJZ.SNO = 'S1'
) );

运行结果：

附录1：

/*建立S表*/
CREATE TABLE S(SNO CHAR(9) PRIMARY KEY,
SNAME CHAR(24) UNIQUE,
STATUS INT(4),
CITY CHAR(16));

/*P表*/
CREATE TABLE P(PNO CHAR(9) PRIMARY KEY,
PNAME CHAR(24),
COLOR CHAR(8),
WEIGHT INT(4));

/*J表*/
CREATE TABLE J(JNO CHAR(9) PRIMARY KEY,
JNAME CHAR(24),
CITY CHAR(16));

/*spj表*/
CREATE TABLE SPJ(SNO CHAR(9),
PNO CHAR(9),
JNO CHAR(9),
QTY INT(6),
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO));

/*S表添加数据*/
INSERT INTO S VALUES('S1','精益',20,'天津');
INSERT INTO S VALUES('S2','盛锡',10,'北京');
INSERT INTO S VALUES('S3','东方红',30,'北京');
INSERT INTO S VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S VALUES('S5','为民',30,'上海');

SELECT * FROM S;

/*P表添加数据*/
INSERT INTO P VALUES('P1','螺母','红',12);
INSERT INTO P VALUES('P2','螺栓','绿',17);
INSERT INTO P VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P VALUES('P4','螺丝刀','红',14);
INSERT INTO P VALUES('P5','凸轮','蓝',40);
INSERT INTO P VALUES('P6','齿轮','红',30);

SELECT * FROM P;
/*J表添加数据*/
INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无线电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');
SSSSSS
SELECT * FROM J;
/*SPJ表添加数据*/

INSERT INTO SPJ VALUES('S1', 'P1', 'J1',200);
INSERT INTO SPJ VALUES('S1', 'P1', 'J3',100);
INSERT INTO SPJ VALUES('S1', 'P1', 'J4',700);
INSERT INTO SPJ VALUES('S1', 'P2', 'J4',700);
INSERT INTO SPJ VALUES('S1', 'P2', 'J2',100);
INSERT INTO SPJ VALUES('S2', 'P3', 'J1',400);
INSERT INTO SPJ VALUES('S2', 'P3', 'J2',200);
INSERT INTO SPJ VALUES('S2', 'P3', 'J4',500);
INSERT INTO SPJ VALUES('S2', 'P3', 'J5',400);
INSERT INTO SPJ VALUES('S2', 'P5', 'J1',400);
INSERT INTO SPJ VALUES('S2', 'P5', 'J2',100);
INSERT INTO SPJ VALUES('S3', 'P1', 'J1',200);
INSERT INTO SPJ VALUES('S3', 'P3', 'J1',200);
INSERT INTO SPJ VALUES('S4', 'P5', 'J1',100);
INSERT INTO SPJ VALUES('S4', 'P6', 'J3',300);
INSERT INTO SPJ VALUES('S4', 'P6', 'J4',200);
INSERT INTO SPJ VALUES('S5', 'P2', 'J4',100);
INSERT INTO SPJ VALUES('S5', 'P3', 'J1',200);
INSERT INTO SPJ VALUES('S5', 'P6', 'J2',200);
INSERT INTO SPJ VALUES('S5', 'P6', 'J4',500);
INSERT INTO SPJ VALUES('S1', 'P2', 'J4',700);

SELECT * FROM SPJ;

此文为期末MYSQL查询语句复习题，祝大家期末考出个好成绩。goodbye!

END!