数据库第三章作业：习题

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 S.*,T.*
FROM S,T
WHERE S.C=T.C;

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

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

4

CREATE TABLE S
( SNO CHAR(10) PRIMARY KEY,
  SNAME CHAR(10),
  STATUS SMALLINT,
  CITY CHAR(10)
  );
CREATE TABLE P
( PNO CHAR(10)PRIMARY KEY,
PNAME CHAR(10),
COLOR CHAR(10),
WEIGHT SMALLINT
);
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 SMALLINT,
 FOREIGN KEY(SNO) REFERENCES S(SNO),
 FOREIGN KEY(PNO) REFERENCES P(PNO),
 FOREIGN KEY(JNO) REFERENCES J(JNO)
 );
 
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);

5

SELECT SNO, CITY
FROM S;

SELECT PNO,COLOR, WEIGHT
FROM P;

SELECT PNO
FROM SPJ
WHERE SNO='S1';

SELECT PNO,QTY
FROM SPJ
WHERE JNO='J2';

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

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

SELECT JNO
FROM SPJ
WHERE NOT EXISTS
      (SELECT *
      FROM S
      WHERE CITY='天津');

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

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

DELETE
FROM S
WHERE SNO='S2';

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

查询结果依次向下

9

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


SELECT SNO,QTY
FROM SPQ_SPJ;


SELECT *
FROM SPQ_SPJ
WHERE SNO='S1';