实验六 SQL进阶应用

根据数据库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'