SQL Server综合练习题
说明：
请在做题前找到随题文件北风贸易.mdf和北风贸易.ldf，附加数据库并指定数据库名为“w+学号最后两位+姓名”，如“w23张三”，以下所有的操作均完成在这个数据库中，请严格按照题目要求答题，包括字段次序。
一、基础设计（共40分）
业务介绍：
Sporting Goods是一家美国批发公司，接收来自世界范围内的运动产品零售商店的定单。
该公司的客户遍布国内外，每一个客户都有一个唯一标识号。
公司必须保存有客户的商店名称和电话号码、地址、所在城市、州、国家、邮政编码、
信誉等级以及对客户喜好的总体评论。
公司需跟踪每一份定单的标识号、订货日期及付款方式。 客户所在区域有唯一的名称及标识号。
每个区域有一个仓库，仓库具有唯一的标识号及其它信息，包括地址、城市、州、、国
家、邮政编码、经理ID及电话号码等。 库存中每一件商品具有唯一的标识号，还必须跟踪产品价格、订购的数量及已发送的数
量。
公司对每一种产品，必须知道它唯一的产品标识号及名称。
公司有一些雇员或销售代表被派往多个地区，对每一雇员，公司备有其姓、名、唯一的
标识号及计算机登录ID，其他信息包括该雇员进入公司的时间、评语、职务、工资及佣金百分率。
对每一个仓库及它存储的产品，公司货单记录有每个产品的存储量、再订货点等。
1) 建立下列9张表
（1）s_customer表（描述客户信息） 列名 ID Name Phone Address City State County Zip_code Credit_rating Sales_rep_id Region_id comments 第 1 页 共 6 页
描述/数据类型 客户唯一的标识号，最长3字符 客户名，最长20字符 客户的电话号码，最长20字符 客户地址，最长20字符 客户所在城市，最长20字符 客户居住地所在州，最长15字符 客户居住的国家，最长20字符 客户的邮政编码，最长15字符 客户的信誉等级，最长9字符 客户的销售代表，最长3字符 客户居住的国家所在的区域，最长3字符 客户喜好的产品，最长255字符 SQL Server综合练习
（2）s_dept表（描述公司各部门信息） 列名 ID Name Region_id 描述/数据类型 每个部门唯一的标识号，最长3字符 部门名称，最长20字符 部门所在的区域ID，最长3字符
（3）s_region表（描述地区信息） 列名 ID Name 描述/数据类型 每个地区唯一的标识号，最长3字符 每个区域的名称，最长20字符
（4）s_emp表（描述公司雇员信息） 列名 ID Last_name First_name Userid Start_date Comments Manager_id Title Dept_id Salary Commision_pct 描述/数据类型 每个雇员唯一的标识号，最长3字符 雇员姓氏，最长20字符 雇员名字，最长20字符 雇员的计算机登录ID，最长8字符 雇员开始在公司工作的日期，日期数据类型 雇员的有关描述信息，最长25字符 雇员的经理的ID，最长3字符 雇员在公司里的职务，最长25字符 雇员的部门ID，最长3字符 雇员的工资 雇员赢得的佣金百分率，共4位数，包括2位小数
（5）s_warehouse表（描述仓库信息） 列名 ID Region_id Address City State Zip_code Country Phone Manager_id 描述/数据类型 每个仓库唯一的标识号，最长7字符 仓库所在区域ID，最长3字符 仓库地址，最长20字符 仓库所在城市，最长20字符 仓库所在州，最长15字符 仓库所在城市邮政编码，最长15字符 仓库所在国家，最长20字符 仓库的电话号码，最长20字符 仓库经理的ID，最长3字符
（6）s_product表（描述产品信息） 列名 Id Name Short_desc Suggested_whlsl_price Whlsl_units 描述/数据类型 产品唯一标识号，最长7字符 产品名，最长25字符 对产品的描述，最长255字符 产品的建议批发价 批发的产品单位，最长10字符 第 2 页 共 6 页
SQL Server综合练习
（7）s_inventory表（描述存货信息） 列名 Product_id Warehouse_id Amount_in_stock Reorder_point Max_in_stock Out_of_stock_explanation Restock_date 描述/数据类型 产品唯一的标识号，最长7字符 存储产品的仓库ID，最长7字符 库存中每件产品的数量，整型 需要再订货的库存产品的最低数量，整型 库存产品的最大值，整型 产品无货的原因，最长255个字符 产品再存货的日期，日期数据类型
（8）s_ord表（描述订单信息） 列名 ID Customer_id Date_ordered Date_shipped Sales_rep_id Total Payment_type Order_filled 描述/数据类型 每个定单唯一的标识号，最长3字符 客户的唯一的标识号，最长3字符 定单的定货日期，日期类型 定单的发运日期，日期类型 负责定单的销售代表的唯一标识号，最长3字符 定单的总金额 支付方式，最长6字符 指出定单是否已经填写，最长1字符
（9）s_item表（描述项目信息） 列名 Ord_id Item_id Product_id Price Quantity Quantity_shipped 描述/数据类型 与项目有关的定单ID，最长3字符 分配给每个项目的唯一的标识号，最长7字符 与该项目有关的产品ID，最长7字符 该项目的价格 该项目的数量，整型 已知产品的定单中该项目的发运数，整型 2) 参照完整性约束
表名 S_dept S_emp S_emp S_customer S_customer S_ord S_ord S_item 外码 Region_id Manager_id Dept_id Sales_rep_id Region_id Customer_id Sales_rep_id Order_id 表s_region的ID 表s_emp的ID 表s_dept的ID 表s_emp的ID 表s_region的ID 表s_customer的ID 表s_emp的ID 表s_ord的ID 参照表 第 3 页 共 6 页
SQL Server综合练习 S_item S_warehouse S_warehouse S_invertory S_invertory
Product_id Manager_Id Region_id Product_id Warehouse_id 表s_product的ID 表s_emp的ID 表s_region的ID 表s_product的ID 表s_warehouse的ID 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. 查询出“飞狐工作室”表中，家庭地址中含_字符的员工编号、姓名。*
第 4 页 共 6 页
SQL Server综合练习
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. 先建立“北京客户”空表，结构与“客户”表相同，然后从“客户”表选取“北京
市”的客户存入“北京客户”表内。
第 5 页 共 6 页
SQL Server综合练习
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，表示“雇员”表中该部门的平均薪资，最后请写出调用语句。
第 6 页 共 6 页
因篇幅问题不能全部显示，请点此查看更多更全内容