Oracle数据库查询练习（二）过滤、排序、函数

本文主要是介绍Oracle数据库查询练习（二）过滤、排序、函数，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

本人菜鸡，如有错误，恳请指出。

使用到的函数与关键字

Notes
聚合函数（组函数）：用来输入多个数据，输出一个数据的，如sum,min,max,avg,count
 
为什么where不能跟聚合函数？
聚集函数也叫列函数，它们都是基于整列数据进行计算的，而where子句则是对数据行进行过滤的(这里过滤是在一个记录里边过滤的,基于"行")，在筛选过程中依赖“基于已经筛选完毕的数据得出的计算结果”是一种悖论，这是行不通的。更简单地说，因为聚集函数要对全列数据时行计算，因而使用它的前提是：结果集已经确定！
 
所有包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
 
where子句在查询过程中执行优先级别优先于聚合语句
 
1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
2.GROUP BY 子句用来分组 WHERE 子句的输出。
3.HAVING 子句用来从分组的结果中筛选行。

• case
  

• order by

• group by 传送门（点我）
  

• having

--员工表
create table EMP
(
empno NUMBER(4) primary key,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

--部门表
create table DEPT
(
deptno NUMBER(2) primary key,
dname VARCHAR2(14),
loc VARCHAR2(13)
);


insert into DEPT (deptno, dname, loc)
values (10, '财务', 'NEW YORK');
insert into DEPT (deptno, dname, loc)
values (20, '研发', 'DALLAS');
insert into DEPT (deptno, dname, loc)
values (30, '销售', 'CHICAGO');
insert into DEPT (deptno, dname, loc)
values (40, '运营', 'BOSTON');

--工资等级表
create table SALGRADE
(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

--奖金表
create table BOUNS
(
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER
);

（1） 查询每名员工的empno，ename，sal和dept,要求使用CASE 表达式将各部门由部门号转换为对应的中文名称：10—财务部，20—研发部，30—销售部

SELECT empno,ename,sal,
CASE deptno 
WHEN 10 THEN '财务部'
WHEN 20 THEN '研发部'
WHEN 30 THEN '销售部'
END  "部门"
FROM "EMP";

（2）使用子查询创建表，并将新表中的记录查询出来

CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10;
SELECT * FROM emp10;

（3）统计平均工资大于 500 的部门，显示部门号和平均工资

SELECT deptno "部门号",AVG(nvl(sal,0)+nvl(comm,0)) "平均工资" FROM emp
GROUP BY deptno
HAVING AVG(nvl(sal,0)+nvl(comm,0))>500;

（4）查找部门 30 中得到最多奖金的员工姓名

SELECT ename "员工姓名" FROM "EMP" 
WHERE comm=(
SELECT MAX(nvl(comm,0)) FROM "EMP"
GROUP BY deptno
HAVING deptno = 30
);

（5）查找工资大于自己部门平均工资的员工信息

• 写法一

SELECT * FROM emp  e
WHERE nvl(sal,0)>
(
  SELECT AVG(nvl(sal,0)) FROM "EMP"
  GROUP BY deptno
  HAVING deptno=e.deptno
);

• 写法二

SELECT * FROM emp e WHERE sal > (SELECT AVG(nvl(sal,0)) FROM emp WHERE e.deptno=deptno);

（6）查找平均工资大于 2000 的工作岗位

SELECT job "工作岗位" FROM "EMP"
GROUP BY job
HAVING AVG(nvl(sal,0))>2000;

（7）分组统计每个部门下，每种职位的平均补贴（没奖金的设为0）和总工资(包括奖金)
提示：因为comm属性有空值，需要用nvl()函数处理comm属性

SELECT deptno "部门号",job "职位",AVG(nvl(comm,0)) "平均补贴",SUM(nvl(sal,0)) "总工资" FROM "EMP"
GROUP BY deptno,job;

（8）以工作岗位分组，找出平均工资最高的两种岗位。
提示：排序后在where中指定 rownum < 3;

SELECT * FROM (
SELECT job "工作岗位",AVG(nvl(sal,0)) "平均工资" FROM "EMP"
GROUP BY job
ORDER BY "平均工资" DESC
)
WHERE rownum<3;

这篇关于Oracle数据库查询练习（二）过滤、排序、函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！