SQL练习题

选自王珊的《数据库系统概论》第五版 教材

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

 S(SNO,SNAME,STATUS,CITY);

 P(PN0,PNAME,CLOLR,WEIGHT);

 J(JNO,JNAME,CITY);

 SPJ(SNO,PNO,JNO,QTY);

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

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

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

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

各表中均有若干数据，今有若干数据如下：

先建好四个表。

CREATE TABLE S(
SNO CHAR(3),
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10)
)
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
CLOLR CHAR(4),
WEIGHT INT
)
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10)
)
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT
)

再插入一些数据（这里选用了王珊的《数据库系统概论》第五版 教材配套的习题解析与实验指导上的数据）

J表：

S表

P表

 SPJ表

插入数据代码：

/*S表添加数据*/

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,'上海');

/*P表添加数据*/

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);

/*J表添加数据*/

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


/*SPJ表添加数据*/

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', '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', 'J4',500);

INSERT INTO SPJ VALUES('S2', 'P3', 'J5',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);

INSERT INTO SPJ VALUES('S1', 'P2', 'J4',700);

试用SQL语言完成如下查询：

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

SELECT DISTINCT SNO FROM SPJ WHERE  JNO='J1' 

 这个题王珊版教材的习题解析给出的代码是SELECT SNO FROM SPJ WHERE  JNO='J1' ，没有DISTINCT，但是这样会重复结果，后面的题也是一样的。

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

SELECT DISTINCT SNO 
FROM SPJ 
WHERE JNO='J1' AND PNO='P1' 

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

SELECT DISTINCT SNO FROM SPJ 
WHERE JNO='J1' AND 
PNO IN(SELECT PNO FROM P 
       WHERE CLOLR='红')

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

方法一：

SELECT  JNO
FROM J 
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.JNO=J.JNO
AND SNO IN(
SELECT SNO
FROM S
WHERE CITY='天津')
AND PNO IN
(SELECT PNO
FROM P
WHERE CLOLR='红'))

方法二：

SELECT JNO
FROM J
WHERE NOT EXISTS  
(SELECT *
 FROM SPJ,S,P
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO 
AND S.CITY='天津' AND P.CLOLR='红');

 (5)求至少用了S1供应商所供应的全部零件的工程号JNO；

SELECT 	DISTINCT  JNO
FROM 	SPJ  SPJZ
WHERE 	NOT  EXISTS
	(SELECT 	*
	FROM	SPJ  SPJX
	WHERE	SNO='S1'
    AND  NOT  EXISTS
		(SELECT  *
        FROM	 SPJ  SPJY
	    WHERE	 SPJX.PNO=SPJY.PNO
		AND  SPJY.JNO=SPJZ.JNO))

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

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

SELECT  SNAME, CITY

FROM  S;

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

SELECT  PNAME, CLOLR, 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='上海';

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

SELECT  JNO
FROM  J
WHERE NOT EXISTS
       (SELECT *
        FROM SPJ,S
        WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY ='天津')

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

UPDATE  P

SET  COLOR='蓝'

WHERE  COLOR='红';

(9)由S5供给J4的零件P6改为由S3供应，请作必要的修改。

UPDATE  SPJ

SET  SNO='S3'

WHERE  SNO='S5' AND JNO='J4' AND PNO='P6';

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

　 DELETE  FROM  SPJ

   WHERE  SNO=’S2’;

   DELETE  FROM  S

   WHERE  SNO=’S2’;

注意删除顺序，应该先从 SPJ表中删除供应商S2所供应零件的记录，然后从S表中删除S2。

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

方法一：

INSERT  INTO  SPJ(SNO, JNO, PNO, QTY)

VALUES  (S2,J6,P4,200);

方法二：注意一下顺序，建表的时候是SPJ(SNO,PNO,JNO,QTY),不能反了。

INSERT  INTO  SPJ

VALUES  (S2,P4,J6,200);

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

创建视图：

CREATE VIEW VSP AS SELECT SNO,SPJ.PNO,QTY FROM SPJ,J

WHERE SPJ.JNO=J.JNO AND J.JNAME='三建'

 

 或者用下面这个

CREATE  VIEW  V_SPJ  AS
SELECT  SNO, PNO, QTY
FROM  SPJ
WHERE  JNO  IN
　　(SELECT  JNO
FROM  J
WHERE  JNAME='三建');

结果一样。

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

SELECT  PNO, SUM(QTY) AS 数量
FROM  V_SPJ
GROUP BY PNO;

配套的习题解析给出的答案是

SELECT PNO,QTY  

FROM  VSP

 

这么看，P1,P3,P5有重复的。

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

SELECT  *

FROM VSP

WHERE SNO='S1'