第三章作业

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

SELECT * FROM S WHERE A=10

SELECT A,B FROM S

SELECT A,B,S.C,S.D,E,F 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 A<E

SELECT S.C,S.D,T.*
FROM S,T

4.根据第2章习题6建立四张表 并完成题目中的查询

先建立表，然后插入数据

CREATE TABLE S        
 (	
 SNO CHAR(10) PRIMARY KEY,                  
 SNAME CHAR(10),
 STATUS INT,
 CITY CHAR(10)
 ); 
CREATE TABLE P
 (	
 PNO CHAR(10) PRIMARY KEY,
 PNAME CHAR(10),
 COLOR CHAR(10),
 WEIGHT INT
 ); 
 CREATE TABLE J
 (	
 JNO CHAR(10) PRIMARY KEY,
 JNAME CHAR(10),
 CITY CHAR(10)
 ); 
  CREATE TABLE SPJ
 (	
 SNO CHAR(10),
 PNO CHAR(10),
 JNO CHAR(10),
 QTY INT
 );
--插入数据
--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','为民',20,'上海');
SELECT * FROM S
--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);
SELECT * FROM P
--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','半导体厂','南京');
SELECT * FROM J
--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','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);
SELECT * FROM SPJ

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

SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1'

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

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

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

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

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

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

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

SELECT  PNO
FROM SPJ
WHERE SNO='S1'
--先选出S1供应的零件类型
SELECT  JNO
FROM SPJ 
WHERE PNO='P1' AND JNO IN
            (SELECT  JNO
             FROM SPJ 
			 WHERE PNO='P2'
			)

5.针对习题4中的4个表达式用SQL完成以下各项操作

（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 JNO='J2' AND SPJ.PNO=P.PNO

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

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

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

SELECT JNAME
FROM J
WHERE JNO IN(
			SELECT JNO
			FROM SPJ,S
			WHERE SPJ.SNO=S.SNO AND 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='红'
SELECT * FROM P

（9）由S5供给J4的零件P6改为由S3供应，请做必要的修改

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

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

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

删除前

删除后

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

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

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

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

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

SELECT PNO,QTY
FROM SJ

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

SELECT *
FROM SJ
WHERE SNO='S1'