第十期数据库上机考试题

第十期计算机中级培训数据库考试（上机题）

2009-12

单位：__________ 姓名：_______________ 成绩：_______________ 上机70分（时间2小时）。

在D：盘根目录下用自己的名字加“_考试”建立一个文件夹。（例：“王平_考试”）

一．（6分）使用企业管理器建立满足如下要求的数据库：

1．数据库名字为：library；

2．此数据库包含的一个主数据文件和一个辅助数据文件。主数据文件的逻辑文件名为：library_data1,物理文件名为library _data1.mdf，存放在新建文件夹下，初始大小为2MB，自动增长，每次增加20％，最多增加到8MB。辅助数据文件的逻辑文件名为：library _data2,物理文件名为library_data2.ndf，同主数据文件存放在相同的位置，初始大小为2MB，不自动增长。

3．日志文件包含一个，其逻辑文件名为：library _log,物理文件名为library _log.ldf，同主数据文件存放在相同的位置，初始大小为3MB，增长方式为自动增长，每次增加1MB，文件增长不受限制。

二．（10分）为描述读者向图书馆的借阅情况，建立了三张表：读者表、图书表和借阅表。读者表用于描述读者的基本信息，图书表用于描述图书的基本信息，借阅表用于描述读者向图书馆的借阅情况。在查询分析器中写出创建这三张表的语句，并将语句以文件形式保存下来，文件名为“考生名.sql”，存放在新建的文件夹下。可以将后续所有的SQL语句均保存在此文件中，在文件中注明题号，不用抄题）。

注:括号中为表名和列名的拼音表示，建表时用拼音表示表名和列名，不区分大小写。

三张表的结构如下：

1．读者表（ZZB）

读者号（dzh）普通字符编码定长字符型，长度为4，主码；

读者名（dzm）统一字符编码可变长字符型，最多5个汉字，取值不重复,非空；

登记日期（djrq）小日期时间型，默认值为系统当前时间；

借阅次数（jycs）整型，默认值为0。

2. 图书表（TSB）

图书号（tsh）普通字符编码定长字符型，长度为4，主码；

图书名（tsm）普通字符编码可变长字符型，最大长度为20，非空；

作者（zz）普通字符编码可变长字符型，最大长度为10，非空；

价格(jg) money型。

3. 借阅表（JYB）

图书号（tsbh）普通字符编码定长字符型，长度为4，非空；

读者号（dzh）普通字符编码定长字符型，长度为4，非空；

借阅日期（jyrq）小日期时间型，非空，默认值为系统当前时间；

应还日期（yhrq）小日期时间型，非空，默认值为系统当前时间之后的30天

（即借书期限为30天—dateadd(day,30,getdate( ))）；

其中，主码为（图书号，读者号，借阅日期），

“图书号”为引用图书表的“图书号”的外码；

“读者号”为引用读者表的“读者号”的外码。

三．（6分）利用SQL Server的DTS工具，将“D:\图书表.txt”文件中的数据导入到“图书表”

中，将“D:\读者与借阅表.xls”文件中的数据分别导入到“读者表”和“借阅表”中。

四．（6分）在SQL Server中创建“SQL Server身份验证”模式的登录帐号：lib，其中账户密码为空。并完成如下操作：

1．让lib成为library数据库的合法用户，且让library数据库成为此登录帐号的默认数据库。2．在library数据库中建用户自定义角色：MyRole，让lib成为MyRole角色中的成员。

3．授予此用户具有对library数据库中的“读者表”和“图书表”的SELECT权限。

五．（4分）按要求完成下列操作：

1．用企业管理器创建一个永久备份设备，备份设备的名字为“lib_bk”，存放在新建文件夹下，物理文件名为“lib_bk.bak”。

2．用“lib_bk”备份设备对“library数据库”进行一次完全备份。

六．（28分）利用第二题的三张表，在查询分析器中写出实现如下要求的SQL语句，注明题号。并将这些语句与第二题所写的语句保存在一起。（注：要求下述语句均使用一条SQL语句完成）

1.查询价格大于或等于20元的图书的图书号，图书名，作者以及价格。

2.查询姓哪个姓的作者最多，列出这个姓氏和姓这个姓氏的作者人数，包含并列的情况。

3.查询从来没有借过书的读者的信息，列出读者号，读者名，登记日期。

4.查询图书名相同，作者名不同的图书信息，列出图书号，图书名，作者。

5.查询全体读者（包括还没有借过书稿的读者）的人均借书次数，列出总借书次数、总人数和人

均借书次数。要求平均借书次数保存到小数点后1位。

6.向借阅表中插入一条记录,读者号为“0006”,图书号为“2”,借书日期、应还日期为默认值。

7.将价格高于所有图书的平均价格超过10元的图书的价格减10元。

七．（10）修改读者表借阅次数列的值，使得读者的借阅次数等于其借阅的全部图书的总次数（只修改有借阅记录的读者，可用多条语句实现）。

八．（10）用游标实现按如下报表形式显示结果的SQL语句。该报表查询每年借阅总数量最高的前2种图书的图书号、借阅总数量（包括并列情况）。

注意：报表的显示格式。)

提示：有关游标的语句格式如下：

●声明游标：declare 游标名 cursor for select 语句

●打开游标：open 游标名

●关闭游标：close游标名

●提取数据：fetch next from 游标名 into……

●释放游标：deallocate 游标名

●全局变量：@@fetch_status