单行函数习题，组函数|多行函数 ，分组

--函数
--单行函数 : 一行记录返回一个结果
--多行函数|组函数|聚合函数 : 多条记录返回一个结果  

--单行函数
-- 1.当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

--2. 2天以后日期
-- 日期可以进行+-
select sysdate,sysdate+2 from dual;

-- 3.所有员工入职的3天后是几号
select empno,ename,sal,hiredate,hiredate-3 from emp;

-- 4.查询所有员工的试用期期到期（转正的日期） 3个月试用期
select empno,ename,sal,hiredate,hiredate+90 from emp;
--add_months(日期,月数)
select empno,ename,sal,hiredate,add_months(hiredate,3) from emp;

-- 5.查询所有员工到目前为止一共工作了几个月
select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;

-- 6.查询当前月的最后一天
select last_day(sysdate) from emp;

-- 7.下一个星期三是几号
select next_day(sysdate,'星期三') from dual;

-- 8.日期对象 与 字符串之间转换问题
--to_char(日期对象,'模板')

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--to_date(日期字符串,'模板')
select to_date('2021-7-29 15:37:47','yyyy-mm-dd hh24:mi:ss') from dual;

-- 9.查询82年入职员工的信息

select *
  from emp
 where hiredate between to_date('1982-01-01', 'yyyy-mm-dd') and
       to_date('1982-12- 31', 'yyyy-mm-dd');

--聚合函数|组函数|多行函数
-- count(*|字段|伪列)  sum()  max()   min()  avg()
--注意:
   --如果select后面出现组函数 的使用,只能和组函数或者分组字段一起使用

-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;

-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno  = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;

-- 请查询出 20部门的平均工资， 部门编号
select avg(sal),deptno from emp where deptno = 20 group by deptno;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;
--null值不参与组函数的计算

-- 统计有奖金的员工有几个
select count(1) from emp where  comm is not null;
select count(comm) from emp;

-- 查询 最高薪水的员工姓名,及薪水
select max(sal) from emp;

select ename,sal from emp where sal = (最高薪资);
select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal <(select avg(sal) from emp);

-- 查看高于本部门平均薪水员工姓名
select ename from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno = e2.deptno);
 

--分组: group by 分组字段1,分组字段2
--查询: select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
--流程: from-->where --> group by --> having --> select --> order by 

--注意 : 
     --一旦分组,分组之后的行为操作都是以组为单位,只能看到有多少组,每组的标签(分组字段的值),但是看不到组中的数据
     --一旦分组,select后面只能查询分组字段或者组函数
     --where中不能使用组函数

-- 找出每个部门的最高工资 
select deptno,max(sal),min(sal),sum(sal),avg(sal),count(1) from emp group by deptno;

-- 找出20部门和30部门的每个部门最高工资 
--1)先过滤后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno order by max(sal) desc;

--2)先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal),deptno from emp where sal>1000  group by deptno having deptno in(10,20);

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询 最低平均工资的部门编号
select deptno from emp group by deptno having avg(sal) = (最低平均工资);

--最低平局工资
select min(avg(sal)) from emp group by deptno;
--1)
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

--2)
select *
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal = (select min(avg(sal)) from emp group by deptno);