SQL习题及答案

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 PRIMARY KEY(SNO,PNO,JNO),

CONSTRAINT FK_SNO FOREIGN KEY(SNO)REFERENCES S(SNO), CONSTRAINT FK_PNO FOREIGN KEY(PNO)REFERENCES P(PNO), CONSTRAINT FK_JNO FOREIGN KEY(JNO)REFERENCES J(JNO)

);

2.

SELECT * FROM J;

3.

SELECT * FROM J WHERE CITY='上海';

4.

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

5.

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

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' AND SPJ.PNO=P.PNO AND P.COLOR='红';