上机试题2

数据库上机练习2
在C:盘根目录下用自己的名字加“_DB”建立一个文件夹。

（例：“王平_DB”）
一．（4分）使用企业管理器建立满足如下要求的数据库（不区分大小写）：
1．数据库名字为：DB081；
2．此数据库包含的主数据文件的逻辑文件名为：DB_data,物理文件名为DB_data.mdf，存放在新建文件夹下，初始大小为4MB，自动增长，每次增加2MB，最多增加到10MB。

3．日志文件的逻辑文件名为：DB_log,物理文件名为DB_log.ldf，同主数据文件存放在相同的位置，初始大小为3M，自动增长，每次增加20%。

二．（10分）为描述商品的销售情况，建立了三张表：客户表、商品表和销售表。

客户表用于描述客户的基本信息，商品表用于描述商品的基本信息，销售表描述商品对客户的销售情况。

在查询分析器中写出创建这三张表的语句，并将语句以文件形式保存下来，文件名为“考生名.SQL”，存放在新建的文件夹下。

可以将后续所有的SQL语句均保存在此文件中，在文件中注明题号，不用抄题）。

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

三张表的结构如下：
1．客户表（KHB）
客户号(KHH) 普通字符编码定长字符型，长度为4，主码。

客户名(KHM) 普通字符编码可变长字符型，最大长度为12，非空。

积分(JF) 整型，默认值为0。

2．商品表（SPB）
商品号(SPH) 普通字符编码定长字符型，长度为4，主码。

（注：一个商品号代表一种商品）。

商品名(SPM) 统一字符编码可变长字符型，最大长度为20，非空。

类别(LB) 普通字符编码定长字符型，长度为6，取值范围为{冰箱，电视，电脑} 进价(JJ) 整型。

3．销售表（XSB）
客户号(KHH) 普通字符编码定长字符型，长度为4,非空。

商品号(SPH) 普通字符编码定长字符型，长度为4，非空。

销售日期(XSRQ) 小日期时间型，默认值为系统当前日期，非空。

销售数量(XSSL) 小整型。

销售价格(XSJG) 整型。

（注：指单个商品的销售价格）
其中：主码为（客户号，商品号，销售日期），
“客户号”为引用客户表的“客户号”的外码，
“商品号”为引用商品表的“商品号”的外码。

三．（6分）运行C:\SQL_Data1.exe程序解压缩数据文件（可解压到任何地方），解压缩的密码为“bbaa”。

利用SQL Server的DTS工具，将解压后的“客户及销售表.xls”文件中的数据分别导入到“客户表”和“销售表”中，将“商品表.txt”文件中的数据导入到“商品表”中。

四．（32分）利用第三题的三张表，在查询分析器中写出实现如下要求的SQL语句，注明题号。

并将这些语句与第三题所写的语句保存在一起（全部语句均保留在一个文件中，包括后续的语句）。

（注：下述每个小题要求均使用一条SQL语句完成）
1. 查询姓“张”和姓“王”的客户的详细信息。

2. 查询2008年每个月每种商品的销售总数量，列出月份、商品号和销售总数量，结果按月份和
商品号升序排序。

3. 查询被卖出次数最少的三种商品（包括没有被卖出过的商品，包括并列的情况），列出商品名、
类别和进价。

4. 查询在2008年3月到8月期间没有被卖出过的“电脑”类商品的名称和进价。

5. 查询至少购买了“电视”和“冰箱”两类商品的客户名、购买的商品名和类别。

6. 在客户表中插入一新记录，客户号为“K100”，客户名为“新客户”，积分用默认值。

7. 将销售总数量超过1000的商品的进价降低10%。

六．（8分）修改客户表积分列的值，使得客户的积分等于其购买的全部商品的销售总价的百分之十（只修改有购买记录的客户，可用多条语句实现）。

七．（10分）写出用游标实现按如下报表形式显示结果的SQL语句。

该报表查询每年销售总数量最高的前2种商品的商品号、销售总数量和销售总利润（利润＝销售价格 - 进价）
提示：有关游标的语句格式如下：
●声明游标：declare 游标名 cursor for select 语句
●打开游标：open 游标名
●关闭游标：close游标名
●提取数据：fetch next from 游标名 into……
●释放游标：deallocate 游标名
报表形式大致如下：
2007年
商品号销售总量总利润
S003 15 14000
S001 10 8000
2008年
商品号销售总量总利润
S005 30 26000
S001 20 20000。