数据库第三章习题

目录

3.有两个关系，S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式。
4.用SQL语句建四个表，完成查询：
（1）查询供应J1零件的SNO
（2）查询供应J1零件P1的供应商号码SNO
（3）供应工程J1零件为红色的供应商号码SNO
（4）没有使用天津供应商生产的红色零件的工
程号JNO
（5）至少用了S1供应的全部零件的工程号JNO
5.在建立的四个表中，完成以下查询。
（1）找出所有供应商的姓名和所在城市
（2）找出所有零件的名称，颜色，重量
（3）找出使用供应商S1所供应零件的工程号码
（4）找出工程项目J2使用的各种零件的
名称及其数量
（5）找出上海厂商供应的所有零件号码
（6）找出使用上海产的零件的工程名称
（7）找出没有使用天津产的零件的工程号码
（8）把全部红色零件的颜色改为蓝色
（9）由S5供给J4的零件P6改为由S3供应，
请做必要的修改
（10）从供应商关系中删除S2的记录，并从供
应情况关系中删除相应的记录
（11）请将（S2，J6，P4，200）插入供应
情况关系
9.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO），零件代码（PNO），供应数量（QTY）。针对该视图完成下列查询： （1）找出三建工程项目使用的各种零件代码及其数量 （2）找出供应商S1的供应情况**

3.有两个关系，S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式。

 SELECT *
 FROM S
 WHERE A=10;

SELECT A,B
FROM S;

SELECT *
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

SELECT *
FROM S,T
WHERE S.C=T.C;

SELECT *
FROM S,T
WHERE S.A<S.E;

SELECT *
FROM S,T
WHERE S.C=T.C AND T.D=S.D;

4.用SQL语句建四个表，完成查询：

首先建表

CREATE TABLE S
( SNO CHAR(2) UNIQUE,
SNAME CHAR(6),
STATUS CHAR(2),
CITY CHAR(4));

CREATE TABLE J
(JNO CHAR(2),
JNAME CHAR(8),
CITY CHAR(4)
);
CREATE TABLE P
(PNO CHAR(2) UNIQUE,
PNAME CHAR(6),
COLOR CHAR(2),
WEIGHT INT
);

CREATE TABLE SPJ
(SNO CHAR(2),
PNO CHAR(2),
JNO CHAR(2),
QTY INT);

插入数据

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','螺栓','绿',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);

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

（1）查询供应J1零件的SNO

SELECT SNO
FROM SPJ
WHERE JNO='J1';

（2）查询供应J1零件P1的供应商号码SNO

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

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

SELECT SNO
FROM SPJ,P
WHERE JNO='J1'AND SPJ.PNO=P.PNO AND COLOR='红';

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

SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT JNO
				FROM SPJ,P,S
				WHERE S.CITY='天津' AND COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO);
				

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

SELECT DISTINCT PNO
FROM SPJ
WHERE SNO='S1';
SELECT JNO
FROM SPJ
WHERE PNO='P1' AND JNO IN (SELECT JNO
							FROM SPJ
							WHERE PNO='P2');

[注意]：先查询S1供应的零件号（可以得出是P1和P2），再查询两者均使用的工程号

5.在建立的四个表中，完成以下查询。

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

SELECT SNAME,CITY
FROM S;

（2）找出所有零件的名称，颜色，重量

SELECT PNAME,COLOR,WEIGHT
FROM P;

（3）找出使用供应商S1所供应零件的工程号码

SELECT DISTINCT JNO
FROM SPJ
WHERE SNO='S1';

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

SELECT PNAME,QTY
FROM SPJ,P
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';

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

SELECT PNO
FROM SPJ,S
WHERE S.SNO=SPJ.SNO AND CITY='上海';

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

SELECT JNAME
FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO;

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

SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT DISTINCT JNO
					FROM SPJ,S
					WHERE S.SNO=SPJ.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 S
WHERE SNO='S2';
DELETE FROM SPJ
WHERE SNO='S2';

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

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

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

首先，创建视图

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

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

SELECT DISTINCT PNO,QTY
FROM SPJ_1;

（2）找出供应商S1的供应情况

SELECT DISTINCT PNO,QTY
FROM SPJ_1
WHERE SNO='S1';

完。

总结：
前几个比较简单，4.（4）和5.（7）用到了NOT IN；4.(5)要分步进行；5.（11）插入数据时若不指明顺序，需要按原表的顺序。