数据库系统概论第五版第二章习题6（SPJ数据库查询操作）

用SQL 语句建立第二章习题65 中的 4 个表。针对建立的 4 个表试用SQL 语言完成第二章习题 6 中的查询

( l ）求供应工程 Jl 零件的供应商号码 SNO ;

( 2 ）求供应工程 Jl 零件 Pl 的供应商号码 SNO ;

( 3 ）求供应工程 Jl 零件为红色的供应商号码 SNO。

使用的是mysql数据库，navicat连接并进行创建查询操作!

/*创建SPJ数据库*/
create database SPJ20211401R03;
USE spj20211401r03
/*创建并插入S表*/
create table S(
    `SNO` char(2) PRIMARY KEY,
    `SNAME` varchar(15),
    `STATUS` int(4),
    `CITY` varchar(10)
);
insert into S 
values    ('S1','精益',20,'天津'),
    ('S2','盛锡',10,'北京'),
    ('S3','东方红',30,'北京'),
    ('S4','丰泰盛',20,'天津'),
    ('S5','为民',30,'上海');

/*创建并插入SPJ表*/
create table SPJ(
    `SNO` char(2),
    `PNO` char(2),
    `JNO` char(2),
    `QTY` int(4)
);
insert into SPJ 
values
('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');

/*创建并插入P表*/
create table P(
    `PNO` char(2) PRIMARY KEY,
    `PNAME` varchar(15),
    `COLOR` char(3),
    `WEIGHT` int2

);
insert into P
values    ('P1','螺母','红',12),
    ('P2','螺栓','绿',17),
    ('P3','螺丝刀','蓝',14),
    ('P4','螺丝刀','红',14),
    ('P5','凸轮','蓝',40),
    ('P6','齿轮','红',30);

/*创建并插入J表*/
create table J(
    `JNO` char(2) PRIMARY KEY,
    `JNAME` varchar(15),
    `CITY` varchar(10)

);
insert into J
values    ('J1','三建','北京'),
    ('J2','一汽','长春'),
    ('J3','弹簧厂','天津'),
    ('J4','造船厂','天津'),
    ('J5','机车厂','唐山'),
    ('J6','无线电厂','常州'),
    ('J7','半导体厂','南京');
 

/*第(1)题*/
SELECT SNO
FROM SPJ
WHERE SPJ.JNO='J1';

/*第(2)题*/
SELECT SNO
FROM SPJ
WHERE SPJ.JNO='J1' AND SPJ.PNO = 'P1';

/*第(3)题*/
SELECT SNO
FROM P,SPJ
WHERE SPJ.PNO=P.PNO AND SPJ.JNO = 'J1' AND P.COLOR='红';
 

针对上题中的四个表试用SQL语言完成以下各项操作：

(1)找出所有供应商的姓名和所在城市。

(2)找出所有零件的名称、颜色、重量。

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

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

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

(6)出使用上海产的零件的工程名称。

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

(8)把全部红色零件的颜色改成蓝色。

(9)由S5供给J4的零件P6改为由S3供应。

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

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

/*46.第(1)题*/
SELECT sname,    city
FROM S;

/*46.第(2)题*/
SELECT pname,color,weight
FROM P;

/*46.第(3)题*/
SELECT jno
FROM SPJ
WHERE SNO='S1';

/*46.第(4)题*/
SELECT PNAME,QTY
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND SPJ.JNO='J2';

/*46.第(5)题*/
SELECT DISTINCT PNO
FROM S,SPJ
WHERE SPJ.SNO=S.SNO AND S.CITY = '上海';

/*46.第(6)题*/
SELECT JNAME
FROM J
WHERE JNO IN(
        SELECT JNO
        FROM S,SPJ
        WHERE S.SNO=SPJ.SNO AND S.CITY = '上海'
        );
        
/*46.第(7)题*/
SELECT JNO
FROM J
WHERE JNO NOT IN(
        SELECT JNO
        FROM  S,SPJ
        WHERE S.SNO=SPJ.SNO AND S.CITY='天津'
        );
        
/*46.第(8)题*/
UPDATE P
SET COLOR = '蓝'
WHERE COLOR = '红';

/*46.第(9)题*/
UPDATE SPJ
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';

/*46.第(10)题*/
DELETE
FROM S
WHERE SNO = 'S2';

DELETE
FROM SPJ
WHERE SNO = 'S2';

/*46.第(11)题*/
INSERT INTO SPJ
VALUES ('S2', 'P4', 'J6', 200);

S,P,J,SPJ各表修改后的状态如下：