【例2

Size: px

Start display at page:

Download "【例2"

能歧别
7 years ago
Views:

1 第 3 章关系数据库标准语言 SQL 语言本章学习要点关系数据库的标准语言 SQL 语言的特点和组成 SQL 定义语句 SQL 查询语句 SQL 更新语句视图的概念和操作嵌入式 SQL 语句的应用重点 :SQL 的数据定义查询和更新功能难点 : 复杂的查询功能, 特别是嵌套查询 3.1 基本知识点知识领域一级知识点二级知识点掌握程度 SQL 基本概念视图基本表理解 SQL 标准存储文件了解模式定义模式删除基本表定义表删除表修改数数据定义关系数据库标据类型索引定义索引删除唯一性索引聚簇索引掌握准语言 SQL 数据查询单表查询多表查询嵌套查询聚集函数集合查询掌握数据更新插入删除修改掌握视图定义删除查询更新视图作用掌握理解单项选择题 3.2 例题分析例 3.1 SQL 语言是语言, 易学 A. 过程化 B. 非过程化 C. 格式化 D. 导航式答 :SQL 是一种介于关系代数与关系演算之间的结构化查询语言, 它是高度非过程化的本题答案为 B 例 3.2 SAL 语言是语言 A. 层次数据库 B. 网络数据库 C. 关系数据库 D. 非数据库答 :SQL 是一种关系数据库标准语言本题答案为 C 例 3.3 SQL 语言具有的功能 A. 关系规范化, 数据操纵, 数据控制 B. 数据定义, 数据操纵, 数据控制 1

2 C. 数据定义, 关系规范化, 数据控制 D. 数据定义, 关系规范化, 数据操纵第 3 章关系数据库标准语言 SQL 答 :SQL 语言自身不具备关系规范化功能本题答案为 B 例 3.4 SQL 语言具有两种使用方式, 分别称为交互式 SQL 和 A. 提示式 SQL B. 多用户 SQL C. 嵌入式 SQL D. 解释式 SQL 答 :C 例 3.5 SQL 语言中, 实现数据检索的语句是 A.SELECT B.INSERT C.UPDATE D.DELETE 答 :A 例 3.6 下列 SQL 语句中, 修改表结构的是 A.ALTER B.CREATE C.UPDATE D.INSERT 答 :A 例 3.7 SQL 中, 与 NOT IN 等价的操作符是 A.=SOME B.<>SOME C.=ALL D.<>ALL 答 :D 第 8-11 题基于这样的三个表, 即学生表 S 课程表 C 和学生选课表 SC, 它们的结构如下 : S(S#,SN,SEX,AGE,DEPT) C(C#,CN) SC(S#,C#,GRADE) 其中,S# 为学号,SN 为姓名,SEX 为性别,AGE 为年龄,DEPT 为系别,C# 为课程号,CN 为课程名,GRADE 为成绩例 3.8 检索所有比王华年龄大的学生姓名年龄和性别正确的 SELECT 语句是 A.SELECT SN,AGE,SEX WHERE AGE>(SELECT AGE WHERE SN= 王华 ) B.SELECT SN,AGE,SEX WHERE SN= 王华 C.SELECT SN,AGE,SEX WHERE AGE>(SELECT AGE WHERE SN= 王华 ) D.SELECT SN,AGE,SEX WHERE AGE > 王华.AGE 答 :A 2

3 例 3.9 检索选修课程 C2 的学生中成绩最高的学生的学号正确的 SELECT 语句是 A. SELECT S# C WHERE C#= C2 AND GRADE >= (SELECT GRADE C WHERE C#= C2 ) B. SELECT S# C WHERE C#= C2 AND GRADE IN (SELECT GRADE C WHERE C#= C2 ) C. SELECT S# C WHERE C#= C2 AND GRADE NOT IN (SELECT GRADE C WHERE C#= C2 ) D. SELECT S# C WHERE C#= C2 AND GRADE >= ALL (SELECT GRADE C WHERE C#= C2 ) 答 :D 例 3.10 检索学生姓名入其所选修课程的课程号和成绩正确的 SELECT 语句是 A. SELECT S.SN, SC.C#, SC.GRADE WHERE S.S#=SC.S# B. SELECT S.SN, SC.C#, SC.GRADE C WHERE S.S#=SC.S# C. SELECT S.SN, SC.C#, SC.GRADE, SC WHERE S.S#=SC.S# D. SELECT S.SN, SC.C#, SC.GRADE, SC 答 :C 例 3.11 检索选修 4 门以上的课程的学生总成绩 ( 不统计不及格的课程 ), 并要求按总成绩的降序排列出来正确的 SELECT 语句是 A. SELECT S#, SUM (GRADE) C WHERE GRADE>=60 GROUP BY S# ORDER BY 2 DESC HAVING COUNT (*) >=4 3

4 B. SELECT S#, SUM (GRADE) C WHERE GRADE>=60 GROUP BY S# HAVING COUNT (*) >=4 ORDER BY 2 DESC C. SELECT S#, SUM (GRADE) C WHERE GRADE>=60 HAVING COUNT (*) >=4 GROUP BY S# ORDER BY 2 DESC D. SELECT S#, SUM (GRADE) C WHERE GRADE>=60 ORDER BY 2 DESC GROUP BY S# HAVING COUNT (*) >=4 答 :B 例 3.12 假定学生关系是 S(S#,SNAME,SEX,AGE), 课程关系是 C(C#,CNAME,TEACHER), 学生选课关系是 SC(S#,C#,GRADE) 要查找选修 COMPUTER 课程的女学生姓名, 将涉及到关系 A. S B.SC,C C.S,SC D.S,C,SC 答 : 满足该条件的 SELECT 命令如下 : SELECT SNAME,C,SC WHERE SC.C#=C.C# AND C.CNAME= COMPUTER AND S.S#=SC.S# AND S.SEX= 女本题答案为 D 例 3.13 在数据库中有如图 3.1 所示的两个表, 若职工表的主码是职工号, 部门表的主码是部门号, SQL 操作不能执行 A. 从职工表中删除行 ( 025, 王芳, 03,720) B. 将行 ( 005, 乔兴, 04,750) 插入到职工表中 C. 将职工号为 001 的工资改为 700 D. 将职工号为 038 的部门号改为 03 4

5 答 : 由于职工表中职工号为主码, 不能向其中插入同主码的记录本题答案为 B 例 3.14 若用如下的 SQL 语句创建一个表 student: CREATE TABLE student (NO CHAR(4) NOT NULL, NAME CHAR(8) NOT NULL, SEX CHAR(2), AGE INT) 可以插入到 student 表中的是 A.( 1031, 曾华, 男,23) B.( 1031, 曾华,NULL,NULL) C.(NULL, 曾华, 男, 23 ) D.( 1031,NULL, 男,23) 答 :A 中性别 SEX 属性值格式不正确 ;C 中 NO 属性值不能为空 ;D 中 NAME 属性值不能为空本题答案为 B 填空题例 3.15 SQL 是答 : 结构化查询语言例 3.16 SQL 语言的数据定义功能包括答 :1 定义数据库 2 定义基本表 3 定义视图 4 定义索引例 3.17 SELECT 命令中, 子句用于选择满足给定条件的元组, 使用子句可按指定列的值分组, 同时使用子句可撮满足条件的组答 :1WHERE 2GROUP BY3HAVING 例 3.18 在 SELECT 命令中进行查询, 若希望查询的结果不出现重复元组, 应在 SELECT 语句中使用保留字答 :DISTINCT 例 3.19 子查询的条件依赖于你查询, 这类查询称为答 : 相关子查询例 3.20 视图是一个虚表, 它是从导出的表在数据库中, 只存放视图的, 不存放视力对应的答 :1 一个或几个基本表 2 定义 3 数据例 3.21 设有如下关系表 R,S 和 T: R (BH,SM,SB,DWH) S (DWH,DWM) T (BH,XM,DWH) 1 实现 R T 的 SQL 语句是 2 实现 б DWH= 100 (R) 的 SQL 语句是 5

6 3 实现 Л XM,XB(R) 的 SQL 语句是第 3 章关系数据库标准语言 SQL 4 实现 Л XM,XB(б XB= 女 (R)) 的 SQL 语句是 5 实现 R*S 的 SQL 语句是 6 实现 Л XM,XB,DWM(б XB= 男 (R*S)) 的 SQL 语句是答 : 1 SELECT * FROM R UNION SELECT * FROM T 2 SELECT * FROM R WHERE DWH= SELECT XM, XB FROM R 4 SELECT XM, DWH FROM R WHERE XB= 女 5 SELECT R.BH, R.XM, R.XB, R.DWH, S.DWM FROM R, S WHERE R.DWH=S.DWH 6 SELECT R.XM, R.XB, S.DWM FROM R, S WHERE R.DWH=S.DWH AND R.XB= 男例 3.22 设有如下关系表 R: R(NO,NAME,SEX,AGE,CLASS) 主码是 NO 其中 NO 为学号,NAME 为姓名,SEX 为性别,AGE 为年龄,CLASS 为班号写出实现下列功能的 SQL 语句 1 插入一个记录 (25, 李明, 男,21, ): 2 插入班学号为 30 姓名为郑和的学生记录 : 3 将学号为 10 的学生姓名改为王华 : 4 将所有班号改为 : 5 删除学号为 20 的学生记录 : 6 删除姓王的学生记录 : 答 : 1 INSERT INTO R VALUES (25, 李明, 男,21, ) 2 INSERT INTO R (NO,NAME,CLASS) VALUES(30, 郑和, ) 3 UPDATE R SET NAME= 王华 WHERE NO=10 4 UPDATE R SET CLASS= WHERE CLASS= DELETE FROM R WHERE NO=20 6 DELETE FROM R WHERE NAME LIKE= 王 % 例 3.23 在宿主语言中使用 SQL 语句时, 必须在 SQL 语句前加, 用标识结束答 :1EXEC SQL 2END EXEC 例 3.24 宿主语言向 SQL 语言提供参数是通过, 在 SQL 语句中应用时, 必须在宿主变量前加答 :1 宿主变量 2: 例 3.25 用 OPEN 语句打开游标时, 游标指针指向查询结果的答 : 第一个记录 6

7 3.2.3 问答题例 3.26 叙述 SQL 语言支持的三级逻辑结构答 :SQL 语言支持的三级逻辑结果如图 3.2 所示在概念层, 对应概念模式的概念记录型的基本表基本表是这样一种表, 它本身实际存在, 一个基本表就是一个关系, 它不是由其他表导出的表基本表是使用 CREATE TABLE 语句建立的在外层, 用户所看到的可以是基本表, 也可以是视图视图是一个虚表, 它是由一个或几个基本表导出的表, 它不直接存在于物理存储器上视图是使用 CREATE VIEW 语句建立的在内层, 每个基本表用一个存储文件来表示, 即用一组类型相同的存储记录值来表示 DBA 可以对物理存储文件进行操作例 3.27 SELECT 语句中, 何时使用分组子句, 何时不必使用分组子句? 答 :SELECT 语句中使用分组子句的先决条件是要有聚合函数当聚合函数值与其他属性的值无关时, 不必使用分组子句当聚合函数值与其他属性的值有关时, 必须使用分组子句例 3.28 为什么将 SQL 中的视图称为虚表? 答 : 在 SQL 中创建一个视力时, 系统只是将视图的定义存放在数据字典中, 并不存储视图对应的数据, 在用户使用视图时才去求对应的数据, 因此, 我们将视图称为虚表这样处理的目的是为了节约存储空间, 因为视图对应的数据都可从存储的基本表中获得例 3.29 所有的视图是否都可以更新? 为什么? 答 : 更新视图是指通过视图来插入 (INSERT) 删除(DELETE) 和修改 (UPDATE) 数据由于视图是不实际存储数据的虚表, 因此对视图的更新最终要转换为对基本表的更新为防止用户通过视图对数据进行增加删除修改, 有意无意地对不属于视图范围的基本表数据进行操作, 所以一些相关措施使得不是所有的视图都可以更新在 SQL 中, 允许更新的视图在定义时, 必须加上 WITH CHECK IPTION 子句, 这样在视力上增删改数据时,DBMS 会检查视图定义中的条件, 若不满足条件, 则拒绝执行该操作 7

8 例 3.30 在嵌入式 SQL 中如何区分 SQL 语句和宿主语言语句? 答 : 在嵌入式 SQL 中, 为了能够区分 SQL 语句与宿主语言语句, 所有 SQL 语句都必须加前缀 EXEC SQL SQL 语句的结束标志则随宿主语言的不同而不同例如, 在 PASCAL 和 C 中以分号 (;) 结束例 3.31 嵌入式 SQL 如何解决数据库工作单元与源程序工作单元之间的通信? 答 : 数据库工作单元与源程序工作单元之间的通信主要包括 : (1) 向宿主语言传递 SQL 语句的执行状态信息, 使宿主语言能够据此信息控制程序流程, 主要用 SQL 通信区 (SQLCA) 实现 (2) 宿主不应该向 SQL 语句提供参数, 主要用共享变量实现 (3) 将 SQL 语句查询数据库的结果交宿主语言进一步处理, 主要用共享变量和游标 (Cursor) 实现例 3.32 在嵌入式 SQL 中如何协调 SQL 语言的集合处理方式和宿主语言的单记录处理方式? 答 : 在宿主语言程序中嵌入 SQL 语句对数据库进行操作时, 由于 SQL 语句处理的是记录集合, 而宿主语言一次只能处理一条记录, 因为需要使用游标机制, 将集合操作转换成单记录处理方式应用题例 3.33 有两个关系 : C (cno, cn, pcno) SC (sno, cno, g) 其中,C 为课程表关系, 对应的属性分别是课号课程名和选修课号,SC 为学生选课表关系, 对应的属性分别是学号课号和成绩用 SQL 语言写出 : (1) 对关系 SC 中课号等于 C1 的选择运算 (2) 对关系 C 的课号课程名的投影运算 (3) 两个关系的自然连接运算 (4) 求每一课程的间接选修课 ( 即选修课的选修课 ) 答 : 对应的 SQL 命令如下 : (1) SELECT * C WHERE cno= C1 (2) SELECT cno, cn FROM C (3) SELECT C.cno, C.cn, C.pcno, SC.sno, SC.q FROM C, SC WHERE C.cno=SC.cno (4) SELECT first,.cno, second.pcno FROM C first, C second WHERE first.pcno=second.cno 8

9 例 3.34 设有如下 4 个关系模式 : 书店 ( 书店编号, 书店名, 地址 ) 图书 ( 书号, 书名, 定价 ) 图书馆 ( 馆号, 馆名, 城市, 电话 ) 图书发行 ( 馆号, 书号, 书店号, 数量 ) 设各关系模式中的数据满足下列问题请解答 : (1) 用 SQL 语句检索已发行的图书中最贵和最便宜的书名和定价 (2) 写出下列 SQL 语句所表达的中文意思 SELECT 馆名 FROM 图书馆 WHERE 馆名 IN (SELECT 馆号 FROM 图书发行 WHERE 书号 IN (SELECT 书号 FROM 图书 WERE 书名 = 数据库系统基础 )) 答 :(1) 对应的 SQL 语句如下 : SELECT 图书. 书名, 图书. 定价 FROM 图书 WHERE 定价 =(SELECT MAX( 定价 ) FROM 图书, 图书发行 WHERE 图书. 书号 = 图书发行. 书号 ) UNION SELECT 图书. 书名, 图书. 定价 WHERE 定价 =(SELECT MIN( 定价 ) FROM 图书, 图书发行 WHERE 图书. 书号 = 图书发行. 书号 ) (2) 查询拥有已发行的数据库系统基础一书的图书馆馆名例 3.35 设有职工关系模式如下: people (pno, pname, sex, job, wage, dptno) 其中,pno 为职工号,pname 为职工姓名,sex 为性别,job 为职业,wage 为工资,dptno 为所在部门号请写出下列查询使用的 SQL 语句 : (1) 查询工资比其所在部门平均工资高的所有职工信息 (2) 查询工资大于赵明华工资的所有职工信息 9

10 答 : 对应的 SQL 语句如下 : (1) SELECT * FROM people x WHERE wage > (SELECT AVG(wage) FROM people y WHERE x.dptno=y.ptno) (2) SELECT * FROM people WHERE wage > (SELECT wage FROM people WHERE pname= 赵明华 ) 例 3.36 设有如图 3.3 所示的三个基本表表中各个属性的含义如下 :A#( 商店代号 ) ANAME( 商店名 ) WQTY( 店员人数 ) CITY( 所在城市 ) B#( 商品号 ) BNAME( 商品名称 ) RPICE( 价格 ) QTY( 商品数量 ) 试用 SQL 语句写出下列查询 : (1) 找出店员人数不超过 100 人或者在长沙市的所有商店的商店代号和商店名 (2) 找出供应书包的商店名 (3) 找出至少供应代号为 256 的商店所供应的全部商品的商店名和所在城市答 : (1) SELECT A#,ANAME FROM A WHERE WQTY<=100 OR CITY= 长沙 (2) 方法一 : SELECT A.ANAME FROM A,B,AB WHERE A.A#=AB.A# AND B.B#=AB.B# AND B.BNAME= 书包 10

11 方法二 : SELECT ANAME FROM A WHERE A# IN (SELECT A# FROM AB WHERE B# IN (SELECT B# FROM B WHERE BNAME= 书包 )) (3) SELECT A.ANAME, A.CITY FROM A, AB WHERE A.A#=AB.A# AND AB.B# IN (SELECT AB.B# FROM AB WHERE A#= 256 ) 例 3.37 设有图书登记表 TS, 具有属性 :BNO( 图书编号 ),BC( 图书类别 ),BNA( 书名 ),AU ( 著者 ),PUB( 出版社 ) 按下列要求用 SQL 语句进行设计 : (1) 按图书编号 BNO 建立 TS 表的索引 ITS (2) 按出版社统计其出版图书总数 (3) 删除索引 ITS 答 : (1) CREATE INDEX ITS ON TS(BNO) (2) SELECT PUB,COUNT(BNO) FROM TS GROUP BY PUB (3) DROP INDEX ITS 例 3.38 已知 3 个关系 R,S 和 T, 如图 3.4 所示试用 SQL 语句实现如下操作 : (1) 将 R S 和 T3 个关系按关联属性建立一个视图 R-S-T (2) 对视图 R-S-T 按属性 A 分组后, 求属性 C 和 E 的平均值 11

12 答 : (1) CREATE VIEW R-S-T AS SELECT R.A, R.B, R.C, S.D, S.E, T.F FROM WHERE R, S,T (2) SELECT AVG(C), AVG(E) FROM R-S-T GROUP BY A 第 3 章关系数据库标准语言 SQL R.A=S.A AND S.D=T.D 例 3.39 设关关系 R 和 S, 如图 3.5 所示试用 SQL 语句实现 : (1) 查询属性 C>50 时,R 中与之相关联的属性 B 之值 (2) 当属性 C=40 时, 将 R 中与之相关联的属性 B 值修改为 b4 答 : (1) SELECT S FROM R, S WHERE R.A=S.A AND C>50 (2) UPDATE R SET B= b4 WHERE A IN (SELECT A WHERE C=40) 例 3.40 已知 R 和 S 两个关系, 如图 3.6 所示执行如下 SQL 语句 : (1) CREATE VIEW H(A, B, C, D, E) 12

13 AS SELECT A, B, R.C, D, E FROM WHERE (2) SELECT B, D, E FROM H WHERE C= C2 R, S 第 3 章关系数据库标准语言 SQL R.C=S.C 试给出 :(1) 视图 H (2) 对视图 H 的查询结果答 :(1) 视图 H 如图 3.7 所示 (2) 视图 H 的查询结果如图 3.8 所示例 3.41 已知关系 R 如图 3.9 所示试用 SQL 语句实现下列操作 : (1) 按属性 A 分组, 求出每组中在属性 C 上的最大值和最小值, 且将它们置于视图 RVE 中 (2) 在视图 RVE 中查询属性 A= 98 的记录答 : (1)CREATE VIEW RVE(A, CMAX, CMIN) 13

14 AS SELECT A, MAX(C), MIN(C) FROM R GROUP BY A (2)SELECT * FROM RVE WHERE A= 98 第 3 章关系数据库标准语言 SQL 例 3.42 已知学生表 S 和学生选课表 SC 其关系模式如下 : S(SNO,SN,SD,PROV) SC(SNO,CN,GR) 其中,SNO 为学号,SN 为姓名,SD 为系名,PROV 为省区,CN 为课程名,GR 为分数试用 SQL 语言实现下列操作 : (1) 查询信息系的学生来自哪个省区 (2) 按分数降序排序, 输出英语系学生中选修了计算机课程的学生的姓名和分数答 :(1)SELECT DISTINCT PROV WHERE SD= 信息系 (2)SELECT SN, GR, SC WHERE SD= 英语系 AND CN= 计算机 AND S.SNO=SC.SNO GROUP BY GR DESC 例 3.43 设有学生表 S(SNO,SN)(SNO 为学生号,SN 为姓名 ) 和学生选修课程表 SC(SNO, CNO,CN,G)(CNO 为课程号,CN 为课程名,G 为成绩 ), 试用 SQL 语句完成以下操作 : (1) 建立一个视图 V-SSC(SNO,SN,CNO,CN,G), 并按 CNO 升序排序 (2) 从视图 V-SSC 上查询平均成绩在 90 分以上的 SN,CN, 和 G 答 :(1)CREATE VIEW V-SSC(SNO, SN, CNO, CN, G) AS SELECT S.SNO, S.SN, SC.CNO, SC.CN, SC.G, SC WHERE S.SNO=SC.SNO ORDER BY CNO (2)SELECT SN, CN, G FROM V-SSC GROUP BY SNO HAVING AVG(G)>90 例 3.44 设有如图 3.10 所示的 4 个关系模式 : S(SN,SNAME,CITY) 14

15 其中,S 表示供应商,SN 为供应商代号,SNAME 为供应商名字,CITY 为供应商所在城市, 主关键字为 SN P(PN,PNAME,COLOR,WEIGHT) 其中,P 表示零件,PN 为零件代号,PNAME 为零件名字,COLOR 为零件颜色,WEIGHT 为零件重量, 主关键字为 PN J(JN,JANME,CITY) 其中,J 表示工程,JN 为工程代号,JNAME 为工程名字,CITY 为工程所在城市, 主关键字为 JN SPJ(SN,PN,JN,QTY) 其中,SPJ 表示供应关系,SN 是为指定工程提供零件的供应商代号,PN 为所提供的零件代号,JN 为工程代号,QTY 表示提供的零件数量, 主关键字为 SN,PN,JN, 外关键字为 SN,PN,JN 写出实现以下各题功能的 SQL 语句 15

16 (1) 取出所有工程的全部细节第 3 章关系数据库标准语言 SQL (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,CITY> 二元组, 使得第 1 个城市的供应商为第 2 个城市的工程提供零件 (19) 取出所有这样的三元组 <CITY,PN,CITY>, 使得第 1 个城市的供应商为第 2 个城市的工程提供指定的零件 (20) 重复 (19) 题, 但不检索两个 CITY 值相同的三元值答 : 对应的 SQL 语句如下 : (1)SELECT * FROM J (2)SELECT * FROM J WHERE CITY= 上海 (3)SELECT PN FROM P WHERE WEIGHT= (SELECT MIN(WEIGHT) FROM P) (4)SELECT SN WHERE JN= J1 (5)SELECT SN WHERE JN= J1 AND PN= P1 (6)SELECT JNAME FROM J WHERE JN IN (SELECT DISTINCT JN WHERE SN= S1 ) 16

17 (7)SELECT DISTINCT COLOR 第 3 章关系数据库标准语言 SQL FROM P WHERE PN IN (SELECT PN WHERE SN= S1 ) (8)SELECT SN WHERE JN= J1 AND SN IN (SELECT SN WHERE JN= J2 ) (9)SELECT SN WHERE JN= J1 AND PN IN (SELECT PN FROM P WHERE COLOR= 红 ) (10)SELECT DISTINCT SN WHERE JN IN (SELECT JN FROM J WHERE CITY= 上海 ) (11)SELECT SN WHERE JN IN (SELECT JN FROM J WHERE COLOR= 上海 OR CIYT= 北京 ) AND PN IN (SELECT PN FROM P WHERE COLOR= 红 ) (12)SELECT SPJ.PN, J, SPJ WHERE S.SN=SPJ.SN AND J.JN=SPJ.JN AND S.CITY=J.CITY (13)SELECT PN WHERE JN IN (SELECT JN FROM J WHERE CITY= 上海 ) AND SN IN (SELECT SN 17

18 WHERE CITY= 上海 ) (14)SELECT DISTINCT SPJ.JN, J, SPJ 第 3 章关系数据库标准语言 SQL WHERE S.SN=SPJ.SN AND J.JN=SPJ.JN AND S.CITY<>J.CITY (15)SELECT JN FROM J WHERE JN NOT IN (SELECT DISTINCT JN WHERE SN IN (SELECT SN WHERE CITY= 上海 ) (16)SELECT DISTINCT SPJ.SN FROM P, SPJ WHERE SPJ.PN IN (SELECT SPJ.PN, S, P WHERE S.SN=SPJ.SN AND P.PN=SPJ.PN AND P.COLOR= 红 ) (17)SELECT JN WHERE SN= S1 (18)SELECT S.CITY, J.CITY, J, SPJ WHERE S.SN=SPJ.SN AND J.JN=SPJ.JN (19)SELECT S.CITY, SPJ.PN, J.CITY, J, SPJ WHERE S.SN=SPJ.SN AND J.JN=SPJ.JN (20)SELECT S.CITY, SPJ.PN, J.CITY, J, SPJ WHERE S.SN=SPJ.SN AND J.JN=SPJ.JN AND S.CITY<>J.CITY 例 3.45 已知一个关系数据库的模式如下 : S(SNO,SNAME,SCITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,JCITY) SPJ(SNO,PNO,JNO,QTY) 其中,S 表示供应商, 它的各属性依次为供应商号供应商名和供应商所在城市 ;P 表示零件, 它的各属性依次为零件号零件名零件颜色和零件重量 ;J 表示工程, 它的各属性依次为工程号工程名和工程所在城市 ;SPJ 表示供货关系, 它的各属性依次为供应商号零件号工程号和供货数量用 SQL 语句实现下面的查询要求 : (1) 找出北京的任何工程都不购买的零件的零件号 (2) 按工程号递增的顺序列出每个工程购买的零件总量 18

19 答 : 对应的 SQL 语句如下 : (1)SELECT PNO 第 3 章关系数据库标准语言 SQL FROM P WHERE NOT EXISTS, S WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.SCITY= 北京 ) (2)SELECT JNO, SUM(QTY) GROUP BY JNO DRDER BY JNO ASC 例 3.46 设有 4 个关系模式如下 : S(SNO,SNAME,STATE,CITY) P(PNO,PNAME,OLOR,WEI,PRICE) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY) 其中,S 表示供应商, 它的各属性依次为供应商号供应商名供应商所在省份和供应商所在城市 ;P 表示零件, 它的各属性依次为零件号零件名零件颜色零件重量和单价 ;J 表示工程, 它的各属性依次为工程号工程名和工程所在城市 ;SPJ 表示供货关系, 它的各属性依次为供应商号零件号工程号和供货数量用 SQL 语句实现下面的查询要求 : (1) 查询列出供应了全部零件的供应商名和其所在城市 (2) 查询列出供应红色零件的供应商名答 : 对应的 SQL 语句如下 : (1)SELECT SNAME, CITY WHERE NOT EXISTS FROM P WHERE NOT EXISTS WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO)) (2)SELECT SNAME, P, SPJ WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND P.COLOR= 红色例 3.47 关于教学数据库的关系模式如下 : S(S#,SNAME,AGE,SEX) SC(S#,C#,GRADE) 19

20 C(C#,CNAME,TEACHER) 第 3 章关系数据库标准语言 SQL 其中,S 表示学生, 它的各属性依次为学号姓名年龄和性别 ;SC 表示成绩, 它的各属性依次为学号课程号和分数 ;C 表示课程, 它的各属性依次为课程号课程名和任课教师试用 SQL 语句完成下列查询 : (1) 检索王老师所授课程的课程号和课程名 (2) 检索年龄大于 22 岁的男学生的学号和姓名 (3) 检索学号为学生所学课程的课程名与任课教师 (4) 检索至少选修王老师所授课程中一门课程的女学生姓名 (5) 检索张同学不学的课程的课程号 (6) 检索至少选修两门课程的学生学号 (7) 检索全部学生都选项修的课程的课程号与课程名 (8) 检索选修课程包含王老师所授课程的学生学号答 : 对应的 SQL 语句如下 : (1)SELECT C#, CNAME FROM C WHERE TEACHER= 王 (2)SELECT S#,SNAME WHERE AGE>22 AND SEX= 男 (3)SELECT CNAME, TEACHER C, C WHERE SC.C#=C.C# AND S#= (4) 采用连接查询方式 : SELECT SNAME, SC, C WHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX= 女 AND TEACHER= 王采用嵌套查询方式 : SELECT SNMAE WHERE SEX= 女 AND S# IN (SELECT S# C WHERE C# IN (SELECT C# FROM C WHERE TEACHER= 王 ) 采用存在量词查询方式 : SELECT SNAME WHERE SEX= 女 AND EXISTS 20

21 C WHERE SC.S#=S.S# AND EXISTS FROM C WHERE C.C#=SC.C# AND TEACHER= 王 ) (5)SELECT C# FROM C WHERE NOT EXISTS, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME= 张 ) (6)SELECT DISTINCT X.S# C X, SC Y WHERE X.S#=Y.S# AND X.C#!=Y.C# (7)SELECT C#, CNAME FROM C WHERE NOT EXISTS WHERE NOT EXISTS C WHERE S#=S.S# AND C#=C.C#)) (8)SELECT DISTINCT S# C X WHERE NOT EXISTS FROM C WHERE TEACHER= 王 AND NOT EXISTS C Y WHERE Y.S#=X.S# AND Y.C#=C.C#)) 例 3.48 已知一个关系数据库的模式如下 : market(mno, mname, city) item(ino, iname, type, color) sales(mno, ino, price) 其中,market 表示商场, 它的各属性依次为商场号商场名和所在城市 ;item 表示商品, 它的各属性依次为商品号商品名商品类别和颜色 ;sales 表示销售, 它的各属性依次为商场号商品号和售价用 SQL 语句实现下面的查询要求 : (1) 列出北京每个商场都销售, 且售价均超过元的商品的商品号和商品名 (2) 列出在不同的商场中最高售价和最低售价之差超过 100 元的商品的商品号其最高售价和最低售价 21

22 答 : 对应的 SQL 语句如下 : (1)SELECT INO, INAME FROM item WHERE NOT EXISTS FROM market WHERE city= 北京 AND NOT EXISTS FROM sales WHERE item=sales.ino AND market.mno=sales.mno AND price>10000)) (2)SELECT ino, MAX(price), MIN(price) FROM sales GROUP BY ino HAVING MAX(price)-MIN(price)>100 22

作业参考答案

作业参考答案本章的知识点了解 SQL 语言发展史掌握关系数据库体系结构三层结构在关系数据库体现 ) 掌握基本表定义包括修改删除定义 ) 掌握视图的概念与定义删除定义理解索引的概念与定义删除定义总结 SQL 数据定义的特点总结用户数据查询的基本结构掌握 SELECT 子句重复元组的处理掌握 FROM 子句掌握 WHERE 子句理解更名属性列 ) 运算理解字符串操作理解元组显示顺序理解分组掌握聚集函数掌握空值处理理解嵌套子查询的概念