SQL标准语言内容大全(8)

演示：根据岗位执行不同的加薪计划。

select last_name, job_id, salary, casewhen(job_id='IT_PROG', 1.1*salary,

casewhen(job_id='ST_CLERK', 1.15*salary, casewhen(job_id='SA_REP', 1.2*salary, salary)) ) revised_salary from hr.employees;

练习-工资等级(case)

（10分钟）用case表达式根据员工的工资高低确定工资等级，5000以下为D，5001-1000为C，10001-15000为B，15000以上为A。显示员工姓氏、工资、工资等级，按照等级和工资进行排序。

5-12 decode函数

1、case函数实现类似if-then-else的逻辑，decode函数则是实现类似switch的逻辑（等值判断）。

decode(expr, case1, result1, [, case2, result2,.....,]

[,default])

表达式等于case1则返回result1，如果等于case2则返回result2，都不满足则返回默认的default。

2、个性化加薪计划

decode(job_id,'IT_PROG', 1.1*salary, 'ST_CLERK',1.15*salary, 'SA_REP', 1.2*salary, salary);

演示-个性化加薪计划(decode)

用decode演示个性化加薪计划。

select last_name,job_id, salary, decode(job_id,'IT_PROG', 1.1*salary,

'ST_CLERK',1.15*salary, 'SA_REP', 1.2*salary, salary) \from hr.employees;

练习-工资等级（decode）

（10分钟）用decode表达式根据员工的工资高低确定工资等级，5000以下为D，5001-1000为C，10001-15000为B，15000以上为A。显示员工姓氏、工资、工资等级，按照等级和工资进行排序。

提示：要把数值区间转换为等值判断，可以用除法和取整（truncate函数）。

练习-个人所得税

（10分钟）显示80部门中每个人需要缴纳的个人所得税，按税金排序，税率规定如下： 2000以下不纳税，

2000-3999税率为9%， 4000-5999为20%， 6000-7999为30%， 8000-9999为40%， 10000-11999为42% 12000-13999为44% 14000以上为 45%

6-1分组函数概念

1、什么是分组函数？

分组函数可以对行集进行操作，并且为每组给出一个结果。可以是整个表，也可以是由表分割成的组

2、分组函数的类型：

AVG:列或表达式的平均值，适用于数值数据类型。 COUNT：取得总计行数。

MAX：列或表达式的最大值，适用于任何数据类型。 MIN：列或表达式的最小值，适用于任何数据类型。

STDDEV：取得列或表达式的标准方差，适用于数值数据类型。当一行数据时返回0。 SUM：列或表达式的总和，适用于数值数据类型。

VARIANCE：取得列或表达式的方差，适用于数值数据类型。当一行数据时返回0。 以上统计都忽略空值,COUNT除外。

6-2分组函数语法

1、SQL语法

SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

2、分组函数的接受参数

AVG（[DISTINCT|ALL]n）：n的平均值，忽略空值

COUNT({*|[DISTINCT|ALL]expr})：*表示所有行，包括重复行和带有空值的行 MAX([DISTINCT|ALL]expr)：expr的最大值，忽略空值 MIN([DISTINCT|ALL]expr)：expr的最小值，忽略空值 STDDEV([DISTINCT|ALL]x)：n的标准偏差，忽略空值 SUM([DISTINCT|ALL]n)：n的总计值，忽略空值 VARIANCE([DISTINCT|ALL]x)：n的方差，忽略空值

3、分组函数的规则：

DISTINCT：只考虑非重复值

ALL：包括重复值在内的所有值，默认为ALL

expr参数的数据类型可以是：CHAR、VARCHAR、NUMBER、DATE 使用GROUP BY时隐式升序排序。

演示- 分组函数使用1

1. 对数字数据使用AVG、SUM、MIN、MAX。

演示- 分组函数使用2

1. 对任何数据使用MIN、MAX。

2. 注：AVG、SUM、VARIANCE和STDDEV只能用于数字数据类型。

练习 - 使用分组函数

显示所有员工的最高薪金、最低薪金、总计薪金和平均薪金。分别将这些列标记为Maxinmum、Minimum、Sum和Average。将结果舍入到最接近的整数。

练习 - 使用分组函数 查看表EMPLOYEES，编写一个查询，使其显示最高薪金和最低薪金的差额。将该列标记为DIFFERENCE。

6-3分组函数-COUNT

1、COUNT（*）可以返回表中行的数量。

三种格式：

COUNT(*)：返回表中满足SELECT条件的行的数量，包括重复的和带有空值的行。 COUNT(表达式)：返回表达式的列中非空值的数量。

COUNT（DISTINCT 表达式）：返回表达式的列中不重复的、非空值的数量。

演示 - 使用COUNT函数1

1. 显示部门50中员工的数量。

演示 - 使用COUNT函数2

1. 显示EMPLOYEES表中部门值的数量，不包括空值。 2. 显示了部门80中能够赚取佣金的员工的数量。

演示 - 使用DISTINCT关键字

1. COUNT（DISTINCT 表达式）返回表达式的不重复的非空值的数量。 2. 显示EMPLOYEES表中不重复的部门值的数量。

练习 - 使用COUNT

1. 显示EMPLYEES表里的总员工数量。

练习 - 使用Count

1. 查看表EMPLOYEES,在不列出经理的情况下，确定他们的人数。将该列标记为

Number of Managers。 提示：使用MANAGER_ID列可以确定经理的人数。

6-4分组函数与空值

1、分组函数会忽略列中的空值。

2、NVL函数可以强制分组函数包含空值。

演示 - 分组函数忽略空值

1. 计算EMPLOYEES表中员工佣金的平均值。在计算过程中自动忽略空值。 2. NVL函数强制包含空值。 3. 两个SQL的结果不一样，因为平均值的计算方法是员工的佣金总和除以有佣金的员

工数。第一条是没有佣金的员工忽略了，第二条是不忽略。

6-5创建数据分组

1、通过GROUP BY子句来进行数据分组。 2、语法

SELECT column, group_function(column) FROM table [WHERE condition]

[GROUP BY group_by_expression] [ORDER BY column];

3、规则

在SELECT子句里出现单个列，在GROUP by子句中也要出现单个列。否则会收到一个错误消息。

? 使用WHERE子句可以将某些行在分组之前排除在外。

? 不能在GROUP BY子句中使用列别名。 ? 默认是按升序排序。

演示 - 使用GROUP BY

1. 显示每个部门的编号和平均薪金。

演示 - 多列使用GROUP BY

1. 对多列使用GROUP BY。

2. GROUP BY首先按照部门编号对行进行分组，再在部门编号组内，按职务对行进行

分组。

练习 - 使用GROUP BY

1. 查看表EMPLOYEES，显示每个职务类型的最低薪金、最高薪金、总计薪金和平均

薪金。将结果舍入到最接近的整数。

练习 - 使用GROUP BY

1. 查看表EMPLOYEES ，编写一个查询以显示拥有相同职务的人员的数量。

6-6分组函数常见错误

1、SELECT子句里的列表中不是分组函数的任何列或表达式都必须在GROUP BY子句中。 2、不能使用WHERE子句来限制组及分组函数 3、可以使用HAVING子句来限制组。

演示 - 分组函数常见错误

1. SELECT子句的列表没有出现在GROUP BY 子句中。

演示 - 分组函数的常见错误

…… 此处隐藏：2047字，全部文档内容请下载后查看。喜欢就下载吧 ……