复习——第三章

第03章练习题.doc
一、选择题

1. SQL语言称为（ ）。
   A．结构化定义语言 B. 结构化操纵语言
   C. 结构化查询语言 D. 结构化控制语言
2. 下列SQL语句中，修改表结构的是 ( )
   A．CREATE B．UPDATE C．INSERT D．ALTER
3. 在SELECT语句中, 用于实现数据分组统计的子句是 ( )
   A．FROM子句 B．WHERE子句
   C．GROUP BY子句 D．ORDER BY 子句
4. 下列哪种运算不能引出子查询。( )
   A．IN B．LIKE C．EXISTS D．比较运算
5. 基于学生选课数据库中的3个关系：S(学号, 姓名, 性别, 年龄)、C(课程号, 课程名, 任课教师)、 SC(学号, 课程号, 成绩)，若要求查找选修“数据库技术”这门课程的学生姓名和成绩，将使用关系（ ）
   A．S和SC B．SC和C C．S和C D．S、SC和C
6. 基于学生选课数据库中的3个关系：S(学号, 姓名, 性别, 年龄)、C(课程号, 课程名, 任课教师)、 SC(学号, 课程号, 成绩)，若要求查找学生的平均成绩大于80分的学号和平均成绩，下面列出的SQL语句，正确的是（ ）。
   A．SELECT 学号, AVG(成绩) FROM SC WHERE AVG(成绩)>80
   B．SELECT 学号, AVG(成绩) FROM SC WHERE AVG(成绩)>80 GROUP BY 学号
   C．SELECT 学号, AVG(成绩) FROM SC GROUP BY 学号 HAVING AVG(成绩)>80
   D．SELECT 学号, 成绩 FROM SC GROUP BY 学号 HAVING AVG(成绩)>80
7. 下列SQL语句中，正确的是（ ）。
   A．SELECT S#, SNAME FROM SC GROUP BY S#
   B．SELECT S# FROM SC GROUP BY S# HAVING COUNT()>3
   C．SELECT S# FROM SC GROUP BY S# WHERE COUNT()>3
   D．SELECT S# FROM SC WHERE COUNT(*)>3 GROUP BY S#
8. 在视图上不能完成的操作是（ ）。
   A．更新视图 B．查询视图
   C．在视图上定义新的基本表 D．在视图上定义新视图
   二、填空
9. 在SQL的查询语句中，使用（ FROM ）子句定义数据源，使用（ WHERE ）子句选择参与运算的数据行，使用（ SELECT ）子句指定目标列。可使用（ GROUP BY ）子句结合聚集函数进行分组统计。若对查询结果排序可使用（ ORDER BY ）子句。
10. 在SQL中，（ CREATE ）语句创建表和声明完整性约束条件。如果要为一个基本表增加列和完整性约束条件，应该使用（ ADD ）语句。删除表的定义及表中的数据和索引，应该使用的语句是（ DROP ）。
11. 视图是定义在（ 基本表 ）之上的虚表，对视图也可以进行插入、修改、删除和查询操作。但视图不存储数据，对视图的一切操作最终要转换为对（ 基本表 ）的操作。
    三、简述题

1． SQL语言的特点？
1.综合统一
2.高度非过程化
3.面向集合的操作方式
4.以同一种语法结构提供两种使用方式
5.语言便捷，易学易用
2． SQL语言中常用的语句有哪些？
数据定义：
CREATE SCHEMA
DROP SCHEMA
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
数据查询：
SELECT
FROM
WHERE
GROUP BY
ORDER BY
数据更新：
INSERT INTO
UPDATE SET
DELETE FROM
3． SQL中提供了哪些常用的聚集函数？它们如何使用？
COUNT()
SUM()
AVG()
MAX()
MIN()
在SELECT字句或者HAVING子句中使用
4． 什么是连接查询？连接查询如何指定连接条件？
一个连接同时涉及两个以上的表，则称之为连接查询。
WHERE子句中连接条件的一般格式为表名.列名 比较运算符 表名.列名
5． 什么是子查询？用在WHERE子句中的子查询有几种调用方式？
下层查询块是嵌套在上层查询块的WHERE条件中的。上层的查询块称为父查询，下层查询块成为子查询。

• 带有IN谓词的子查询
• 带有比较运算符的子查询
• 带有ANY或ALL谓词的子查询
• 带有EXISTS谓词的子查询

四、数据库操作

1. 仓库管理系统
   在某仓库管理系统中，有两个表：KC表和CKMX表，其结构和部分数据如下：
   表1、KC表结构和数据
   材料代码 材料名称 单位 单价 库存数量
   01 计算机 台 5000.00 10
   02 电视机 台 2000.00 20
   … … … … …
   表2、CKMX表结构和数据
   编号 材料代码 出库日期 出库数量
   1 01 2009-10-10 4
   2 01 2009-11-20 3
   3 02 2009-11-20 5
   … … … …
   其中，KC表保存当前仓库库存材料的信息，CKMX表存放库存材料的出库名细。
   例如，KC表第一行表示该仓库中现有计算机10台。CKMX表第二行表示2009年11月20日从仓库中出库3台计算机。
   (1) 试写出创建KC表和CKMX表的SQL语句，要求定义实体和参照完整性。

CREATE TABLE KC（
材料代码 CHAR(2) PRIMARY KEY,
材料名称 CHAR(9),
单位 CHAR(9),
单价 FLOAT(2),
库存数量 INT
)
CREATE TABLE CKMX(
编号 INT PRIMARY KEY,
材料代码 CHAR(2),
出库日期 DATE,
出库数量 INT
FOREIGN KEY(材料代码) REFERENCES KC(材料代码)
)
(2) 写出在KC表上按材料名称建立唯一索引的SQL语句。
CREAT UNIQUE INDEX Cname ON KC(材料名称)
(3) 创建一个视图，用于查询计算机的出库明细。
CREAT VIEW OUT_KC(编号,材料名称,出库日期,出库数量)
AS
SELECT 编号,材料名称,出库日期,出库数量
FROM KC,CKMX
WHERE KC.材料代码 = CKMX.材料代码
(4) 写出将表中数据插入到相应表中的语句。
(5) 从表CKMX中统计出各种材料的出库次数和出库总数量，试写出SQL语句。
(6) 写出由KC表和CKMX表产生如下结果集的查询语句：
编号 出库日期 材料名称 单价 出库数量 金额
1 2009-10-10 计算机 5000.00 4 20000.00
2 2009-11-20 计算机 5000.00 3 15000.00
… … … … … …
(1) 创建表
CREATE TABLE KC
(
材料代码 char(2) PRIMARY KEY,
材料名称 char(20),
单位 char(10),
单价 numeric(10,2),
库存数量 int
)
CREATE TABLE CKMX
(
编号 char(4) PRIMARY KEY,
材料代码 char(2) REFERENCES KC(材料代码),
出库日期 date,
出库数量 int
)
Or或者
CREATE TABLE CKMX
(
编号 char(4) PRIMARY KEY,
材料代码 char(2),
出库日期 date,
出库数量 int,
Foreign key (材料代码) REFERENCES KC(材料代码)
)
(2) 创建索引
CREATE UNIQUE INDEX index1 ON KC(材料名称)
Drop index index1 on KC
(3) 创建视图
CREATE VIEW viewComputer
AS
SELECT FROM CKMX,KC
WHERE CKMX.材料代码=KC.材料代码 and KC.材料名称= ‘计算机’
(4) 插入数据
INSERT INTO KC VALUES(‘01’,’计算机’,’台’,5000.00,10)
INSERT INTO KC VALUES(‘02’,’电视机’,’台’,2000.00,20)
INSERT INTO CKMX VALUES(‘1’,’01’,’2009-10-10’,4)
INSERT INTO CKMX VALUES(‘2’,’01’,’2009-10-20’,3)
INSERT INTO CKMX VALUES(‘3’,’02’,’2009-10-20’,5)
(5) 统计
SELECT 材料代码,COUNT() as 次数, SUM(出库数量) as 出库数量
from CKMX
group by 材料代码
(6) 查询
SELECT ckmx.编号,出库日期,材料名称,单价,出库数量,出库数量*单价 as 金额
FROM KC,CKMX
WHERE KC.材料代码 = CKMX.材料代码

1. 财务管理系统
   学校有多名学生，财务处每年要收一次学费。财务处现用两个表记录相关信息，其结构和部分数据如下表：
   表1、XS表结构和数据
   学号 姓名 性别 年龄 所在系
   200901 张明 男 18 计算机
   200902 王晓萌 21 信息
   200903 李刚 男 20 计算机
   … … … … …
   表2、JFQK表结构和数据
   序号 学号 交费日期 书费 学费
   1 200901 2009-09-10 500.50 5000
   2 200902 2009-09-10 400.00 5000
   3 200901 2010-10-10 300.20 4000
   … … … …
   其中，XS表是在校学生名册和基本信息登记表，JFQK表是学生交学费情况记录。
   (1) 试写出创建XS表和JFQK表的SQL语句，要求定义实体和参照完整性。
   (2) 写出在XS表上按学生姓名建立唯一索引的SQL语句。
   (3) 创建一个视图，用于查询计算机系同学的基本信息。
   (4) 写出将表中数据插入到相应表中的语句。
   (5) 将计算机系同学的年龄增加1岁，并将计算机系同学的学费减1000元。
   (6) 从表JFQK中统计出每个同学的交费次数和交费总金额，试写出SQL语句。
   (7) 写出由XS表和JFQK表能产生类似如下结果集的查询语句：
   序号 姓名 交费日期 书费 学费 合计
   1 张明 2009-09-10 500.50 5000 5500.50
   2 王晓萌 2009-09-10 400.00 5000 5400.00
   … … … … … …

(1) 创建表
CREATE TABLE XS
(
学号 char(6) PRIMARY KEY,
姓名 char(10) not null,
性别 char(2) null,
年龄 smallint,
所在系 char(20)
)
CREATE TABLE JFQK
(
序号 int PRIMARY KEY,
学号 char(6) REFERENCES XS(学号),
交费日期 date,
书费 numeric(6,2),
学费 int
)
(2) 创建索引
CREATE UNIQUE INDEX index1 ON XS(姓名)
(3) 创建视图
CREATE VIEW viewComputer1
AS
SELECT * FROM XS WHERE 所在系 = ‘计算机’
(4) 插入数据
INSERT INTO XS VALUES(‘200901’,’张明’, ‘男’,18,’计算机’)
INSERT INTO XS VALUES(‘200902’,’王晓萌’,null,21,’信息’)
INSERT INTO XS VALUES(‘200903’,’李刚 ‘, ‘男’,20,’计算机’)
INSERT INTO JFQK VALUES(1,’200901’,’2009-09-10’,500.50,5000)
INSERT INTO JFQK VALUES(2,’200902’,’2009-09-10’,400.00,5000)
INSERT INTO JFQK VALUES(3,’200901’,’2010-10-10’,300.20,4000)
(5) 更新数据
UPDATE XS SET 年龄 = 年龄 + 1
WHERE 所在系 = ‘计算机’
或 UPDATE viewComputer1 SET 年龄 = 年龄 + 1

UPDATE JFQK SET 学费 = 学费 -1000
WHERE ‘计算机’ = (SELECT 所在系 FROM XS WHERE XS.学号 = JFQK.学号)
(6) 统计
SELECT 学号,COUNT(*) as 交费次数, SUM(书费+学费) as 总金额
FROM JFQK
GROUP BY 学号
(7) 查询
SELECT 序号,姓名,交费日期,书费,学费,书费+学费 as 合计
FROM XS,JFQK
WHERE XS.学号 = JFQK.学号