设有一个SPJ数据库，包括S、P、J及SPJ4个关系模式：
S(SNO,SNAME,STATUS,CITY);
P(PNO,PNAME,COLOR,WEIGHT);
J(JNO,JNAME,CITY);
SPJ(SNO,PNO,JNO,QTY);
供应商表S由供应商代码（SNO）、零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。
工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。
供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。
今有若干数据如下：
| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S1 | 精益 | 20 | 天津 |
| S2 | 盛锡 | 10 | 北京 |
| S3 | 东方红 | 30 | 北京 |
| S4 | 丰泰盛 | 20 | 天津 |
| S5 | 为民 | 30 | 上海 |
| PNO | PNAME | COLOR | WEIGHT |
|---|---|---|---|
| P1 | 螺母 | 红 | 12 |
| P2 | 螺栓 | 绿 | 17 |
| P3 | 螺丝刀 | 蓝 | 14 |
| P4 | 螺丝刀 | 红 | 14 |
| P5 | 凸轮 | 蓝 | 40 |
| P6 | 齿轮 | 红 | 30 |
| JNO | JNAME | CITY |
|---|---|---|
| J1 | 三建 | 北京 |
| J2 | 一汽 | 长春 |
| J3 | 弹簧厂 | 天津 |
| J4 | 造船床 | 天津 |
| J5 | 机车厂 | 唐山 |
| J6 | 无线电厂 | 常州 |
| J7 | 半导体厂 | 南京 |
| SNO | PNO | JNO | QTY |
|---|---|---|---|
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | 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 |
/*首先建立表格*/
CREATE TABLE S(
SNO CHAR(12) PRIMARY KEY,
SNAME CHAR(20),
STATUS SMALLINT,
CITY CHAR(12)
);
CREATE TABLE P(
PNO CHAR(12) PRIMARY KEY,
PNAME CHAR(20),
COLOR CHAR(12),
WEIGHT INT
);
CREATE TABLE J(
JNO CHAR(12) PRIMARY KEY,
JNAME CHAR(20),
CITY CHAR(12)
);
CREATE TABLE SPJ(
SNO CHAR(12),
PNO CHAR(12),
JNO CHAR(12),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
);
/*插入数据*/
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,'上海');
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);
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','半导体厂','南京');
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','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);
/***********题目：*************/
/*求供应工程J1零件的供应商号码SNO*/
SELECT SNO FROM SPJ WHERE JNO='J1';
/*求供应工程J1零件P1的供应商号码SNO*/
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';
/*求供应工程J1零件为红色的供应商号码SNO*/
SELECT SNO FROM SPJ,P WHERE JNO='J1' AND P.PNO=SPJ.PNO AND COLOR='红'; /*方法1，多表连接查询*/
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO IN(SELECT PNO FROM P WHERE COLOR='红'); /*方法2，不相关子查询*/
/*求没有使用天津供应商生产的红色零件的工程号JNO*/
SELECT JNO FROM SPJ,S,P WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND CITY!='天津' AND COLOR='红';/*方法1，多表连接查询*/
SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY!='天津') AND PNO IN(SELECT PNO FROM P WHERE COLOR='红'); /*方法2，不相关子查询*/
/*求至少用了供应商S1所供应的全部零件的工程号JNO*/
/*SELECT JNO FROM SPJ SPJ1 WHERE SNO='S1' AND NOT EXISTS (SELECT * FROM P WHERE NOT EXISTS (SELECT * FROM SPJ SPJ2 WHERE SPJ2.SNO='S1' AND P.PNO=SPJ2.PNO))*/
/*找出所有供应商的姓名和所在城市*/
SELECT SNAME,CITY FROM S;
/*找出所有零件的名称、颜色、重量*/
SELECT PNAME,COLOR,WEIGHT FROM P;
/*找出使用供应商S1所供应零件的工程号码*/
SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';
/*找出工程项目J2使用的各种零件的名称及其数量*/
SELECT PNAME,WEIGHT FROM P,SPJ WHERE JNO='J2' AND P.PNO=SPJ.PNO; /*方法1，多表连接*/
SELECT PNAME,WEIGHT FROM P WHERE PNO IN (SELECT PNO FROM SPJ WHERE JNO='J2'); /*方法2，不相关子查询*/
/*找出上海厂商供应的所有零件号码*/
SELECT PNO FROM S,SPJ WHERE CITY='上海' AND S.SNO=SPJ.SNO; /*方法1，多表连接*/
SELECT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海'); /*方法2，不相关子查询*/
/*找出使用上海产的零件的工程名称*/
SELECT DISTINCT JNAME FROM S,J,SPJ WHERE S.CITY='上海' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO; /*方法1，多表连接*/
SELECT DISTINCT JNAME FROM J WHERE JNO IN (SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海')); /*方法2，不相关子查询*/
/*找出没有使用天津产的零件的工程号码*/
SELECT JNO FROM J WHERE JNO NOT IN (SELECT J.JNO FROM S,J,SPJ WHERE S.CITY='天津' AND S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO); /*先找出使用了天津产的零件的工程号码，然后用NOT IN取反*/
SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='天津'));
/*把全部红色零件的颜色改成蓝色*/
UPDATE P SET COLOR='蓝' WHERE COLOR='红';
/*由S5供给J4的零件P6改为由S3供应，请作必要修改*/
UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
/*从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录*/
DELETE FROM SPJ WHERE SNO='S2'; /*先从SPJ中删除*/
DELETE FROM S WHERE SNO='S2'; /*从S中删除*/
/*请将(S2,J6,P4,200)插入供应情况关系*/
INSERT INTO SPJ VALUES ('S2','J6','P4',200);
Comments | NOTHING