数据库第三章作业

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

（1）

SELECT*FROM S/*即选择满足A=10条件的元组*/
WHERE A=10;

（2）

SELECT A,B
 FROM S;/*投影A,B*/

(3)

SELECT A,B,S.C,S.D,E,F FROM S,T/*自然连接*/
WHERE S.C=T.C AND S.D=T.D;

（4）

SELECT */*等值连接*/
FROM S,T
WHERE S.C=T.C

(5)

SELECT */*非等值连接*/
FROM S,T
WHERE S.A<T.E;

(6)

SELECT S.C,S.D,T.*/*先投影再笛卡尔积*/
FROM S,T;

4.用SQL语句建立第二章习题6中的4个表；针对建立的4个表用SQL完成第2章习题6中的查询


建表：

CREATE TABLE S(SNO char(10) UNIQUE,SNAME char(10),STATUS int,CITY char(10));
CREATE TABLE P(PNO char(10) UNIQUE,pNAME CHAR(10),COLOR CHAR(5),WEIGHT int);
CREATE TABLE J(JNO CHAR(10) UNIQUE,JNAME CHAR(10),CITY CHAR(10));
CREATE TABLE SPJ (SNO char(10) ,PNO CHAR(10),JNO CHAR(10),QTY int);

INSERT INTO S VALUES ('S1','精益',20,'天津');
INSERT INTO S VALUES ('S2','盛锡',10,'北京');
INSERT INTO S VALUES ('S3','东方红',20,'北京');
INSERT INTO S VALUES ('S4','丰泰盛',20,'天津');
INSERT INTO S VALUES ('S5','为民',20,'上海');

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）

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

(2)

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

(3)

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

(4)
使用嵌套查找

SELECT JNO
FROM SPJ
WHERE SNO NOT IN/*注意是NOT IN*/
(
SELECT SNO
FROM S
WHERE CITY='天津'
)
AND PNO NOT IN
(SELECT PNO
FROM P
WHERE COLOR='红'
);

本题也可直接查询

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

（5)

SELECT DISTINCT JNO
FROM SPJJ
WHERE PNO>=ALL
(SELECT PNO
FROM SPJ
WHERE SNO='S1'
);

5.针对习题4中的4个表试用SQL语句完成以下各项操作
（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 SPJ,P
WHERE SPJ.PNO=P.PNO AND JNO='J2';

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

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

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

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

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

SELECT PNO
FROM S,SPJ
WHERE SPJ.SNO=S.SNO AND CITY!='天津';

(8)把全部红色零件颜色改为蓝色

UPDATE P
SET COLOR='蓝'
WHERE COLOR='红';

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

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

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

DELETE
FROM S
WHERE SNO='S2';
DELETE 
FROM SPJ
WHERE SNO='S2';

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

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

9.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO)、零件代码（PNO)、供应数量（QTY）

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

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

SELECT PNO,QTY
FROM VSPJ;

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

SELECT *
FROM VSPJ
WHERE SNO='S1';

第三章的作业就到此结束了，虽然有点多但题目也都很基础，没有那种很绕的，SQL语句的使用也学习的差不多了，再往下学应该就会容易些了。