SQLServer综合练习题

SQL Server综合练习题
说明：
请在做题前找到随题文件北风贸易.mdf和北风贸易.ldf，附加数据库并指定数据库名为“w+学号最后两位+姓名”，如“w23张三”，以下所有的操作均完成在这个数据库中，请严格按照题目要求答题，包括字段次序。

一、基础设计（共40分）
业务介绍：
●Sporting Goods是一家美国批发公司，接收来自世界范围内的运动产品零售商店的定单。

该公司的客户遍布国内外，每一个客户都有一个唯一标识号。

●公司必须保存有客户的商店名称和电话号码、地址、所在城市、州、国家、邮政编码、
信誉等级以及对客户喜好的总体评论。

●公司需跟踪每一份定单的标识号、订货日期及付款方式。

●客户所在区域有唯一的名称及标识号。

●每个区域有一个仓库，仓库具有唯一的标识号及其它信息，包括地址、城市、州、、国
家、邮政编码、经理ID及电话号码等。

●库存中每一件商品具有唯一的标识号，还必须跟踪产品价格、订购的数量及已发送的数
量。

●公司对每一种产品，必须知道它唯一的产品标识号及名称。

●公司有一些雇员或销售代表被派往多个地区，对每一雇员，公司备有其姓、名、唯一的
标识号及计算机登录ID，其他信息包括该雇员进入公司的时间、评语、职务、工资及佣金百分率。

●对每一个仓库及它存储的产品，公司货单记录有每个产品的存储量、再订货点等。

1) 建立下列9张表
（1）s_customer表（描述客户信息）
（2）s_dept表（描述公司各部门信息）
（4）s_emp表（描述公司雇员信息）
（6）s_product表（描述产品信息）
2) 参照完整性约束
3) 其它约束
1.s_customer客户的信誉等级（Credit_rating）只能取Excellent、Good、Poor
2.s_dept表中，name及region_id的组合必须是唯一的，确保在地区中部门名是唯一
的。

3.任何雇员的佣金百分率必须为以下值之一：10、12.5、15、17.5、20
4.在表s_inventory中，product_id及warehouse_id的组合必须是唯一的。

5.给s_inventory表的Out_of_stock_explanation列设定默认值为“原因未知”。

6.建立DEFAULT对象，名称为AddressDefault，值为“地址不祥”，并将该对象绑定
到s_customer表、s_warehouse表、的Address列上。

4) 索引
1.给s_emp表按Dept_id创建索引，升序排序。

2.给s_warehouse表按Region_id创建索引，降序排序。

3.给s_ord表按Customer_id和Date_ordered创建索引，升序排序。

二、查询设计（20题，共40分）
1.列出“客户”表中所有城市名称，城市名称相同者只列出一次。

2.列出“客户”表中城市列的值为“北京市”的客户，要求只包括列：客户编号、公
司名称、联系人，并且将列出的查询结果输出到表“北京客户”中。

3.列出“飞狐工作室”表中，在行销部、业务部、财务部任职的员工的员工编号、姓
名、电话号码。

4.查询出“飞狐工作室”表中，起薪在20000～25000之间的员工编号、姓名。

5.查询出“飞狐工作室”表中，本月出生的员工编号、姓名。

6.查询出“飞狐工作室”表中，姓名中含“一”的员工编号、姓名。

7.查询出“飞狐工作室”表中，姓“温”的员工编号、姓名。

8.查询出“飞狐工作室”表中，姓名中第二个字为“一”的员工编号、姓名。

9.查询出“飞狐工作室”表中，身份证号码第一个字符为M、N、A的员工编号、姓
名。

10.查询出“飞狐工作室”表中，身份证号码第一个字符不介于A～M的员工编号、
姓名。

11.查询出“飞狐工作室”表中，家庭地址中含_字符的员工编号、姓名。

*
12.查询每一位客户的订货情况，查询结果中要包含客户编号、公司名称、订单号码、
订单日期、送货地址。

13.查询每一位客户的订货情况，查询结果中要包含客户编号、公司名称、订单号码、
订单日期、产品编号、单价、数量。

14.查询每一位客户的订货情况，希望那些未下订单的客户数据也要出现在查询结果
中，要求选取列：客户编号、公司名称、订单号码、订单日期、送货地址。

15.查询出所有曾经在1997年以后订货的的客户编号、公司名称、订单号码、订单日
期、产品编号、产品、单价、数量。

*
16.统计出“雇员”表中，有多少员工未婚。

17.统计出“雇员”表中，有多少员工住在“深圳市”。

18.统计出“雇员”表中，有多少员工雇用年数在20～30年之间（包括20年和30年）。

19.统计出在1996年7月份下订单的北京市客户有多少位。

20.统计出“雇员”表中，最高的“目前薪资”是多少。

21.统计出“雇员”表中，“信息部”最高的“目前薪资”是多少。

22.统计出“雇员”表中，最低的“目前薪资”是多少。

23.统计出“雇员”表中，“信息部”与“业务部”最低的“目前薪资”是多少。

24.统计出“雇员”表中，员工的平均年龄。

25.统计出“雇员”表中，员工的平均“目前薪资”。

26.统计出“雇员”表中，“信息部”所发的“目前薪资”总和。

27.统计出所有客户的订购总金额。

28.统计出“雇员”表中，各个部门的薪资最高值、平均值及人数，另要求统计结果中
包括任职部门列。

29.统计出每一个客户的订购次数和订购总金额，要求统计结果中包括客户编号、公司
名称、采购次数、采购总金额。

*
30.统计出“员工”表中每一个业务人员的销售总数量，要求列出员工编号、姓名和销
售总数。

*
31.统计出“飞狐工作室”表中，部门平均薪资大于30000的各个部门的薪资最高值、
最小值、平均值及人数。

*
32.按目前薪资从大到小的顺序，列出“飞狐工作室”表中所有员工的姓名、性别和目
前薪资，其中性别列的内容要显示成“男”、“女”字样。

*
33.查询出“飞狐工作室”表中，目前薪资倒数前10名员工姓名及其薪资。

34.查询出“飞狐工作室”表中，目前薪资最高前10名员工姓名及其薪资。

35.查询出“飞狐工作室”表中，部门的平均薪资最高的前3个部门名及平均薪资。

36.查询出销售业绩最佳的前3名业务员的员工编号、姓名及该员工的销售总金额。

*
37.查询出“飞狐工作室”表中，与公司中最低薪资相同的员工姓名及薪资（使用子查
询）。

*
38.查询出“飞狐工作室”表中，超过公司中平均薪资80％的员工姓名及薪资（使用
子查询）。

*
39.查询出“客户”表中，哪些客户并没有订购过，要求列出客户编号、公司名称。

（使
用子查询）。

*
40.在s_region表中添加两条记录，id、Name值分别为“001”、“中美/加勒比”，“002”、
“北美”。

41.先建立“北京客户”空表，结构与“客户”表相同，然后从“客户”表选取“北京
市”的客户存入“北京客户”表内。

42.先将“雇员”表数据复制到“我的雇员1”表，然后在“我的雇员1”表中，将目
前薪资都提高10％。

43.先将“雇员”表数据复制到“我的雇员2”表，然后在“我的雇员2”表中，将目
前薪资低于平均薪资调高成平均薪资。

*
44.先将“雇员”表数据复制到“我的雇员3”表，然后在“我的雇员3”表中，将目
前薪资倒数10名内的员工删除掉。

45.先将“雇员”表数据复制到“我的雇员4”表，然后在“我的雇员4”表中，将目
前薪资倒数10名内的员工删除掉。

*
46.先将“客户”表数据复制到“我的客户1”表，然后在“我的客户1”表中，将没
有下过订单的客户删除掉。

*
三、视图设计（2题，共10分）
1.查询出所有订单中订购数量>=100产品，要求包括订单号码、订单日期、要货日期、
产品编号、单价、数量，且按订单号码升序排序。

2.查询出订购产品的“上海市”客户名单，要求在查询结果中显示客户编号、公司名
称、订单号码、订单日期、送货城市、产品、供应商、单价、数量、折扣。

四、存储过程设计(2题，共10分)
1.建立存储过程sp1，要求传递入参数@name，功能是查询出与该参数指定的姓名相
对应的雇员表中的雇员名单，要求包括列：身份证号码、姓名、出生日期、年龄，
最后请写出调用语句。

2.建立存储过程sp2，要求传递入参数@name，功能是将与该参数指定的姓名相对应
的雇员表中的雇员的目前薪资增加20％，最后请写出调用语句。

3.建立存储过程sp3，要求传递入参数@dep（用于表示“部门”），功能是返回＠
avgsalary，表示“雇员”表中该部门的平均薪资，最后请写出调用语句。