南邮数据库实验1：SQL查询操作

题目：设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：

S(SNO，SNAME，STATUS，CITY)；

P(PNO，PNAME，COLOR，WEIGHT)；

J(JNO，JNAME，CITY)；

SPJ(SNO，PNO，JNO，QTY)。

供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成。

零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。

工程项目表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

今有若干数据如下：

S表：

P表：

J表：

SPJ表：

首先进行创建数据库、表，插入数据等操作。

创建数据库：

CREATE DATABASE ***;

创建数据表：

CREATE TABLE S(
SNO CHAR(10) PRIMARY KEY, --主码
SNAME CHAR(50) UNIQUE,    --唯一
STATUS INT,
CITY CHAR(20)
);

CREATE TABLE P(
PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(50),
COLOR CHAR(20),
WEIGHT INT
);

CREATE TABLE J(
JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(50) UNIQUE,
CITY CHAR(20)
);

CREATE TABLE SPJ(
SNO CHAR(10),
PNO CHAR(10),
JNO CHAR(10),
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),
);

开始查询操作：

（1）求供应工程J1零件的供应商号码SNO：

πSno(σJno=‘J1’（SPJ）)

SELECT * FROM SPJ
WHERE JNO='J1';

（2）求供应工程J1零件P1的供应商号码SNO：

πSno(σJno=‘J1’∧Pno=‘P1’(SPJ))

SELECT * FROM SPJ
WHERE JNO='J1' AND PNO='P1';

（3）求供应工程J1零件为红色的供应商号码SNO：

πSno(πSno,Pno（σJno=‘J1‘ (SPJ))∞πPno（σCOLOR=’红‘ （P）))

注意：用了嵌套查询。

SELECT * FROM SPJ
WHERE JNO='J1' AND PNO IN(
SELECT PNO FROM P
WHERE COLOR='红')

（4）求没有使用天津供应商生产的红色零件的工程号JNO：(这题答案有点问题)

πJno(SPJ)- πJNO（σcity=‘天津’∧Color=‘红‘ （S∞SPJ∞P）

SELECT J.JNO FROM P,J,SPJ
WHERE SPJ.JNO=J.JNO 
AND SPJ.PNO=P.PNO 
AND J.CITY!='天津' 
AND P.COLOR='红';

（5）求至少用了供应商S1所供应的全部零件的工程号JNO：

πJno，Pno(SPJ)÷πPno，Sno（σSno=‘S1‘ （SPJ））    注意：第二个投影要同时把Pno和Sno取出来，因为要由S1产生Pno

SELECT * FROM SPJ
WHERE SNO='S1'

P130页第五题：

（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 PNAME,QTY FROM P,SPJ
WHERE JNO='J2' AND P.PNO=SPJ.PNO

（5）找出上海厂商供应的所有零件号码；

SELECT DISTINCT PNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY='上海')

（6）找出使用上海产的零件的工程名称；

SELECT JNAME FROM J
WHERE JNO IN(
SELECT JNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY='上海'))

（7）找出没有使用天津产的零件的工程号码；

方法一：

SELECT DISTINCT JNO FROM SPJ
WHERE SNO!='S1' AND SNO!='S4'

方法二：更具有普适性

SELECT DISTINCT JNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY!='天津')

（8）把全部红色零件的颜色改成蓝色；

UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'
SELECT * FROM P

（9）由S5供给J4的零件P6改为由S3供应；

UPDATE SPJ
SET SNO='S3'
WHERE PNO='P6' AND JNO='J4' AND SNO='S5'

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

注意：这题要先删除SPJ表中的S2，再删除S表中的S2，否则会产生外键冲突。

DELETE FROM SPJ
WHERE SNO='S2';
SELECT * FROM SPJ

DELETE FROM S
WHERE SNO='S2';
SELECT * FROM S

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

INSERT INTO S VALUES
('S2','盛锡',10,'北京');
INSERT INTO SPJ VALUES
('S2','P4','J6',200);
SELECT * FROM SPJ

完整程序：

CREATE DATABASE ***;

CREATE TABLE S(
SNO CHAR(10) PRIMARY KEY, --主码
SNAME CHAR(50) UNIQUE,    --唯一
STATUS INT,
CITY CHAR(20)
);

CREATE TABLE P(
PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(50),
COLOR CHAR(20),
WEIGHT INT
);

CREATE TABLE J(
JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(50) UNIQUE,
CITY CHAR(20)
);

CREATE TABLE SPJ(
SNO CHAR(10),
PNO CHAR(10),
JNO CHAR(10),
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,'上海');
--SELECT * FROM S

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);
--SELECT * FROM p

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','半导体厂','南京');
--SELECT * FROM J

--多条数据同时插入，效率更高
INSERT INTO SPJ (SNO,PNO,JNO,QTY)
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);
--SELECT * FROM SPJ

--P71.(1)
SELECT * FROM SPJ
WHERE JNO='J1';

--P71.(2)
SELECT * FROM SPJ
WHERE JNO='J1' AND PNO='P1';

/*
SELECT * FROM SPJ,S,J,P
WHERE S.SNO=SPJ.SNO AND SPJ.JNO='J1' AND P.COLOR='红色'
*/

/*
SELECT * FROM SPJ,P
WHERE JNO='J1' AND COLOR IN(
SELECT COLOR FROM P
WHERE COLOR='红')
*/

--P72.(3) 嵌套查询
SELECT * FROM SPJ
WHERE JNO='J1' AND PNO IN(
SELECT PNO FROM P
WHERE COLOR='红')

--P72.(4)
SELECT * FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY!='天津')
OR PNO IN(
SELECT PNO FROM P
WHERE COLOR!='红')

--P72.(4) 正确结果
SELECT J.JNO FROM P,J,SPJ
WHERE SPJ.JNO=J.JNO 
AND SPJ.PNO=P.PNO 
AND J.CITY!='天津' 
AND P.COLOR='红';

--P72.(5)
SELECT * FROM SPJ
WHERE SNO='S1'

--P130.(1)
SELECT SNAME,CITY FROM S

--P130.(2)
SELECT PNAME,COLOR,WEIGHT
FROM P

--P130.(3)
SELECT JNO FROM SPJ
WHERE SNO='S1'

--P130.(4)
SELECT PNAME,QTY FROM P,SPJ
WHERE JNO='J2' AND P.PNO=SPJ.PNO

--P130.(5)
SELECT DISTINCT PNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY='上海')

--P130.(6)
SELECT JNAME FROM J
WHERE JNO IN(
SELECT JNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY='上海'))

--P130.(7)
SELECT DISTINCT JNO FROM SPJ
WHERE SNO!='S1' AND SNO!='S4'

--法二，更具有普适性
SELECT DISTINCT JNO FROM SPJ
WHERE SNO IN(
SELECT SNO FROM S
WHERE CITY!='天津')

--P130.(8)
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'
SELECT * FROM P

--P130.(9)
UPDATE SPJ
SET SNO='S3'
WHERE PNO='P6' AND JNO='J4' AND SNO='S5'

--P130.(10)
DELETE FROM SPJ
WHERE SNO='S2';
SELECT * FROM SPJ

DELETE FROM S
WHERE SNO='S2';
SELECT * FROM S

--P130.(11)
INSERT INTO S VALUES
('S2','盛锡',10,'北京');
INSERT INTO SPJ VALUES
('S2','P4','J6',200);
SELECT * FROM SPJ

版权声明：本文为博主原创文章，未经博主允许不得转载。