数据库作业10：第三章课后题

(1)${\sigma }_{A=10}(S)$

SELECT *
FROM S
WHERE A=10;

(2)${\Pi }_{A,B}(S)$

SELECT A,B
FROM S;

(3)$S\bowtie T$
自然连接：在等值连接的前提下，把重复的目标列去掉

SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

(4)$S\underset{S.C=T.C}{\bowtie }T$

SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C ;

(5)$S\underset{A<E}{\bowtie }T$

SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.A<T.E ;

(6)${\Pi }_{C,D}(S)\times T$

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

4.

/*建表*/
CREATE TABLE S
(SNO char(5) PRIMARY KEY,
SNAME char(10),
STATUS int,
CITY char(10)
);
/*插入数据*/
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S1','精益',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S2','盛锡',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S3','东方红',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S5','为民',30,'上海');
/*查询表*/
SELECT *
FROM S;

CREATE TABLE P			/*建表*/
(PNO char(5) PRIMARY KEY,
PNAME char(10),
COLOR char(5),
WEIGHT int
);
/*插入数据*/
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P1','螺母','红',12);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P2','螺栓','绿',17);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P4','螺丝刀','红',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P5','凸轮','蓝',40);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES('P6','齿轮','红',30);
/*查询表*/
SELECT *
FROM P;

CREATE TABLE J			/*建表*/
(JNO char(5) PRIMARY KEY,
JNAME char(10),
CITY char(5)
);
/*插入数据*/
INSERT INTO J(JNO,JNAME,CITY) VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J4','造船厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J5','机车厂','唐山');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J6','无线电厂','常州');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J7','半导体厂','南京');
/*查询表*/
SELECT *
FROM J;

CREATE TABLE SPJ		/*建表*/
(SNO char(4),
PNO char(4),
JNO char(4),
QTY int
);
/*插入数据*/
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500);
/*查询*/
SELECT * FROM SPJ;

SELECT SNO
FROM SPJ
WHERE JNO='J1';
/*优化一下，去掉重复的*/
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1';

这时的查询结果会有重复行，想去掉重复行必须指定DISTINCT，如果没有指定DISTINCT关键词，就默认ALL（保留所有行）

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

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

先找出使用天津供应商生产的红色零件，然后用NOT IN 取不在这一集合的元组。

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

至少：双重否定NOT EXISTS

SELECT JNO
FROM SPJ
WHERE NOT EXISTS
  (SELECT *
  FROM S S1
  WHERE NOT EXISTS
     (SELECT *
     FROM S S2
     WHERE SPJ.SNO=S2.SNO AND S2.SNO='S1'
     )
  );

5.

SELECT SNAME,CITY
FROM S;

SELECT PNAME,COLOR,WEIGHT
FROM P;

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

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

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

/*去重*/
SELECT DISTINCT PNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海';

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

/*去重*/
SELECT DISTINCT JNAME
FROM SPJ,J,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海' AND SPJ.JNO=J.JNO;

SELECT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY <> '天津';

天津产的有S1,S4

重复的有点多，我感觉去重后的更清楚。

SELECT DISTINCT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY <> '天津';

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

SELECT * FROM P;

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

SELECT * FROM SPJ;

DELETE
FROM S
WHERE SNO='S2' ;

DELETE
FROM SPJ
WHERE SNO='S2' ;

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

属性列与建表的顺序相同

9.
建立视图

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

SELECT PNO,QTY
FROM IS_SPJ;

SELECT *
FROM IS_SPJ
WHERE SNO='S1';