（尚硅谷）Orcal sql 分组函数 子查询

<span style="font-size:18px;">第5节 分组函数
1.分组函数：avg count max min sum
2.使用group by 进行分组
3.使用having 进行分组数据的过滤
--1.avg()/sum():只试用与number类型的数据
SELECT AVG(salary) , sum(salary)
FROM employees;

select avg(last_name) 
from employees;

--2.max() min() count() :适用于number,Date类型 varchar2类型 
select max(salary) , min(salary) , max(last_name) , min(last_Name) ,
max(hire_date) , min (hire_date)
from employees

--count 在累加个数时不计算空值
select count (employee_id) , count (salary) , count(1) , count(2),
count(*) , count(commission_pct)
from employees;

select commission_pct
from employees
where commission_pct is not null ;

--结论:avg = sum / count
select avg(salary) , sum(salary)/count(salary)
from employees

select avg(commission_pct) , sum(commission_pct)/count(commission_pct),
sum(commission_pct)/count(1)
from employees;

select count(nvl(commission_pct , 0))
from employees;

select count(distinct nvl(department_id , 0))
from employees;

--2.使用group by 
-- 求出employees表中各部门的平均薪资
----结论：如果在查询项里出现了分组函数以外的列，
--那么分组函数以外的列都应该出现在group by当中，否则出错。
select department_id , avg(salary)
from employees
group by department_id;

select job_id , avg(salary)
from employees
group by job_id;

select department_id , job_id , avg(salary)
from employees
group by department_id , job_id ;

--包含在group by 子句中的列不必包含在select 列表中
select department_id , avg(salary)
from employees
group by department_id , job_id;


--3使用having 进行过滤
--如果过滤条件中出现了分组函数，使用having 替代where
select department_id , avg(salary)
from employees
--having avg(salary)>600
group by department_id
having avg(salary)>600;

--部门最高工资比1000高的部门
select department_id , max (salary)
from employees
having max(salary)>10000
group by department_id;

--分组函数可以嵌套
select max(avg(salary))
from employees
group by department_id;

练习
--查询公司员工工资的最大值，最小值，平均值，总和
select max (salary) , min (salary) ,avg(salary),sum(salary)
from employees;
--查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id , max(salary) , min(salary) , avg(salary) , sum(salary)
from employees 
group by job_id;
--选择具有各个job_id的员工人数
select job_id , count(employee_id)
from employees
group by job_id;
--查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary) , min(salary) , max(salary)-min(salary)"difference"
from employees;
--查询各个管理者手下员工的最低工资，其中最低工资不能低于
--6000，没有管理者的员工不计算在内
select manager_id , min(salary)
from employees
group by manager_id
having min(salary) >= 6000 and manager_id is not null;

--查询所有部门的名字，location_id，员工数量和工资平均值
select department_name , location_id , count(employee_id) , avg(salary)
from   departments d join employees e
on e.department_id = e.department_id
group by location_id ,department_name ;

--查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式
select hire_date , count(employee_id)
from employees
group by hire_date
having hire_date between to_date('1995','yyyy') and  to_date('1998','yyyy');

select count(1) ,
 count(decode( to_char(hire_date , 'yyyy'),'1995',1 ,null ))"1995",
 count(decode( to_char(hire_date , 'yyyy'),'1996',1 ,null ))"1996",
 count(decode( to_char(hire_date , 'yyyy'),'1997',1 ,null ))"1997",
 count(decode( to_char(hire_date , 'yyyy'),'1998',1 ,null ))"1998"
 from employees
 where to_char(hire_date , 'yyyy') IN('1995' , '1996' , '1997' ,'1998');




*******************************************************
第六节 子查询
--子查询
--外查询
select last_name , salary
from employees
where salary >(
         --内查询
         SELECT salary
         from employees
         where last_name='Abel'
         );
--查询last_name 为'Chen'的manager的信息
select manager_id
from employees
where last_Name = 'Chen';

select last_name , salary
from employees
where employees_id = 108;

--自连接
select e2.last_name , e2.salary
from employees e1 , employees e2 
where e1.manager_id = e2.manager_id 
and e1.last_name = 'Chen';

--子查询
select last_name , salary 
from employees
where employee_id = (
                    select manager_id
                    from employees
                    where last_name = 'Chen'
                  );

--如何书写一个包含子查询的select语句？第一种方式：
--从里向外写  第二种方式：从外向里写。

--返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
select last_name , job_id , salary
from employees 
where job_id = (
               select job_id
               from employees
               where employee_id = 141
             ) 
select last_name , job_id ,salary
from employees 
where job_id = (
               select job_id
               from employees
               where employee_id = 141
             )
and salary > (
             select salary
             from employees
             where employee_id =143
             )

--返回公司工资最少的员工的last_name,job_id和salary
select last_name , job_id ,salary
from employees
where salary = (
               select min(salary)
               from employees
             )

--查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id , min(salary)
from employees 
group by department_id
having min(salary) > (
                       SELECT min(salary) 
                       from employees
                       group by department_id---???????
                       having  department_id = 50
                     )
--如下的操作是错误的
select employee_id , last_name 
from employees
where salary = (
               select MIN(salary)
               from employees
               group by department_id
             )


select last_name , job_id
from employees
where job_id = (
               select job_id
               from employees
               where last_name = 'Haas'
              )
--查询工资最低的员工信息: last_name, salary
select last_name , salary
from employees
where salary = (
               select min(salary)
               from employees
             )
--查询平均工资最低的部门信息
select * 
from departments
where department_id = (
                      select department_id
                      from employees 
                      group by department_id 
                      having avg(salary) = (
                                             select min(avg(salary))
                                             from employees
                                             group by department_id
                                           )
                    )
--查询平均工资最低的部门信息和该部门的平均工资
select * 
from departments
where department_id = (
                       select department_id 
                       from employees 
                       group by department_id
                       having avg(salary) = (
                                            select  Min(avg(e.salary))
                                            from employees e
                                            group by department_id
                                           )
                      )
第六节
--谁的工资比Abel的高
--子查询
--外查询
select last_name , salary
from employees
where salary > (
             --内查询
             select salary
             from employees 
             where last_Name = 'Abel'
             )
--查询last_name为'Chen'的manager的信息
select *
from employees
where employee_id IN (
                             select manager_id
                             from employees
                             where last_name = 'Chen'
                          )
--自连接
select d.*
from employees e , employees d
where e.manager_id = d.employee_id and e.Last_name = 'Chen'

--子查询
select last_name , salary 
from employees
group by manager_id
having employee_id = (
                    select manager_id
                    from employees
                    where last_name = 'Chen'
                    )
select *
from employees 
where employee_id = (
                      select manager_id 
                      from employees
                      where last_name ='Chen'
                    )

--返回job_id与141号员工相同，
--salary比143号员工多的员工姓名，job_id 和工资
select last_name , job_id , salary
from employees
where job_id in(
                   select job_id 
                   from employees
                   where employee_id = 141
                 ) and salary > (
                                     select salary
                                     from employees
                                     where employee_id =143
                                )
select last_name , job_id salary
from employees
where job_id = (
                 select job_id 
                 from employees
                 where employee_id = 141
               )
and salary > (
               select salary
               from employees
               where employee_id = 143
             )
--返回公司工资最少的员工的last_name,job_id和salary
select last_name ,job_id ,salary
from employees
where salary = (
                 select min(salary)
                 from employees               
               )
--查询最低工资大于--50号部门最低工资--的部门id和其最低工资
select  department_id , min(salary)
from employees 
group by department_id
having min(salary) > (
                 select min(salary)
                 from employees 
                 where department_id = 50
               )

select last_name , job_id 
FROM employees
where job_id = (
               select job_id
               from employees
               where last_name = 'Chen'
             )
--查询工资最低的员工信息: last_name, salary	
select last_name , salary
from employees
where salary = (
                 select min(salary)
                 from employees
               )
--查询平均工资最低的部门信息
select * 
from departments
where department_id = (
                        select department_id 
                        from employees
                        group by department_id 
                        having avg(salary)= (
                                             select min(avg(salary))
                                              from employees
                                              group by department_id 
                                            )
                      )
                    
--查询平均工资最低--的部门信息和该部门的平均工资
select d.* , ( select avg(salary)
              from employees e 
               where e.department_id =d.department_id
              )
from departments d 
where department_id = (
                      select department_id 
                      from employees e1
                      group by department_id 
                      having avg(salary) = (
                                           select min(avg(salary))
                                           from employees
                                           group by department_id
                                          )
                     )
--查询平均工资最高的 job 信
select *
from jobs
where job_id = (
                select job_id
                from employees
                group by job_id
                having avg(salary) = (
                                       select min(avg(salary))
                                       from employees
                                       group by job_id
                                     )
               )
--查询平均工资高于公司平均工资的部门有哪些?
select department_name 
from departments
where department_id IN(
                    select department_id 
                    from employees
                    group by department_id 
                    having avg(salary)> (
                                         select min(avg(salary))
                                         from employees
                                         group by department_id
                                        )
                    )
--查询出公司中所有 manager 的详细信息.
select * from 
employees
where employee_id in (
                     select manager_id
                     from employees
                     group by manager_id
                  )
--各个部门中 最高工资中最低的那个部门的 
--最低工资是多少
select min(salary)
from employees
group by department_id 
having  department_id  =(
                           select department_id
                           from employees
                           where salary = (
                                              select max(salary)
                                               from employees
                                          )
                         )
--查询平均工资最高的部门的 manager
-- 的详细信息: last_name, department_id, email, salary 
select last_name , department_id , email , salary
from employees
where employee_id in (
                     select distinct manager_id 
                     from employees
                     where department_id = (
                                                select department_id 
                                                from employees
                                                group by department_id
                                                having avg(salary) = (
                                                                     select max(avg(salary))
                                                                     from employees
                                                                     group by department_id
                                                                     )
                                            )
                    )


select last_name, department_id, email, salary
from employees
where employee_id in (
                     select distinct manager_id
                     from employees
                     where department_id = (
                                           select department_id
                                           from employees
                                           group by department_id
                                           having avg(salary) = (
                                                                select max(avg(salary))
                                                                from employees
                                                                group by department_id
                                                                )
                                           )
                     )
                     
--查询 1999 -----年来公司的人------所有员工的最高工资---的那个员工的信息.
--1.查询出1999年来公司的职工的最高工资
--2.找到最高工资为11000的员工
select *
from employees
where employee_id in(
                     select employee_id
                     from employees e1
                     where salary =  ( 
                                       select min(salary)
                                       from employees  e2
                                       where hire_date like '%97'
                                     )and hire_date like '%97'
                    )
--返回其它job_id中比job_id为‘IT_PROG’
--的任一工资低的员工的员工号、姓名、job_id

--这样是不对的
select min(salary),hire_date
from employees
group by hire_dat
having
hire_date like '%99'
                                       
--这是个对的                                       
select salary,hire_date
from employees
where salary = (
           select min(salary)
           from employees  e2
           where hire_date like '%99'
          ) 
and hire_date like '%97'

--查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
--1.查询出1999年来公司的职工的最高工资
--2.找到最高工资为11000的员工

select *
from employees
where salary in (
                   select max(salary)
                   from employees
                   where to_char(hire_date , 'yyyy') = '1999'
               )and to_char(hire_date , 'yyyy') = '1999'

--返回其它job_id中比job_id为‘IT_PROG’的任一工资低的员工
--的员工号、姓名、job_id 以及salary
select job_id ,employee_id , last_name , job_id ,salary
from employees
where salary > (
               select min(salary)
               from employees
               group by job_id
               having job_id = 'IT_PROG'
             )
and job_id <> 'IT_PROG'
练习
--查询和Zlotkey相同部门的员工姓名和雇用日期
select last_name , hire_date
from employees
where department_id = (
                        select department_id
                        from employees
                        where last_name ='Zlotkey'
                      )
--查询工资---比公司平均工资--高的员工的员工号，
--姓名和工资。
select employee_id , last_Name , salary
from employees
where salary >(
                 select avg(salary)
                 from employees
               )
--查询各部门中工资比本部门平均工资
--高的员工的员工号, 姓名和工资

select employee_id , last_name , salary
from employees 
where salary > (
                 select avg(salary)
                 from employees
               )
--查询和姓名中包含字母u的员工在相同部门的
--员工的员工号和姓名
select employee_id , last_name
from employees
where department_id in (
                        select department_id
                        from employees
                        where last_name like '%u%'
                      )
--查询在部门的location_id为1700的部门
--工作的员工的员工号
select employee_id 
from employees
where department_id IN (
                        select distinct department_id
                        from departments
                        where location_id = 1700
                       )
--查询管理者是King的员工姓名和工资
select last_name , salary
from employees
where manager_id in (
                   select employee_id 
                   from employees
                   where last_name = 'King'
                   )
</span>