sql模拟试题

山东省计算机审计第八期中级培训班结业试题

数据库应用技术上机考试试卷 (1)

说明：考试结束，不要关机，请老师确认；离场前，将试卷填写学号、姓名、机器号放在机器键盘下！

要求：在E：\根目录下用自己的姓名拼音加“DB”建立一个文件夹。（例：姓名为王平的考生建立文件夹“E:\WangpingDB”），考试中所有的文件必须按照要求放在该文件夹下！否则不得分！

一、创建数据库、数据表以及数据导入操作。

本题共15分，其中第1小题3分，第2小题8分，第3小题4分。

在SQL Server 2008中，使用SQL Management Studio建立满足如下要求的数据库。1．数据库的名字：ShoppingDB。

数据文件：包含一个主数据文件和一个辅助数据文件。主数据文件的逻辑文件名

Shop_dat，物理文件名: Shop_Mdat.mdf，存放在新建目录下；初始大小20MB，自动增长，每次增长30%，最多增加到200MB；辅助数据文件的逻辑文件名为：Shop_dat2，物理文件名为Shop_Mdat2.ndf，同主数据文件存放在相同的位置，初始大小为6MB；

日志文件：日志文件包含一个，其逻辑文件名为：Shop_log，物理文件名为

Shop_log.ldf，同主数据文件存放在相同的位置，初始大小为8MB，自动增长，每次增长1MB，最多增加到100MB。

2．在SQL Server 2008中，使用SQL语句分别创建满足下述条件的表。文件保存在

新建文件夹下,名为question1.sql。（建表时用括号里面的英文作为字段名）（1）agent表结构（记录代理商信息）

代理商代码（aid）：普通编码定长字符串，长度3，主码；

代理商姓名（aname）：统一编码变长字符串，长度6，非空；

所在城市（acity）：统一编码变长字符串，长度8，非空；

代理商提成（apercent）：小数点后保留2位，小数点前4位，默认值为0。

（2）Client表结构（记录客户信息）

客户代码（cid）：普通编码定长字符串，长度4，主键；

客户姓名（cname）：统一编码变长字符串，长度6，非空；

所在城市（ccity）：统一编码变长字符串，长度8，取值范围为{北京，天津，广州，济南}；

折扣率（cdiscnt）：普小数点后保留2位，小数点前4位，默认值为0。

（3）Product表结构（记录商品信息）

商品代码（pid）：普通编码定长字符串，长度4，主键；

商品名（pname）：统一编码变长字符串，长度6，非空，唯一；

产地（pcity）：统一编码变长字符串，长度8；

数量（pquantity）：整型，默认值为0；

价格（pprice）：小数点后保留2位，小数点前4位，价格介于0到100之间。

（4）OrderItems表（记录订单信息）

订单代码（oid）：普通编码定长字符串，长度3，主键；

代理商代码（aid）：普通编码定长字符串，长度3，非空；

客户代码（cid）：普通编码定长字符串，长度4，非空；

商品代码（pid）：普通编码定长字符串，长度4，非空；

数量(oquantity) ：整型，默认值为0；

“代理商代码（aid）”为参照代理商表（agent）的“代理商代码（aid）”的外部关键字；

“客户代码（cid）”为参照客户表（client）的“客户代码（cid）”的外部关键字；“商品代码（pid）”为参照商品表（product）的“商品代码（pid）”的外部关键字。

3．利用SQL Server2008的DTS工具将E：\目录下的数据分别导入到第1题建立的数

据库中，将“agent.txt”中的数据导入到“Agent”表中；将“client-orderitems- product数据.xls”中的数据分别导入到client表、orderitems表和product表中。

二、利用第一题建立的表，在Management Studio中写出实现如下要求的SQL语句。要求

注明题号，保存在新建目录下，文件名为：question2.Sql。（注：下述操作必须使用

一条SQL语句完成）。

本题共24分，（1）-（6）题每题2分，（7）-（10）题每题3分。

（1）查询所有订购了至少一个价值为0.50的商品的顾客的姓名。

（2）找出全部没有在代理商“杜不朗”处订购商品的顾客姓名。

（3）找出订购了p01和p07这两种商品的顾客的姓名。

（4）查询每个代理商为顾客c002和c003订购产品及产品总数量。显示代理商姓名、商品代码以及产品总数量。

（5）查询被至少两个顾客订购的产品名。

（6）查询每个代理商销售给“北京”的顾客的订单销售总额占其总销售额的比例，要求保留到小数点后2位。列出销售商代码和比例。

（7）北京和天津的顾客中有一些顾客具有相同折扣率。找出其它城市中与两城市具有相同折扣率的顾客的cid。

（8）求出满足以下条件的顾客姓名:该顾客的discnt小于任一住在北京的顾客的discnt。

（9）找出没有被任何一个在北京的顾客通过在上海的代理商订购的所有商品。（10）将商品表中钢笔价格提升20%，铅笔价格提升50%，其他商品价格保持不变。

1.查询至少由两个代理商代理的商品的名称和单价

2.查询各种产品的已销售数量占其库存数量的比例

3.（利用视图）计算每种产品的囤积成本和销售收入（扣除打折以后），最后显示每种产品的回本情况（销售收入-囤积成本-提成），如果为负数，显示“亏本”，如果是小于100，显示“保本”，大于等于100，则显示“赚钱”。

4.

三、创建视图。（用于创建视图的语句写在新建目录下quesiton3.sql中）。

本题共4分。

利用orderitems表生成每笔业务的利润profit。计算公式为：总收入-总支出，总收入

为单价*数量；总支出包括：60%的成本，以及顾客折扣和代理商提成，顾客折扣以及代理商的提成依据折扣率（cdiscnt）和提成率（apercent）计算，如果cdiscnt=10表示

折扣10%卖出；apercent的值为10表示提成10%。利润高于150的显示利润高，在100与150之间的显示利润一般，否则显示利润低。利润保留小数点后2位。结果如下图所示。

第三题图

四、创建存储过程完成如下输出。（用于创建存储过程的语句写在新建目录下

quesiton4.sql中）。

本题共5分。

要求显示顾客的姓名，所订商品的订单数量以及折扣金额。该存储过程包含一个输入参数：cname，表示顾客的姓名，默认值为‘李广’；两个返回参数ocount和osumdiscnt 分别表示订单数量和折扣金额。折扣金额的计算参照第三题（价格*数量*折扣率）。给出创建和执行存储过程语句。

结果如下图所示。

第四题图

五、利用游标完成如下输出。（用于创建存储过程的语句写在新建目录下

quesiton5.sql中）。

本题共8分。

用游标实现按如下报表形式。列出各个代理商的商品订货情况。