SPJ数据库.doc

设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：
供应商S(SNO , SNAME , STATUS , CITY)
零件P(PNO , PNAME , COLOR , WEIGHT)
工程J(JNO , JNAME , CITY)
SPJ(SNO , PNO , JNO , QTY)

创建数据库：
create database SPJ;

创建表：
CREATE TABLE S ( SNO char(2) primary key,
SNAME varchar(20),
STATUS char(4),
CITY char(10));
create table P ( PNO char(2) primary key,
PNAME varchar(20),
COLOR char(4),
WEIGHT int);
create table J ( JNO char(2) primary key,
JNAME varchar(20),
CITY char(10));
create table SPJ ( SNO char(2),
PNO char(2),
JNO char(2),
QTY int,
primary key(SNO,PNO,JNO),
foreign key(SNO) references S(SNO) on delete cascade,
foreign key(PNO) references P(PNO) on delete cascade,
foreign key(JNO) references J(JNO) on delete cascade
);

插入数据：
insert into S values('S1','供应商一','良好','郑州');
insert into S values('S2','供应商二','良好','天津');
insert into S values('S3','供应商三','良好','上海');
insert into S values('S4','供应商四','优秀','郑州');

insert into P values('P1','管材','红色',20);
insert into P values('P2','管线','绿色',30);
insert into P values('P3','螺帽','红色',10);
insert into P values('P4','螺帽','黄色',10);

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 SPJ values('S1','P1','J1',100);
insert into SPJ values('S1','P2','J1',100);
insert into SPJ values('S2','P1','J1',100);
insert into SPJ values('S2''P2','J1',100);
insert into SPJ values('S1','P1','J3',100);
insert into SPJ values('S1','P2','J3',100);
insert into SPJ values('S2','P1','J3',100);
insert into SPJ values('S2','P2','J3',100);
insert into SPJ values('S3','P1','J2',100);
insert into SPJ values('S3','P2','J2',100);
insert into SPJ values('S4','P3','J2',100);

1）求供应工程J1零件的供应商号码SNO：
select distinct SNO
from SPJ
where JNO='J1';

2）求供应工程J1零件P1的供应商号码SNO：
select SNO
from SPJ
where JNO='J1' and PNO='P1';

3）求供应工程J1红色零件的供应商号码SNO：
select SNO
from SPJ,P
where SPJ.PNO=P.PNO and JNO='J1' and P.COLOR='红色';

或者
SELECT SNO /*这是嵌套查询*/
FROM SPJ
WHERE JNO='J1' AND PNO IN /*找出红色零件的零件号码PNO */
(SELECT PNO
FROM P /*从P表中找*/
WHERE COLOR ='红色');

4）求没有使用天津供应商生产的红色零件的工程号JNO
select distinct JNO
from J
where JNO NOT IN ( select JNO
from SPJ,P,S
where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and P.COLOR='红色' and S.CITY='天津'
);
或者：
SELECT JNO
FROM J
WHERE NOT EXISTS(SELECT 1
FROM SPJ
WHERE SPJ.JNO=J.JNO AND SNO IN /*天津供应商的SNO*/
(SELECT SNO
FROM S
WHERE CITY='天津')
AND PNO IN /*红色零件的PNO*/
(SELECT PNO
FROM P
WHERE COLOR='红色'));

5）求至少用了供应商S1所供应的全部零件的工程号JNO
要求这样的工程x,使( y)p→q为真。即,对于所有的零件y,满足逻辑蕴涵p→q：p表示谓词"供应商S1供应了零件y"；q表示谓词"工程 x选用了零件y"。即,只要"供应商S1供应了零件y"为真,则"工程x选用了零件y" 为真。
逻辑蕴涵可以转换为等价形式：﹁ y(p∧﹁q)
它所表达的语义为：不存在这样的零件y,供应商S1供应了y,而工程x没有选用y。
SELECT DISTINCT JNO
FROM SPJ AS SPJ1
WHERE NOT EXISTS /*这是一个相关子查询*/
(SELECT 1 /*父查询和子查询均引用了SPJ表*/
FROM SPJ AS SPJ3 /*用别名将父查询与子查询中的SPJ表区分开*/
WHERE SNO='S1' AND NOT EXISTS
(SELECT 1
FROM SPJ AS SPJ2
WHERE SPJ2.PNO=SPJ3.PNO
AND SPJ2.JNO=SPJ1.JNO));

执行下面操作：
（1） 把全部红色零件的颜色改成蓝色。
UPDATE P
SET COLOR='红色'
WHERE COLOR='蓝色';

（2） 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
DELETE
FROM SPJ
WHERE SNO='S2';

DELETE
FROM S
WHERE SNO='S2';
注意删除顺序,应该先从sn表中删除供应商S2所供应零件的记录,然后从S表中删除S2。

（3） 请将(S2，J6，P4，200)插入供应情况关系。
INSERT INTO SPJ(SNO,JNO,PNO,QTY)
VALUES('S2','J6','P4',200);
或
INSERT INTO SPJ
VALUES('S2','P4','J6',200);

-------------------------------------------------------------------------------------------------------------------------补充查询：
（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 P.PNAME,SPJ.QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';
（5） 找出上海厂商供应的所有零件号码。
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN (SELECT SNO
FROM S
WHERE CITY='上海');
（6） 找出使用上海产的零件的工程名称。
SELECT distinct JNAME
FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY='上海';
或
SELECT JNAME
FROM J
WHERE JNO IN (SELECT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海');
（7） 找出没有使用天津产的零件的工程号码。
SELECT JNO
FROM J
WHERE NOT EXISTS(SELECT 1
FROM SPJ
WHERE SPJ.JNO=J.JNO AND SNO IN(SELECT SNO
FROM S
WHERE CITY='天津'));
或
SELECT JNO
FROM J
WHERE NOT EXISTS (SELECT 1
FROM SPJ,S
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY='天津');
补充修改：
由S4供给J4的零件P4改为由S3供应,请做必要的修改。
insert into SPJ values('S4','P4','J4',200);
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S4' AND JNO='J4' AND PNO='P4';
补充删除：
从供应商关系中删除S4的记录,并从供应情况关系中删除相应的记录。
DELETE
FROM SPJ
WHERE SNO='S4';

DELETE
FROM S
WHERE SNO='S4';
---------------------------------------------------------------------------------------------------------------------------------------------------建立视图：
为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY
FROM SPJ
WHERE JNO=(SELECT JNO
FROM J
WHERE JNAME='三建工程');
针对该视图VSP完成下列查询：
(1) 找出三建工程项目使用的各种零件代码及其数量。
SELECT PNO,QTY
FROM V_SPJ;
(2) 找出供应商S1的供应情况。
SELECT PNO,QTY
FROM V_SPJ
WHERE SNO='S1';