根据数据库SPJ，完成如下查询请求：

可用以下语句快速创建SPJ数据库：

--以下为创建SPJ的语句 create database spj go use spj go create table S ( Sno char(4) primary key, Sname char(20) unique, Status smallint, City char(10) ); create table P ( Pno char(4) primary key, Pname char(20), Color char(10), Weight smallint, ); create table J ( Jno char(4) primary key, Jname char(20), City char(10) ); create table SPJ ( Sno char(4), Pno char(4), Jno char(4), Qty smallint, 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','螺栓','绿',12); insert into P values('P3','螺丝刀','蓝',12); insert into P values('P4','螺丝刀','红',12); insert into P values('P5','凸轮','蓝',12); insert into P values('P6','齿轮','红',12); 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','J3',500); insert into SPJ values('S2','P3','J4',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); select * from S select * from P select * from J select * from SPJ

求供应工程J1零件的供应商号SNO

SELECT SPJ.Sno FROM SPJ WHERE SPJ.Jno='J1';

求供应工程J1零件P1的供应商号SNO

SELECT SPJ.Sno FROM SPJ WHERE SPJ.Jno='J1' AND SPJ.Pno='P1';

求供应工程J1红色零件的供应商号SNO

SELECT SPJ.Sno FROM SPJ INNER JOIN P ON SPJ.Pno=P.Pno WHERE SPJ.Jno='J1' AND P.Color='红';

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

SELECT DISTINCT J.Jno FROM J WHERE J.Jno NOT IN( SELECT SPJ.Jno FROM SPJ INNER JOIN P ON SPJ.Pno=P.Pno INNER JOIN S ON SPJ.Sno=S.Sno WHERE S.City='天津' AND P.Color='红' );

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

要求这样的工程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 FROM SPJ AS SPJ3 WHERE SNO='S1'  AND NOT EXISTS (SELECT 1 FROM SPJ AS SPJ2 WHERE SPJ2.PNO=SPJ3.PNO AND SPJ2.JNO=SPJ1.JNO));

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

SELECT SPJ.Jno SELECT DISTINCT SPJ.Pno FROM SPJ WHERE SPJ.Sno='S1'

找出所有零件的名称、颜色、重量

SELECT P.Pname,P.Color,P.Weight FROM P;

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

SELECT P.Pname,SUM(SPJ.Qty) 数量 FROM SPJ INNER JOIN P ON SPJ.Pno=P.Pno WHERE SPJ.Jno='J2' GROUP BY P.Pname;

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

SELECT DISTINCT SPJ.Pno FROM S INNER JOIN SPJ ON S.Sno=SPJ.Sno WHERE S.City='上海'

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

SELECT DISTINCT J.Jname FROM S INNER JOIN SPJ ON S.Sno=SPJ.Sno INNER JOIN J ON SPJ.Jno=J.Jno WHERE S.City='上海'

把全部红色零件的颜色改成蓝色

UPDATE P SET P.Color='蓝' WHERE P.Color='红'

由S5供给J4的零件P6改为由S3供应

UPDATE SPJ SET SPJ.Sno='S3' WHERE SPJ.Sno='S5' AND SPJ.Jno='J4' AND SPJ.Pno='P6'

从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录

ALTER TABLE SPJ ADD CONSTRAINT FK_SPJ_S_SNO FOREIGN KEY(Sno) REFERENCES S(Sno) ON DELETE CASCADE ON UPDATE CASCADE DELETE FROM S WHERE S.Sno='S2'

请将(S2，J6，P4，200)插入供应情况关系

INSERT INTO S(Sno) VALUES('S2') INSERT INTO SPJ VALUES('S2','P4','J6',200)

请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图VSP完成下列查询： 

CREATE VIEW SJV AS SELECT SPJ.Sno,SPJ.Pno,SPJ.Qty FROM J INNER JOIN SPJ ON J.Jno = SPJ.Jno WHERE J.Jname = '三建'

(1)找出三建工程项目使用的各种零件代码及其数量。

SELECT SJV.Pno,SUM(SJV.Qty) 零件数量 FROM SJV GROUP BY SJV.Pno

(2)找出供应商S1的供应情况。

SELECT * FROM SJV WHERE SJV.Sno='S1'