数据库上机(实验一)作业

在订单数据库OrderDB中，完成如下的查询：（1）查询员工的姓名、职务和薪水。

select employeeName 员工姓名,headShip 职务,salary 薪水from Employee（2）查询姓名中含有“有限”的客户名称和所在地。

select customerName 客户名称,address 所在地from customerwhere customerName like'%有限%'（3）查询出姓“张”并且姓名的最后一个字为“梅”的员工。

select employeeNo 编号,sex 性别,birthday 出生日期,address 地址,telephone 联系电话,employeeName 员工姓名,headShip 职务,salary 薪水from Employeewhere employeeName like'张%梅'（4）查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职务、住址、出生日期和性别。

如果出生日期为空，则显示“不祥”，否则按格式“yyyy—mm—dd”显示，性别用“男”和“女”显示。

select employeeName 员工姓名,department 所属部门,headShip 职务,address 地址,case sexwhen'F'then'女'when'M'then'男'end性别,isnull(convert(char(10),birthday,120),'不详')出生日期from Employeewhere sex like'F'and address like'上海%'or sex like'F'and address like'南昌%'（5）查询出职务为“职员”或职务为“科长”的女员工的信息。

select employeeNo 员工编号,employeeName 员工姓名,case sexwhen'F'then'女'when'M'then'男'end性别,birthday 出生日期,address 地址,telephone 联系电话,hireDate 雇佣时间,department 所属部门,headShip 职务,salary 薪水from Employeewhere sex like'F'and headShip like'职员'or sex like'F'and headShip like'科长'（6）选取编号不在C20050001~C20050004之间的客户编号、客户名称、客户地址。

select customerNo 客户编号,customerName 客户名称,address 地址from Customerwhere customerNo not between'C20050001'and'C20050004'（7）在表OrderMaster中挑选出销售金额大于等于5000元的订单。

先统计订单主表中的订单金额，使用命令：update OrderMaster set orderSum=sum2from OrderMaster a,(select orderNo,sum(quantity *price) sum2from OrderDetailgroup by orderNo) bwhere a.orderNo=b.orderNoselect*from OrderMasterwhere orderSum>=5000（8）在订单主表中选取订单金额最高的前10%的订单数据。

select top 10 percent*from OrderMasterorder by orderSum desc（9）计算一共销售了几种商品。

select count(*)种类from(select distinct ProductNofrom OrderDetail)a（10）计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额，并且依据销售金额有大到小排序输出。

select productNo 商品编号,sum(quantity)销售数量,sum(quantity*price)/sum(quantity)平均销售单价,sum(quantity*price)总销售金额from OrderDetailgroup by productNoorder by sum(quantity*price)desc（11）按客户编号统计每个客户2008年2月的订单总金额。

select a.customerNo 客户编号,orderDate 订单日期,sum(orderSum)总金额from OrderMaster a,Customer bwhere a.customerNo=b.customerNo and year(orderDate)='2008'andmonth(orderDate)='2'group by a.customerNo,orderDate（12）统计至少销售了10件以上的商品编号和销售数量。

select productNo 商品编号,sum(quantity)销售数量from OrderDetailgroup by productNohaving sum(quantity)>=10order by sum(quantity)desc（13）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。

select count(*)员工人数,avg(salary)平均工资from Employeewhere department='业务科'and year(birthday)='1973'or year(birthday)='1967'（14）实验问题①给出SQL语句实现分组聚合操作的执行过程。

答：1，首先执行FROM语句，查找到各表数据2，其次执行WHERE语句，对数据进行筛选以及表与表的连接3，然后执行GROUP BY语句，将数据划分组4，使用聚集函数进行计算5，使用HA VING语句进行筛选分组②WHERE和HA VING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。

答：WHERE子句：作用于整个查询对象，对元组进行过滤。

HA VING子句：作用于分组，对分组内部进行过滤。

必须和GROUP BY配合使用。

举例：WHERE：(1) 查询所有财务部门的员工信息。

SELECT *FROM employeeWHERE department='财务科'HA VING：查询最高分在80以上的每个同学的平均分和最高分SELECT studentNo,avg(score) ,max(score)FROM ScoreGROUP BY studentNoHA VING max(score)>=80（按studentNo分好了组，having在分组内部过滤）③在分组聚集操作中，为什么在查询列中，除了集聚函数运算，其它表达式必须包含在GROUP BY子句中。

答：如果查询列除集聚函数外的表达式不包含在GROUP BY子句中，则聚合没有意义，例如查询客户号，订单号，订单总额。

只按客户号分组的话，语义变得不明确，因为一个客户可能有多张订单，按客户号分组但是订单总额不知道分配到那张订单去。

④分析条件BETWEEN ... AND、AND、OR等关键字的使用方法。

答：BETWEEN ... AND是介于两个值之间的时候的查询条件；AND 连接两个条件，同时成立；OR 连接两个条件，一方成立即可。

⑤请总结SQL语句中的单表查询语句的使用方法。

答：⒈查询所有列SELECT * FROM [表名]2.查询指定列SELECT [字段列表] FROM [表名]3. 消除重复元组：SELECT Distinct [字段列表] FROM [表名]4.查询经过计算的列SELECT 【字段及字段表达式】 FROM 【表名】5. 指定查询结果的查询SELECT TOP n [PERCENT] 【字段列表】 FROM 【表名】6.给属性取别名①：SELECT 【字段名】1 as ''A'' FROM 【表名】②：SELECT "A"=字段名1 FROM 【表名】③：SELECT 字段名1 "A" FROM 【表名】7.选择查询SELECT 【字段名列表】 FROM 【表名】 WHERE 【条件表达式】条件表达式运算符：比较运算：=,<,<=,>,>=,!<,!>,!=,<>逻辑查询：or,AND,not范围查询：between… AND,not between …AND集合查询：IN,not IN字符匹配查询：ike,not LIKE空值查询：is null,is not null8.排序查询SELECT 【字段名1】，【字段名2】 FROM 【表名】WHERE 【条件表达式】ORDER BY 【字段表达式1】，【字段表达式2】 [ASC][DESC]9.分组查询①SELECT 字段名列表 FROM 表名 [WHERE 条件表达式]GROUP BY 字段名②SELECT 字段名列表 FROM 表名 [WHERE条件表达式] GROUP BY 字段名 HAVING 筛选表达式10.聚合查询SELECT count（[ALL| DISTINCT]{*|字段名列表}） FROM 表名[WHERE 条件表达式]SELECT sum| avg| max| min ([ALL| DISTINCT]<字段名列表>）FROM 表名[WHERE 条件表达式]。