SQL习题及答案

实验

设有以下关系模式： S(SNO，SNAME，CITY)

其中，S表示别SUPPLIER(供应者)，SNO为供应者代号，SNAME为供应者的名字，CITY为供应商所在的城市．主键为SNO。

P(PNO，PNAME，COLOR，WEIGHT)

其中，P表示PART(零件)，PNO为零件代号，PNAME为零件名，COLOR为零件颜色，WEIGHT为零件重量，主键为PNO。 J(JNO，JNAME，CITY)

其中，J表示JOB(工程)，JNO为工程编号，JNAME为工程名，CITY为工程所在城市，主键为JNO。 SPJ(SNO，PNO，JNO，QTY)

其中，SPJ表示供应关系，SNO是为指定工程提供零件的供应者代号，PNO是所提供的零件代号，JNO为工程编号，QTY表示提供的零件数量，主键为(SNO，PNO，JNO)，外部键分别为SNO，PNO，JNO。 试做以下各题：

1、用SQL的DDL语言创建S，P，J，SPJ四个基本表。

2、按照下面表格提供的数据，用SQL的插入语句插入所有记录。

3、给出下列各题的查询、存储等操作的语句序列，并且上机验证结果。

1)取出所有工程的全部细节；

2)取出所在城市为上海的所有工程的全部细节； 3)取出重量最轻的那些零件的号码； 4)取出为工程J1提供零件的供应者的代号； 5)取出为工程J1提供零件P1的供应者的代号； 6)取出由供应者S1提供零件的工程的名称； 7)取出由供应者S1提供的零件的颜色；

8)取出为工程J1和J2提供零件的供应者的代号； 9)取出为工程J1提供红色零件的供应者的代号； 10)取出为所在城市为上海的工程提供零件的供应者的代号；

11)取出为所在城市为上海或北京的工程提供红色零件的供应者的代号；

12)取出供应者与工程所在城市相同的供应者提供的零件的代号；

13)取出上海的供应者提供给上海的任一工程的零件的代号；

14)取出至少有一个和工程不在同一城市的供应者提

供零件的工程的代号；

15)取出上海供应者不提供任何零件的工程的代号； 16)取出这样一些供应者的代号，他们能够提供至少一种由红色零件的供应者提供的零件； 17)取出由供应者S1提供零件的工程的代号； 18)取出所有这样的三元组＜CITY，PNO，CITY＞，使得第一个城市的供应者为第二个城市的工程提供零件；

19)取出为所有工程提供同样零件的供应者的代号； 20)取出提供给上海的所有工程的零件代号； 21)取出至少需要供应者S1提供的所有零件的工程的代号；

22)把所有红色零件改成橙色；

23)删除所有红色零件和对应的SPJ记录； 24)取出供应者Sl提供的代号为P1的零件总数； 25)取出每个工程的供应商数；

26)取出所需的零件总数大于1000的工程代号；

1.

CREATE TABLE S(

SNO CHAR(5) PRIMARY KEY, SNAME CHAR(5), CITY CHAR(10)

);

CREATE TABLE P(

PNO CHAR(5) PRIMARY KEY, PNAME CHAR(5), COLOR CHAR(5), WEIGHT INT );

CREATE TABLE J(

JNO CHAR(5) PRIMARY KEY, JNAME CHAR(5), CITY CHAR(10) );

CREATE TABLE SPJ( SNO CHAR(5), PNO CHAR(5), JNO CHAR(5), QTY INT, CONSTRAINT PK_SPJ

KEY(SNO,PNO,JNO),

PRIMARY

CONSTRAINT CONSTRAINT CONSTRAINT ); 2.

SELECT * FROM J; 3.

FK_SNO FK_PNO FK_JNO

FOREIGN FOREIGN FOREIGN

KEY(SNO)REFERENCES S(SNO), KEY(PNO)REFERENCES P(PNO), KEY(JNO)REFERENCES J(JNO)

SELECT * FROM J WHERE CITY='上海'; 4. SELECT 5.

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

PNO

FROM

P

WHERE

WEIGHT=(SELECT MIN(WEIGHT) FROM P);

6.

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

SELECT DISTINCT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO='S1'; 8.

SELECT DISTINCT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO='S1; 9. SELECT

X.SNO

FROM

SPJ

X

WHERE

X.JNO='J1'AND EXISTS

(SELECT * FROM SPJ Y WHERE Y.SNO=X.SNO AND Y.JNO='J2'); 10.

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

AND

SPJ.PNO=P.PNO

AND

11.

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

SELECT DISTINCT SPJ.SNO FROM SPJ,J,P WHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO AND P.COLOR ='红' AND (J.CITY='上海' OR J.CITY=' 北京') ; 13.

SELECT DISTINCT P.PNO FROM S,J,P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY=J.CITY; 14.

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

15.

SELECT DISTINCT JNO FROM J WHERE EXISTS (SELECT

*

FROM

S,SPJ

WHERE

SPJ.SNO=S.SNO AND J.JNO=SPJ.JNO AND J.CITY<>S.CITY); 16.

SELECT DISTINCT JNO FROM J WHERE NOT EXISTS (SELECT

*

FROM

S,SPJ

WHERE

J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY='上海'); 17.

SELECT DISTINCT SNO FROM SPJ WHERE PNO IN

(SELECT DISTINCT PNO FROM SPJ WHERE SNO IN

(SELECT DISTINCT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR='红')); 18.

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

DISTINCT

S.CITY,SPJ.PNO,J.CITY

FROM S,J,SPJ WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY<>J.CITY; 20.

SELECT DISTINCT SNO FROM S WHERE NOT EXISTS

(SELECT * FROM J WHERE NOT EXISTS (SELECT

*

FROM

P,SPJ

WHERE

S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND J.JNO= SPJ.JNO)); 21.

SELECT DISTINCT PNO FROM P WHERE NOT EXISTS

(SELECT * FROM J WHERE J.CITY='上海' AND NOT EXISTS

(SELECT * FROM SPJ WHERE SPJ.PNO=P.PNO

AND SPJ.JNO=J.JNO)); 22.

SELECT DISTINCT JNO FROM SPJ SX WHERE NOT EXISTS

(SELECT * FROM SPJ SY WHERE SY.SNO='S1' AND NOT EXISTS (SELECT 23.

UPDATE P SET COLORE='橙' WHERE COLORE='红'; 24.

DELETE FROM SPJ WHERE SPJ.PNO IN (SELECT PNO FROM P WHERE COLOR='红'); DELETE FROM P WHERE COLOR='红'; 25.

SELECT SUM(QTY) FROM SPJ WHERE SNO='S1' AND PNO='P1';

*

FROM

SPJ

SZ

WHERE

SY.PNO=SZ.PNO AND SX.JNO=SZ.JNO));

26.

SELECT JNO,COUNT(DISTINCT SNO) FROM SPJ GROUP BY JNO; 27.

SELECT JNO FROM SPJ HAVING SUM(QTY)>1000; 1. //创建s表

GROUP JNO BY CREATE TABLE S

(SNO CHAR(8) NOT NULL UNIQUE,

SNAME VARCHAR(20) NOT NULL UNIQUE, CITY VARCHAR(8),

CONSTRAINT CS PRIMARY KEY(SNO)); //创建p表 CREATE TABLE P

(PNO CHAR(8) NOT NULL UNIQUE,

PNAME VARCHAR(20) NOT NULL UNIQUE, COLOR CHAR(1), WEIGHT INT,

CONSTRAINT CP PRIMARY KEY(PNO)); //创建j表 CREATE TABLE J

(JNO CHAR(8) NOT NULL UNIQUE,

JNAME VARCHAR(20) NOT NULL UNIQUE, CITY VARCHAR(8),

CONSTRAINT CJ PRIMARY KEY(JNO)); //创建spj表 CREATE TABLE SPJ

(SNO CHAR(8) NOT NULL NUIQUE, PNO CHAR(8) NOT NULL UNIQUE,

JNO CHAR(8) NOT NULL UNIQUE, QTY INT,

CONSTRAINT CSPJ PRIMARY KEY(SON,PNO,JNO),

CONSTRAINT CSPJ FOREING KEY(SNO) REFERENCES S(SNO),

CONSTRAINT CSPJ FOREING KEY(PNO) REFERENCES P(PNO),

CONSTRAINT CSPJ FOREING KEY(JNO) REFERENCES J(JNO)); 2.

//向s表插入数据

INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S1’,’N1’,’上海’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S2’,’N2’,’北京’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S3’,’N3’,’北京’);

INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S4’,’N4’,’上海’); INSERT INTO S(SNO,SNAME,CITY) VALUES(‘S5’,’N5’,’南京’); //向p表插入数据

INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P1’,’PN1’,’红’,’12’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P2’,’PN2’,’绿’,’18’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P3’,’PN3’,’蓝’,’20’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P4’,’PN4’,’红’,’13’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P5’,’PN5’,’蓝’,’11’); INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES(‘P6’,’PN6’,’红’,’15’); //向j表插入数据

INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J1’,’JN1’,’上海’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J2’,’JN2’,’广州’);

INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J3’,’JN3’,’南京’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J4’,’JN4’,’南京’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J5’,’JN5’,’上海’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J6’,’JN6’,’武汉’); INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J7’,’JN7’,’上海’); //向表spj中插入数据

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S1’,’P1’,’J1’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S1’,’P1’,’J4’,’700’); 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’,’J3’,’200’);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J4’,’500’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J5’,’600’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J6’,’400’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P3’,’J7’,’800’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S2’,’P5’,’J2’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S3’,’P3’,’J1’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S3’,’P4’,’J2’,’500’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S4’,’P6’,’J7’,’300’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P2’,’J2’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P2’,’J4’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J5’,’500’);

INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J7’,’100’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P6’,’J2’,’200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P1’,’J4’,’1000’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P3’,’J4’,’1200’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P4’,’J4’,’800’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P5’,’J4’,’400’); INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES(‘S5’,’P6’,’J4’,’500’); 3.1

SELECT * FROM J; 3.2

SELECT * FROM J WHERE CITY LIKE ‘上海’; 3.3

SELECT PNO FROM P WHERE WEIGHT=(SELECT MAX(WEIGHT)FROM P);

3.4

SELECT SNO FROM SPJ WHERE JNO IN(SELECT JNO FROM J WHERE JNO=‘J1’); 3.5

SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’; 3.6

SELECT JNAME FROM J,SPJ WHERE J.JNO=SPJ.JNO AND SPJ.SNO=‘S1’; 3.7

SELECT COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO=‘S1’; 3.8

SELECT DISTINCT SNO FROM SPJ WHERE JNO=‘J1’ OR JNO=‘J2’; 3.9

SELECT SNO FROM SPJ,P WHERE

P.PNO=SPJ.PNO AND SPJ.JNO=‘J1’ AND P.COLOR=‘红’’ 3.10

SELECT SNO FROM SPJ,J WHERE J.JNO=SPJ.JNO AND J.CITY=‘上海’;

3.11

SELECT SNO FROM SPJ,J,P

WHERE SPJ.PNO=P.PNO AND J.JNO=SPJ.JNO AND P.COLOR=‘红’; 3.12

SELECT SPJ.PNO FROM SPJ,S,J

WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=J.CITY; 3.13

SELECT SPJ.PNO FROM SPJ,S,J

WHERE S.SNO=SPJ.SNO AND J,JNO=SPJ.JNO AND S.CITY=‘上海’ AND J.CITY=‘上海’; 3.14

SELECT SPJ.JNO FROM SPJ,S,J

WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY<>J.CITY; 3.15

SELECT JNO FROM SPJ WHERE JNO NOT IN (SELECT SPJ.JNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY =‘上海’); 3.16

SELECT SPJ.SNO FROM SPJ,P WHERE SPJ.PNO IN

(SELECT SPJ.PNO FROM SPJ,S,P

WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND P.COLOR=‘红’); 3.17

SELECT SPJ.JNO FROM S,P,SPJ

WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO; 3.18

SELECT S.CITY,J.CITY FROM S,J,SPJ

WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO; 3.19

SELECT S.CITY,SPJ.PNO,J.CITY FROM S,J,SPJ WHERE S ,=SPJ.SNO AND J.JNO =SPJ.JNO; 3.20

SELECT S.CITY,SPJ.PNO,J.CITY FROM SPJ,S,J WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY<>J.CITY; 3.21

SELECT JNO FROM SPJ AS SPJ1 WHERE NOT EXISTS(

SELECT 1 FROM SPJ AS SPJ2

WHERE SNO=‘S1’ AND NOT EXISTS

(SELECT 1 FROM SPJ AS SPJ3

WHERE SPJ3.PNO=SPJ2.PNO AND SPJ3.JNO=SPJ1.JNO)); 3.22

UPDATE P SET COLOR=‘红’ WHERE COLOR=‘橙’; 3.23

DELETE FROM SPJ,P

WHERE SPJ.PNO=P.PNO AND P.COLOR=‘红’;

DELETE FROM P WHERE COLOR=‘红’; 3.24

SELECT SPJ.SNO,SPJ,PNO,SUM(QTY) FROM SPJ WHERE SPJ.SNO=‘S1’ AND SPJ.PNO=‘P1’ GROUP BY SPJ.SNO,SPJ,PNO; 3.25

SELECT SPJ.SNO,COUNT(*) FROM SPJ GROUP BY SPJ.SNO; 3.26

SELECT JNO FROM SPJ GROUP BY SNO HAVING SUM(QTY)>1000;

本文来源：https://www.bwwdw.com/article/pkiw.html