JAVA ORACLE DAY04
1 Oracle子查询综合示例
1.1 问题
对于emp表和dept表，完成如下各个案例。
1)执行下列SQL语句：
SELECT ename,sal FROM emp WHERE sal= (SELECT sal FROM emp WHERE ename= 'smith' OR deptno=20);
这条语句出错的原因在于（）。
A．子查询中不能出现where子句。
B．逻辑运算符OR不允许出现在where子句中。
C．子查询得到多行结果，主查询中使用的是单行比较运算符。
D．子查询得到单行结果，主查询中使用的是多行比较运算符。
2)写SQL语句，查询哪个部门的平均工资是最高的，列出部门编码、平均工资。
3)写SQL语句，列出各个部门中工资最高的员工的信息：名字、部门号、工资。
4)写SQL语句，查询管理者是“KING”的员工姓名（ename）和工资（sal）。
5)写SQL语句，查询部门所在地（loc）为“NEW YORK”的部门的员工的姓名（ename），部门名称（dname）和岗位名称（job）。
6)写SQL语句，查询工资比公司平均工资高的所有员工的员工号（empno），姓名（ename）和工资（sal）。
7)写SQL语句，查询姓名中包含字母“u”的员工在相同部门的员工的员工号（empno）和姓名（ename）。
8)写SQL语句，查询哪些员工的薪水比本部门的平均薪水低。
1.2 步骤
实现此案例需要按照如下步骤进行。
步骤一:子查询得到多行结果，主查询中要求使用多行比较运算符
案例中，子查询SQL语句：
SELECT sal FROM emp WHERE ename= 'smith' OR deptno=20；
该SQL语句返回多条数据，对于这种返回多条数据的情况，Oracle要求使用多行比较运算符。本案例中，在主查询中使用了“=”运算符，它属于单行运算符，因此，在此案例中，正确的答案为C。
步骤二：查询哪个部门的平均工资是最高的
首先，使用group by子句配合子查询，查询出平均工资最高的部门，SQL语句如下所示：
select max(avg(sal)) from emp group by deptno；
然后，将上述查询的结果作为主查询的条件，进一步查询出平均工资最高的部门的部门编码，SQL语句如下所示：
select deptno, avg(sal) from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);
上述SQL语句中，设置分组的条件使用了having子句。
步骤三：列出各个部门中工资最高的员工的信息
首先，使用group by 子句配合组函数查询出各个部门的最高工资，SQL语句如下所示：
select deptno , max(sal) from emp group by deptno；
然后，将上述查询的结果作为where条件的值，即使用in操作符判断哪些员工与上述查询的结果集中的任一数据相等，SQL语句如下所示：
select ename , sal , deptno from emp where (deptno , sal) in (select deptno , max(sal) from emp group by deptno);
步骤四：查询管理者是“KING”的员工姓名（ename）和工资（sal）
首先，查询员工“KING”的员工编号，SQL语句如下所示：
select empno from emp where ename = 'KING'；
然后，将上述查询的结果作为where条件的值，当员工的管理者ID（mgr）等于上述查询结果时，该员工的管理者即为“KING”，SQL语句如下所示：
select ename, sal from emp where mgr = (select empno from emp where ename = 'KING');
步骤五：查询部门所在地（loc）为“NEW YORK”的部门的员工信息
首先，查询部门所在地为“NEW YORK”的部门编号，SQL语句如下所示：
select deptno from dept where loc = 'NEW YORK'；
然后，将上述查询的结果作为where条件的值，查询部门所在地为“NEW YORK”的部门里的所有员工的员工号（empno）、姓名（ename）和工资（sal）。SQL语句如下所示：
select e.ename, d.dname, e.job from emp e join dept d on e.deptno = d.deptno where e.deptno = (select deptno from dept where loc = 'NEW YORK');
由于需要显示部门名称（dname），因此在上述SQL语句中，使用join... on将表emp和表dept做内连接查询。
步骤六：查询工资比公司平均工资高的所有员工信息
首先，查询所有员工的平均工资，SQL语句如下所示：
select avg(sal) from emp；
然后，将上述查询的结果作为where条件的值，当职员表中的某条记录的工资（sal）大于上述查询结果时，该员工的工资即高于公司的平均工资，SQL语句如下所示：
select empno, ename, sal from emp where sal> (select avg(sal) from emp);
步骤七：查询姓名中包含字母“u”的员工在相同部门的员工信息
首先，使用like运算符查询姓名中包含字母“u”的员工的员工编号，SQL语句如下所示：
select deptno from emp where ename like '%U%'；
然后，将上述查询的结果作为where条件的值，当职员表中的某条记录的部门编号等于上述查询结果中的任意一个时，该员工所在的部门与姓名中包含字母“u”的员工的部门相同，SQL语句如下所示：
select empno, ename from emp where deptno in (select deptno from emp where ename like '%U%');
步骤八：查询哪些员工的薪水比本部门的平均薪水低
本题可以使用关联子查询来解决，关联子查询的语法如下所示：
SELECT column1, column2, …
FROM table1 o
WHERE column1 operator
(SELECT column
FROM table2 i
WHERE i.expr1 = o.expr2)
本题中要解决的问题为如何在主查询和子查询中来表示同一部门，SQL语句如下所示：
select avg(sal) from emp b where b.deptno = a.deptno；
其中a.deptno的a为主查询中emp表的别名。
将上述查询作为主查询的条件，查询哪些员工的薪水比本部门的平均薪水低，SQL语句如下所示：
selectename, sal, deptno from emp a where sal < ( select avg(sal) from emp b where b.deptno = a.deptno ) ;
2 Oracle分页查询综合示例
2.1 问题
根据上一案例中的表结构和示例数据，完成如下查询：
1)查询emp表前5条记录
2)查询第3-5条记录，无需排序
3)查询公司工资最高的三个人
4)查询公司工资最低的五个人
2.2 步骤
实现此案例需要按照如下步骤进行。
步骤一:查询emp表前5条记录
使用ROWNUM伪列，查询emp表前5条记录，SQL语句如下所示：
select * from emp where rownum <= 5;
ROWNUM是一个伪列，对查询返回的行编号即行号，由1开始依次递增。注意：Oracle的rownum数值是在获取每行之后才赋予的，因此使用“rownum>数字”是查询不到数据的。
步骤二:查询第3-5条记录，无需排序
首先，利用ROWNUM截取结果集中的部分数据，需要用到行内视图，SQL语句如下所示：
select rownum num, e.* from emp e；
上述SQL语句中给ROWNUM一个别名num。
然后，将上述查询的结果作为一张虚表，其中，num为这张表中的一列。查询第3-5条记录，SQL语句如下所示：
select * from (select rownum num, e.* from emp e) where num >= 3 and num <= 5;
步骤三:查询公司工资最高的三个人
首先，按工资降序排列员工数据，SQL语句如下所示：
select * from emp order by sal desc；
然后，将上述查询的结果作为一张虚表，再根据ROWNUM的特性查询公司工资最高的三个人，SQL语句如下所示：
select * from (select * from emp order by sal desc) where rownum <= 3;
步骤四:查询公司工资最低的五个人
首先，按工资升序排列员工数据，SQL语句如下所示：
select * from emp order by sal；
然后，将上述查询的结果作为一张虚表，再根据ROWNUM的特性查询公司工资最低的五个人，SQL语句如下所示：
select * from (select * from emp order by sal) where rownum <= 5;
3 Oracle高级查询综合示例
3.1 问题
本案例的详细要求如下：
1）计算职位的人数，其中，“ANALYST”和“MANAGER”职位用“VIP”表示，其余是普通员工，职位用“OPERATION”显示。分别计算VIP职位和普通职员职位的人数，数据显示效果如图-1所示。
图-1
2）将dept表中的数据中按”OPERATIONS”、“ACCOUNTING”、“SALES”进行自定义排序。
3）按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码，要求使用ROW_NUMBER函数完成。
4）将职员表中按照部门编码分组，同组内按薪水降序排列，相同薪水则按奖金数升序排列，并显示等级标识，用Rank_ID表示，要求使用RANK函数完成。
5）关联emp和dept表，按照部门编码分组，每组内按照员工薪水升序排列，列出员工的部门名字、姓名和薪水及等级drank，要求使用DENSE_RANK函数完成。
6）创建sales_tab，该表存储了2010-2011年每月每天的销售额，创建表的SQL语句如下所示：
CREATE TABLE sales_tab ( year_id NUMBER NOT NULL, month_id NUMBER NOT NULL, day_id NUMBER NOT NULL, sales_value NUMBER(10,2) NOT NULL );
向sales_tab表中插入测试数据，SQL语句如下所示：
INSERT INTO sales_tab SELECT TRUNC(DBMS_RANDOM.value(2010, 2012)) AS year_id, TRUNC(DBMS_RANDOM.value(1, 13)) AS month_id, TRUNC(DBMS_RANDOM.value(1, 32)) AS day_id, ROUND(DBMS_RANDOM.value(1, 100), 2) AS sales_value FROM dual CONNECT BY level <= 1000;
以上SQL语句向sales_tab表中插入1000条随机数据，年的范围为2010-2011年，月范围为1-12月，日范围为1-31天，销售额的范围为1到100（不包括100）的随机浮点数。
请统计2010-2011年每月每日的销售额，要求使用ROLLUP函数来完成。
7）统计年月的销售额，要求使用CUBE函数来完成。
8）统计年月的销售额，要求使用GROUPING SETS函数来完成。
3.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：使用DECODE函数，按要求显示数据
使用DECODE函数，计算职位的人数，“ANALYST”和“MANAGER”职位用“VIP”表示，其余是普通员工，职位用“OPERATION”表示。分别计算VIP职位和普通职员职位的人数，SQL语句如下所示：
SELECT DECODE(job,
'ANALYST', 'VIP',
'MANAGER', 'VIP',
'OPERATION') job,
COUNT(1) job_cnt
FROM emp
GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');
步骤二：使用DECODE函数实现自定义排序
使用DECODE函数，将dept表中的数据按”OPERATIONS”、“ACCOUNTING”、“SALES”进行自定义排序，SQL语句如下所示：
SELECT deptno, dname, loc FROM dept ORDER BY DECODE(dname, 'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);
步骤三：使用ROW_NUMBER函数实现分组排序
使用ROW_NUMBER函数按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码，SQL语句如下所示：
SELECT deptno, ename, empno, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS emp_id FROM emp;
ROW_NUMBER函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。
步骤四：使用RANK函数实现分组排序
使用RANK函数将职员表中按照部门编码分组，同组内按薪水降序排列，相同薪水则按奖金数升序排列，并显示等级标识，用Rank_ID表示，SQL语句如下所示：
SELECTdeptno, ename, sal, comm, RANK() OVER (PARTITIONBYdeptno ORDERBYsalDESC, comm) "Rank_ID" FROMemp;
RANK函数排序后的等级标识是跳跃的，即如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。
步骤五：使用DENSE_RANK函数实现分组排序
关联emp和dept表，使用DENSE_RANK函数，按照部门编码分组，每组内按照员工薪水升序排列，列出员工的部门名字、姓名和薪水及等级drank，SQL语句如下所示：
SELECTd.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITIONBYe.deptnoORDERBYe.sal) ASdrank FROMempejoindeptd one.deptno = d.deptno;
DENSE_RANK函数排序后的等级标识是连续的，即如果有并列第二，下一个排序将是三，这一点是和RANK函数不同，RANK是跳跃排序。
使用ROLLUP函数统计2010-2011年每月每日的销售额，SQL语句如下所示：
SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value FROM sales_tab GROUP BY ROLLUP (year_id, month_id, day_id) ORDER BY year_id, month_id, day_id;
上述SQL语句会依次按照（year_id, month_id, day_id）分组、（year_id, month_id）分组、（day_id）分组以及全表分组。
使用CUBE函数统计年月的销售额，SQL语句如下所示：
SELECT year_id, month_id, SUM(sales_value) AS sales_value FROM sales_tab GROUP BY CUBE (year_id, month_id) ORDER BY year_id, month_id;
上述SQL语句会依次按照(year_id, month_id)分组、（year_id）分组、（month_id）分组以及全表分组。
步骤八：使用GROUPING SETS函数统计销售额
使用GROUPING SETS函数统计年月的销售额，SQL语句如下所示：
SELECT year_id, month_id, SUM(sales_value) FROM sales_tab GROUP BY GROUPING SETS ((year_id), (month_id)) order by 1, 2;
上述SQL语句会依次按照(year_id)分组、(month_id)分组。