数据库上机模拟题

数据库上机模拟题

在D盘根目录下用自己的名字加“_DB”新建立一个文件夹（如：“张三_DB”）

一．在SQL Server Management Studio中建立满足如下要求的数据库：

（1）数据库名字为：ABC；

（2）此数据库包含两个数据文件，其中主数据文件的逻辑文件名为：Data1,物理文件名为Data1_data.mdf，初始大小为3MB，存放在新建目录下，增长方式为自动增

长，每次增加1MB，最多增长到10MB。次要数据文件的逻辑文件名为：Data2，

物理文件名为Data2_data.ndf，初始大小为2MB，同主数据文件存放在相同的位

置，不自动增长。

（3）日志文件的逻辑文件名为：log1,物理文件名为log1_log.ldf，初始大小为2MB，同主数据文件存放在相同的位置，增长方式为自动增长，每次增加15%。

二．为描述销售人员对产品的销售情况，定义了三张表：销售人员表、产品表和销售情况表，分别用于描述销售人员的基本信息、产品的基本信息和销售人员与产品之间的销售情况。写出在“ABC”数据库中定义这三张表的语句并创建这三张表，（请将语句以文件形式保存下来，文件名为“考生名.sql”，存放在新建的目录下。可以将下述所有的SQL语句均保存到此文件中，在文件中要注明题号），三张表的结构为：

注:括号中为表名和列名的拼音表示，建表时用拼音表示表名和列名即可。

1．销售人员表（XSRYB）包括如下字段：

职工号（ZGH）：普通编码定长字符型，6位长，主关键字

姓名（XM）：普通编码定长字符型，10位长，非空，姓名不允许重复。

年龄（NL）：整型，取值范围为20 ~ 60，允许空

地区（DQ）：普通编码可变长字符型，10位长，允许空

邮政编码（YZBM）：用户自定义类型MyType，类型为普通编码定长字符型，长度

6位，允许空。

电话（DH）：普通编码定长字符型，12位长,取值形式为：“(3位数字)-6位数字”, 允许空

2．产品表（CPB）包括如下字段：

产品号（CPH）：普通编码定长字符型，6位长，以字母P或C开头，主关键字

产品名称（CPMC）：统一编码可变长字符型，20位长，非空，

产品类别（LB）：统一编码定长字符型，6位长

产地（CD）：普通编码定长字符型，10位长，非空

价格（JG）：整型，非空

生产日期（SCRQ）：小日期时间型，允许空，默认为系统当前日期

3．销售情况表（XSQKB）包括如下字段：

职工号（ZGH）：普通编码定长字符型，6位长，非空

产品号（CPH）：普通编码定长字符型，6位长，非空

销售日期（XSRQ）：日期时间型，非空

销售数量（XSSL）：小整型，非空

其中：（职工号，产品号，销售日期）为主关键字

职工号为参照销售人员表的外部关键字，产品号为参照产品表的外部关键字。

三．运行exam.exe程序解压缩数据文件（可解压到任何地方），解压缩的密码为“ABC33”。利用SQL Server的导入导出向导，将“销售人员表和销售情况表.xls”和“产品表.txt”文件中的数据分别导入到上述三张表中。

四．利用数据库ABC中的三张表，在查询编辑器中实现如下要求的SQL语句，注明题号。并将这些语句与上面建表时所写的语句保存在一起（注：要求下述语句均使用一条SQL语句完成）

1．查询2010年以前单笔销售数量最少的销售记录。

2．查询由多个产地生产的相同类别的产品，列出产品名称，类别及产地。

3．查询只销售过"电冰箱"类产品的销售人员信息。

4．查询销售过"创新牌音响"的销售人员的销售记录次数及平均销售数量，要求只列出销售记录次数大于2次且平均销售数量大于2件的情况。

5．查询2001年每个月每个产品的销售总数量，列出月份、产品号和销售总数量，结果先按月份升序排序，再按销售数量降序排序。

6．查询销售次数大于平均销售次数的人员信息。

7．列出"张晓峰"没有销售过的产品名称及价格信息。

8．查询销售过所有产品的销售人员的职工号。

9．查询被卖出次数最少的前10%的产品（包括没有被买出过的产品，包括并列的情况），列出产品名称。

10．查询产品表按价格降序排序后排在第6~10位的产品信息。

11．将销售人员表中邮政编码列的类型修改为统一编码定长字符型，6位长，不能为空

12．根据产品销售数量对产品价格进行调整。产品销量为0，不调整；产品销量小于5，产品价格下调5%；产品销量在5到10之间，产品价格下调10%；产品销量大于10，产品价格下调15%。

五．视图与存储过程

1．通过视图分步骤解决以下问题：查询每种产品的销售总额占所在类别所有产品销售总额的比例，要求保留到小数点后2位。

2．创建满足如下要求的存储过程：查询指定产品在指定日期之后（不包括该日期）被销售的次数。其中：产品和日期为输入参数，产品名称默认为“YY牌电冰箱”，销售次数为输出参数，要求判定指定产品是否存在。创建完毕后执行此存储过程，查询产品名称为默认值的产品在2000年1月1日之后的销售记录，并按照下图格式打印结果：

六．用游标实现按如下报表形式显示结果的T-SQL语句。该报表统计全部销售人员的销售

总业绩及销售明细记录，要求按照总业绩降序显示，其中总业绩为所有单笔销售金额之和（销

售金额=销售数量*价格）。

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

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

●打开游标：open 游标名

●关闭游标：close游标名

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

●释放游标：deallocate 游标名

报表形式大致如下：

七．在SQL Server Management Studio中完成如下操作：

1．创建一个SQL Server身份验证的登录账户：登录名为loginUser，密码为空，授予loginUser具有创建、更改、删除和还原任何数据库的权限，默认数据库为ABC，其中对应loginUser的数据库用户名为dbUser。

2．授予dbUser有创建视图的权限，有更新销售人员表中职工号和姓名两列的权限，且dbUser有权将该权限授予其它用户。

3．创建一个数据库角色MyRole，dbUser是该角色的成员。用最优方式进行如下安全控制：MyRole角色下所有成员有查询所有表的权限，但dbUser不能查询销售人员表中的数据。

4. 创建一个新的SQL Server身份验证的登录账户logUser2，只允许其访问数据库ABC

中“电冰箱”类别的销售情况信息。如何实现？

八．在SQL Server Management Studio中完成如下操作：

1．将数据文件夹中的studb1.bak和studb2.bak全部恢复到SQL Server2008中，目标数据库名为StuDB。

2．对StuDB数据库进行完整备份、差异备份和事务日志备份各一次，以临时性备份的方式，将备份文件存入考试文件夹，备份文件名为studb.bak。