数据库上机模拟题
在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。