SQL语句与关系代数习题

一、选择题

1、SQL语言是（ ）语言。——（SQL特点）（易）

A)层次数据库 B)网络数据库 C)关系数据库 D)非数据库

答案：C

2、SQL语言具有两种使用方式，分别称为交互式SQL和（ ）。

——（SQL语言使用方式）（易）

A) 提示式SQL B)多用户SQL C)嵌入式SQL D)解释式SQL

答案：C

3-4-5（ ）包括数据库模式定义和数据库存储结构与存取方法定义。（ ）实现对DB的操作，包括查询、插入、删除、修改数据库中的数据。（ ）用于数据保护，包括数据的安全性，完整性，并发控制和恢复等。——（数据库语言 DDL DML DCL）（中）

A) 数据控制子语言 B)数据定义子语言 C)数据操纵子语言 D)数据库语言

答案：B C A

2

6-7-8-9-10、下列SQL语句中，实现数据检索的语句是（ ），修改表结构的是（ ），修改属性值的是（ ），删除表结构的是（ ），删除表记录的是（ ）。

——（DROP TABLE, ALTER TABLE,UPDATE, DELETE,SELECT 语句）（难）

A)SELECT B)DROP C)UPDATE D)ALTER E)DELETE

答案：A D C B E

二、用关系代数表达式及SQL语句描述关系查询

1、

设有如下关系表R、S和T：

——（易）

R(BH, XM, XB, DWH)

S(DWH, DWM)

T(BH, XM, XB, DWH)

写出实现下列关系代数的SQL语句：

DWH'100'(R)

1）

3

2） 3） 4） 5）

解：

XM,XB(R)

XM,DWH(XB'女'(R))RS

XM,XB,DWH(XB'男'(RS))

1） SELECT * FROM R WHERE DWH=’100’;

2） SELECT XM,XB FROM R;

3） SELECT XM,DWH FROM R WHERE XB=’女’;

4） SELECT R.*,S.DWM FROM R, S WHERE R.DWH=S.DWH;

5） 男’;

SELECT XM,XB,DWH FROM R,S WHERE R.DWH=S.DWH AND XB=’

2、设有如下三个关系： ——（易-易-难）

A(A#,ANAME,WQTY,CITY) ： A#：商店代号；ANAME：商店名；WQTY:店员人数

4

B(B#,BNAME,PRICE) ：B#：商品号；BNAME：商品名称；

AB(A#,B#,QTY) ：QTY：商品数量

试用关系代数和SQL语言写出下列查询。

1） 找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名；

2） 找出供应书包的商店名；

3） 找出至少供应代号为256的商店所供应的全部商品的商店名和所在城市。

解： 1）A#,ANAME(WQTY100 OR CITY'长沙'(A))

SELECT A#,ANAME FROM A WHERE WQTY<=100 OR CITY=’长沙’; 2）ANAME((BNAME'书包'(B))ABA)

SELECT ANAME FROM A,B,AB

WHERE BNAME=’书包’ AND B.B#=AB.B# AND AB.A#=A.A#;

5

3）

ANAME,CITY(ABB#(A#'256'(AB))A)

SELECT ANAME,CITY FROM A WHERE NOT EXISTS

(SELECT * FROM AB AB1 WHERE AB1.A#=’256’ AND NOT EXISTS

(SELECT * FROM AB AB2 WHERE AB2.A#=A.A# AND AB2.B#=AB1.B#));

3. 设有如下关系模式：

student(NO, NAME , SEX ,BIRTHDAY, CLASS)

teacher(NO,NAME,SEX,BIRTHDAY,PROF,DEPART) PROF为职称，DEPART为系别

course(CNO, CNAME, TNO)

score(NO, CNO, DEGREE) DEGREE 为成绩

写出实现以下各题功能的SQL语句：

（1） 查询至少有2名男生的班号；——（难）

（2） 查询不姓“王”的同学记录；——（易）

6

（3） （4） （5） （6） （7） （8） （9） （10） （11） （12） —（难）

（13） （14） 查询每个学生的姓名和年龄；——（难）

查询学生中最大和最小的birthday日期值；——（中）

查询学生表的全部记录并按班号和年龄从大到小的顺序；——（中）

查询男教师及其所上的课程；——（中）

查询最高分同学的学号，课程号和成绩；——（中）

查询和“李军”同性别并同班的所有同学的姓名；——（中）

查询选修“数据库系统概论”课程的男同学的成绩表；——（中）

查询所有未讲课的教师的姓名和所在系别；——（难）

查询“计算机系”教师所教课程的成绩表；——（难）

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录；—

查询最低分大于70，最高分小于90的学生的学号；——（中）

查询成绩在60到80之间的所有记录；——（中）

7

（15） 查询成绩比该课程平均成绩低的同学的成绩表；——（相关子查询）（难）

（16） 查询所有女教师和女同学的姓名、性别和生日；——（中）

（17） 查询“计算机系”和“无线电系”不同职称的教师的姓名和职称；——（中）

解：（1）SELECT CLASS FROM student WHERE SEX=‘男’

GROUP BY CLASS HAVING COUNT(*)>=2;

（2）SELECT * FROM student WHERE NAME NOT LIKE ‘王*’;

（3）SELECT NAME，year(date())-year(birthday) as age FROM student;

（4）SELECT MAX(BIRTHDAY), MIN(BIRTHDAY) FROM student ;

（5）SELECT * FROM student ORDER BY CLASS,BIRTHDAY DESC;

（6）SELECT x.name, y.cname FROM teacher x, course y WHERE x.no=y.tno and x.sex=’男’;

（7）SELECT * FROM score WHERE degree=(SELECT max(degree) FROM score);

（8）SELECT name FROM student WHERE sex=(SELECT sex FROM student WHERE name=’李军’ ) and class=(SELECT class FROM student WHERE name=’李军’);

8

（9）SELECT * FROM score WHERE no IN(SELECT no FROM student WHERE sex=‘男’) and cno=(SELECT cno FROM course WHERE cname=‘数据库系统概论’);

（10）SELECT name, depart FROM teacher t WHERE NOT EXIST (SELECT * FROM course c WHERE c.tno=t.no);

（11）SELECT * FROM score s, teacher t, course c WHERE t.depart=’计算机系’ and t.no=c.tno and c.cno=score.cno;

（12）SELECT * FROM student s, score sc WHERE s.no=sc.no and cno=’3-105’ and degree>(SELECT degree FROM sc WHERE no=’109’ and cno=’3-105’);

（13）SELECT no FROM score GROUP BY no HAVING min(degree)>70 and max(degree)<90;

（14）SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

（15）SELECT * FROM score a WHERE degree < (SELECT avg(degree) FROM score b WHERE b.cno=a.cno group by b.cno);

（16）SELECT name, sex, birthday FROM teacher WHERE sex=‘女’UNION SELECT name, sex, birthday FROM student WHERE sex=‘女’;

(17) SELECT name, prof FROM teacher WHERE depart=’计算机系’ OR depart=’无线电系’

9

order by prof;

4、设有图书登记表TS，具有属性：BNO(图书编号)，BC(图书类别)，BNA(书名)，AU(著者)，PUB（出版社）。按下列要求用SQL语言进行设计。 ——（易）

1） 按图书馆编号BNO建立TS表的索引ITS；

2） 查询按出版社统计其出版图书总数。

3） 删除索引。

解：1）CREATE INDEX ITS ON TS (BNO);

2） SELECT PUB,COUNT(BNO) FROM TS GROUP BY PUB;

3） DROP INDEX ITS;

5、已知三个关系R、S和T ——（中）

R(A,B,C) S(A,D,E) T(D,F)

试用SQL语句实现如下操作：

1） R、S和T三个关系按关联属性建立一个视图R-S-T；

10

2）对视图R-S-T按属性A分组后，求属性C和E的平均值。

解：1）CREATE VIEW R-S-T(A,B,C,D,E,F) AS

SELECT R.A , B, C ,S.D, E, F FROM R, S, T

WHERE R.A=S.A AND S.D=T.D;

2） SELECT AVG(C), AVG(E) FROM R-S-T GOUPY BY A;

6、设有学生表S(SNO, SN) （SNO为学生号，SN为姓名） 和学生选修课程表SC(SNO,CNO,CN,G)

（CNO为课程号，CN为课程名，G为成绩），试用SQL语言完成以下各题： ——（易）

a) 建立一个视图V-SSC（SNO, SN, CNO, CN, G）；

b) 从视图V-SSC上查询平均成绩在90分以上的SN, CN 和G。

解：

1） CREATE VIEW V-SSC(SNO , SN, CNO, CN, G) AS

SELECT S.SNO, SN, CNO, CN, G FROM S, SC WHERE S.SNO=SC.SNO

11

2） SELECT SN, CN, G FROM V-SSC GROUP BY SNO HAVING AVG(G)>90

7、设有关系模式：

SB( SN , SNAME, CITY) 其中SB表示供应商，SN为供应商号，SNAME为供应商名字，CITY为供应商所在城市；

PB(PN, PNAME, COLOR, WEIGHT) 其中PB表示零件，PN为零件代号，PANME为零件名字，COLOR为零件颜色，WEIGHT为零件重量；

JB( JN, JNAME, CITY) 其中JB表示工程，JN为工程编号，JNAME为工程名字，CITY为工程所在城市；

SPJB（SN, PN, JN, QTY）其中SPJB表示供应关系，QTY表示提供的零件数量。

写出实现以下各题功能的SQL语句：

（1） 取出所有工程的全部细节；——（易）

（2） 取出所在城市为上海的所有工程的全部细节；——（易）

（3） 取出重量最轻的零件代号；——（难）

（4） 取出为工程J1提供零件的供应商代号；——（易）

12

（5） （6） （7） （8） （9） （10） （11） （12） （13） （14） （15） （16） 零件；——（难）

取出为工程J1提供零件P1的供应商代号；——（易）

取出由供应商S1提供零件的工程名称；——（易）

取出供应商S1提供的零件的颜色；——（易）

取出为工程J1或J2提供零件的供应商代号；——（中）

取出为工程J1提供红色零件的供应商代号；——（易）

取出为所在城市为上海的工程提供零件的供应商代号；——（易）

取出为所在城市为上海或北京的工程提供红色零件的供应商代号；——（中）

取出供应商与工程所在城市相同的供应商提供的零件代号；——（中）

取出上海的供应商提供给上海的任一工程的零件的代号；——（难）

取出至少有一个和工程不在同一城市的供应商提供零件的工程代号；——（难）

取出上海供应商不提供任何零件的工程的代号；——（难）

取出这样一些供应商代号，它们能够提供至少一种由红色零件的供应商提供的

13

（17） 取出由供应商S1提供零件的工程的代号；——（易）

（18） 取出所有这样的一些个城市的工程提供零件；——（难）

（19） 取出所有这样的三元组，使得第一个城市的供应商为第二

个城市的工程提供指定的零件；——（难）

（20） 重复（19）题，但不检索两个CITY值相同的三元组。——（难）

解：

（1） SELECT * FROM JB;

（2） SELECT * FROM JB WHERE CITY=‘上海’;

（3） SELECT PN FROM PB

WHERE WEIGHT=(SELECT MIN(WEIGHT) FROM PB);

（4）

SELECT SN FORM SPJB WHERE JN=‘J1’;

（5） SELECT SN FORM SPJB WHERE JN=‘J1’AND PN=‘P1’;

14

（6） SELECT JNAME FROM JB,SPJB WHERE SN=‘S1’AND SPJB.JN=JB.JN;

（7） SPJB.JN=JB.JN'

SELECT DISTINCT COLOR FROM PB,SPJB WHERE SN=‘S1’AND

（8） SELECT SN FROM SPJB WHERE JN IN {J1, J2};

或者 SELECT SN FROM SPJB WHERE JN=’J1’ OR JN=’J2’;

（9） SELECT SN FROM SPJB,PB

WHERE COLOR=‘红色’AND PB.PN=SPJB.PN AND JN=’J1’;

（10） JB.JN=SPJB.JN;

SELECT DISTINCT SN FROM SPJB,JB WHERE CITY=‘上海’AND

（11） SELECT SN FROM PB, JB, SPJB WHERE COLOR=‘红色’AND CITY IN {‘上

海’，‘北京’} AND PB.PN=SPJB.PN AND JB.JN=SPJB.JN;

（12） SELECT PN FROM SB, JB , SPJB WEHRE SB.CITY=JB.CITY AND

SB.SN=SPJB.SN AND JB.JN=SPJB.JN;

（13） SELECT PN FROM SB, SPJB, JB WEHRE SB.CITY=‘上海’AND JB.CITY=‘上

海’AND SB.SN=SPJB.SN AND JB.JN=SPJB.JN;

15

（14） SELECT JN FROM JB WHERE EXISTS ( SELECT * FROM SB WHERE

EXISTS(SELECT * FROM SPJB WHERE SB.CITY<>JB.CITY AND SPJB.SN= SB.SN AND SPJB.JN= JB.JN));

（15） SELECT DISTINCT JN FROM SPJB WHERE JN NOT IN (SELECT DISTINCT

SPJB.JN FROM SB,SPJB WHERE SB.SN=SPJB.SN AND SB.CITY=‘上海’);

（16） SELECT DISTINCT SPJB.SN FROM SB,SPJB WHERE SPJB.PN IN (SELECT

SPJB.PN FROM SPJB,PB WHERE PB.PN=SPJB.PN AND PB.COLOR=‘红色’;

（17） SELECT JN FROM SPJB WHERE SN=’S1’;

（18） SELECT DINSINCT SB.CITY , JB.CITY FROM SB, JB, SPJB WHERE

SB.SN=SPJB.SN AND JB.JN=SPJB.SN ;

（19） SELECT SB.CITY, SPJB.PN, JB.CITY FROM SB,JB,SPJB WHERE

SB.SN=SPJB.SN AND JB.JN=SPJB.JN;

（20） SELECT DISTINCT SB.CITY, SPJB.PN, JB.CITY FROM SB,JB,SPJB WHERE

SB.SN=SPJB.SN AND JB.JN=SPJB.JN AND SB.CITY<>JB.CITY;

8、设有如下关系模式： ——（中）

图书关系B（图书编号B#，图书名T，作者A，出版社P）；

16

读者关系R（借书证号C#，读者名N，读者地址D）；

借阅关系L（C#，B#，借书日期E，还书标志BZ）；

BZ=‘1’表示已还； BZ=‘0’ 表示未还；

写出实现以下各题功能的SQL语句：

（1） 查询“工业出版社”出版的图书名

（2） 将书号为B5的图书的出版社改为“工业出版社”

（3） 查询99年12月31日以前借书未还的读者名与书名

（4） 查所借的书包含借书证号为C1的读者借出未还的所有书的读者名与借书证号。

（5） 删去“工业出版社”出版的所有图书及相关的借阅信息。

解：

（1）Select T from B Where P = ’工业出版社’

（2）Update B Set P=’工业出版社’ Where B# = ’B5’

17

（3）Select N , T From B, R , L

Where E <’99/12/31’ AND BZ=’0’ AND

L.C#=R.C# AND L.B#=B.B#

（4）select N,C# from R where not exists

(select * from L L1 where L1.C#=’c1’ and BZ=‘0’ and not exists

(select * from L L2 where L2.c#=R.c#

and L2.B#=L1.B#))

（6） Delete from L Where B# IN ( Select B# From B

Where P=’工业出版社’);

Delete from B Where P=’工业出版社’;