ql/sql---子查询练习01

--1，查询工资最低的员工信息：last_name,salary

select last_name,salary
from employees
where salary=(select min(salary)
              from employees
              )
              
--2，查询平均工资最低的部门信息

--1.查询公司中各部门的平均工资是多少

select svg(salary)
from employees
group by department_id

--2.查询公司中各部门的平均工资中最少

select min(svg(salary))
from employees
group by department_id

--3.哪个部门的平均工资=2.结果

select department_id
from employees
having avg(salary)=(
                    select min(svg(salary))
                    from employees
                    group by department_id
                   )
group by department_id;

--4.查询此部门的部门信息

select *
from departments
where department_id=(
                      select department_id
                      from employees
                      having avg(salary)=(
                                          select min(svg(salary))
                                          from employees
                                          group by department_id
                                         )
                      group by department_id;
                    )
                    
--3，查询平均工资最低的部门信息和该部门的平均工资

select d.*,(select avg(salary) from employees where department=d.department_id)
from departments d
where department_id=(
                      select department_id
                      from employees
                      having avg(salary)=(
                                          select min(svg(salary))
                                          from employees
                                          group by department_id
                                         )
                      group by department_id;
                    )

--4，查询平均工资最高的job信息

--1.按job来分组，查询最高的平均工资是多少

select max(avg(salary))
from employees
group by job_id

--2.查询得到哪个job_id平均工资等于1.得到的值

select job_id
from employees
where avg(salary)=(
                    select max(avg(salary))
                    from employees
                    group by job_id
                  )
group by job_id

--3.从jobs表中返回job_id的对应项的信息

select *
from jobs
where job_id in (
                select job_id
                from employees
                having avg(salary)=(
                                    select max(avg(salary))
                                    from employees
                                    group by job_id
                                  )
                group by job_id
             )
             
--5，查询平均工资高于公司平均工资的部门有哪些

select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>(
                    select avg(salary)
                    from employees
                   )

--6.查询吃公司中所有manager的详情信息

select *
from employees
where employee_id in (
                      select manager_id
                      from employees
                     )
                     
--7.各部门中最高工资中最低的那个部门的最低工资是多少

select min(salary)
from employees
where department_id=(
                     select department_id
                     from employees
                     having max(salary)=(
                                          select min(max(salary))
                                          from employees
                                          group by department_id
                                        )
                                        group by department_id
                    )
                    
--8，查询平均工资最高的部门的manager的详情信息:
--last_name,department_id,email,salary

select last_name,department_id,email,salary
from employees
where employees_id in (
                      select distinct manager_id
                      from employees
                      where separtment_id=(
                                            select department_id
                                            from employees
                                            group by department_id
                                            having avg(salary)=(
                                                                select max(avg(salary))
                                                                from employees
                                                                group by department_id
                                                               )
                                          )
                     )

--9，查询1999年来公司的员工中最高工资的那个员工的信息

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