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

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语句： 1） 2） 3）

DWH'100'(R)

XM,XB(R) ((R))

XM,DWHXB'女'4） RS 5）

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

2、设有如下三个关系： ——（易-易-难） A(A#,ANAME,WQTY,CITY) ： A#：商店代号；ANAME：商店名；WQTY:店员人数 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#; 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） （3） （4） （5） （6） （7） （8） （9）

查询至少有2名男生的班号；——（难） 查询不姓“王”的同学记录；——（易） 查询每个学生的姓名和年龄；——（难）

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

查询学生表的全部记录并按班号和年龄从大到小的顺序；——（中） 查询男教师及其所上的课程；——（中）

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

查询和“李军”同性别并同班的所有同学的姓名；——（中） 查询选修“数据库系统概论”课程的男同学的成绩表；——（中）

（10） 查询所有未讲课的教师的姓名和所在系别；——（难） （11） 查询“计算机系”教师所教课程的成绩表；——（难）

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

3 （13） 查询最低分大于70，最高分小于90的学生的学号；——（中） （14） 查询成绩在60到80之间的所有记录；——（中）

（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=’李军’);

（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=’无线电系’ 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;

4

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

R(A,B,C) S(A,D,E) T(D,F) 试用SQL语句实现如下操作：

1） R、S和T三个关系按关联属性建立一个视图R-S-T； 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——（易）

a) b)

解：

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

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） （5） （6） （7） （8） （9）

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

取出所在城市为上海的所有工程的全部细节；——（易） 取出重量最轻的零件代号；——（难）

取出为工程J1提供零件的供应商代号；——（易） 取出为工程J1提供零件P1的供应商代号；——（易） 取出由供应商S1提供零件的工程名称；——（易） 取出供应商S1提供的零件的颜色；——（易）

取出为工程J1或J2提供零件的供应商代号；——（中） 取出为工程J1提供红色零件的供应商代号；——（易） 建立一个视图V-SSC（SNO, SN, CNO, CN, G）；

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

为课程名，G

为成绩），试用

SQL

语言完成以下各题：

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

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

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

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

（16） 取出这样一些供应商代号，它们能够提供至少一种由红色零件的供应商提供的零件；——（难） （17） 取出由供应商S1提供零件的工程的代号；——（易）

（18） 取出所有这样的一些零件；——（难）

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

（20）解：

（1）（2）（3）（4）（5）（6）（7）（8）（9）（10）（11）（12）（13）（14）（15）（16）（17）（18）（19）指定的零件；——（难）

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

SELECT * FROM JB WHERE CITY=‘上海’; SELECT PN FROM PB

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

SELECT SN FORM SPJB WHERE JN=‘J1’;

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

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

SELECT DISTINCT COLOR FROM PB,SPJB WHERE SN=‘S1’AND SPJB.JN=JB.JN' SELECT SN FROM SPJB WHERE JN IN {J1, J2};

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

SELECT SN FROM SPJB,PB

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

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

SELECT SN FROM PB, JB, SPJB WHERE COLOR=‘红色’AND CITY IN {‘上海’，‘北京’} AND

PB.PN=SPJB.PN AND JB.JN=SPJB.JN;

SELECT PN FROM SB, JB , SPJB WEHRE SB.CITY=JB.CITY AND SB.SN=SPJB.SN AND

JB.JN=SPJB.JN;

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

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

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));

SELECT DISTINCT JN FROM SPJB WHERE JN NOT IN (SELECT DISTINCT SPJB.JN FROM

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

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=‘红色’;

SELECT JN FROM SPJB WHERE SN=’S1’;

SELECT DINSINCT SB.CITY , JB.CITY FROM SB, JB, SPJB WHERE SB.SN=SPJB.SN AND

JB.JN=SPJB.SN ;

SELECT SB.CITY, SPJB.PN, JB.CITY FROM SB,JB,SPJB WHERE SB.SN=SPJB.SN AND

JB.JN=SPJB.JN;

6

（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）； 读者关系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’ （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=’工业出版社’;