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.先建立“北京客户”空表，结构与“客户”表相同，然后从“客户”表选取“北京

市”的客户存入“北京客户”表内。