MySql8之子查询练习题

首先需要创建表

Mysql 基础表SQL https://songzixian.com/mysql/1792.html

#1.查询和Zlotkey相同部门的员工姓名和工资
select last_name ,salary
 from employees
where department_id = ( select d.department_id from employees d where last_name = 'Zlotkey' )

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
select last_name,salary
from employees
where salary > (
    select avg(salary) from employees
    );


#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
select e.last_name , e.job_id ,e.salary
from employees e
where e.salary > all (
     select salary from employees where job_id = 'SA_MAN'
    );


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select department_id ,last_name
from employees where department_id in (
     select distinct department_id from employees where last_name like '%u%'
    );


#5.查询在部门的location_id为1700的部门工作的员工的员工号
select department_id
from employees where  department_id in (
     select department_id from departments where location_id = 1700
    );

#6.查询管理者是King的员工姓名和工资
select last_name ,salary ,manager_id
from employees where manager_id in (
     select manager_id from employees where last_name='King' and manager_id is not null
    );

#7.查询工资最低的员工信息: last_name, salary
select last_name,salary
from employees
where salary = (
    select min(salary)
    from employees
    );

#8.查询平均工资最低的部门信息
#方式一
select *
from departments
where department_id  = (
         #查询某个部门的平均工资等于临时表种的最低工资并返回department_id给上一级
         select department_id  from employees group by department_id having avg(salary)  = (
            # 从临时表获取最低的平均工资
            select min(avg_sal) from (
                # 统计部门的所有平均工资
                select avg(salary) as avg_sal  from employees group by department_id
            )t_dept_avg_sal #这是子查询临时表
        )
    );

#方式二
select *
from departments
where department_id = (
    #返回department_id给上一层
    select department_id from employees group by department_id
    # 查找小于或等于最低平均工资
    having avg(salary) <= ALL (
        # 统计部门平均工资
        select 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 avg(salary) as avg_sal
            from employees
            group by department_id
            order by avg_sal asc
            limit 1
    )
);

#方式四
select *
from departments d,
     (
         #查询员工的平均工资,并且按降序排序取一条数据
         select department_id, avg(salary) as avg_sal
         from employees
         group by department_id
         order by avg_sal asc
         limit 1
    )t_dept_avg_sal #作为一个临时表
#两张表关联
where d.department_id = t_dept_avg_sal.department_id ;

#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#方式一
select d.* , ( select avg(salary) from employees where department_id = d.department_id ) as avg_salary
from departments d
# 关联最低平均工资的部门
where department_id = (
        #查询最低的平均工资department_id
        select department_id from employees  group by department_id having avg(salary) = (
            ##查询最低的平均工资
            select min(salary)
            from (
                 #查询部门员工平均工资
                 select avg(salary) as salary from employees group by department_id
            )t_dept_avg_sal
        )
    );


#方式二
select d.* ,( select avg(salary) from employees where  department_id = d.department_id ) as salary
from departments d where d.department_id = (
    select department_id from employees group by department_id
    having avg(salary) <= ALL (
        #查询平均工资
        select avg(salary) from employees group by department_id
    )
);


#10.查询平均工资最高的 job 信息
#方式一
select *
from jobs
where job_id = (
    #查询最大的平均工资的job_id
    select job_id from employees group by job_id having avg(salary) = ALL (
        # 查询最大的平均工资
        select max(salary)
        from (
            #查询平均工资
            select avg(salary) as salary from employees group by job_id
        ) t_dept_avg_sal
    )
);

#方式二
select *
from jobs
where job_id = (
    # 查询最大的平均工资
    select job_id  from employees group by job_id having avg(salary) >= all (
    # 查询平均工资
    select avg(salary)  from employees group by job_id
    )
);

#方式三
select * from jobs where job_id = (
    select job_id from employees group by job_id having avg(salary) = (
        select avg(salary) as salary
        from employees group by job_id
        order by salary desc
        limit 0,1
    )
);

#方式四
select j.* from jobs j,(
    select job_id, avg(salary) as salary
    from employees group by job_id
    order by salary desc
    limit 0,1
    )t_job_avg_sal
where j.job_id = t_job_avg_sal.job_id ;

#11.查询平均工资高于公司平均工资的部门有哪些?
select * from departments where department_id in (
    select department_id
    from employees where department_id is not null  group by department_id  having avg(salary) > (
        select avg(salary) from employees
    )
 );

#12.查询出公司中所有 manager 的详细信息
#方式一
select distinct mgr.employee_id, mgr.last_name ,mgr.job_id ,mgr.department_id
from employees emp join  employees mgr
on emp.manager_id = mgr.employee_id ;



#方式2：子查询
select employee_id ,last_name,job_id ,department_id
from employees
where employee_id in (
    select distinct manager_id from employees
    );

#方式三
select employee_id ,last_name,job_id ,department_id
from  employees e1
where exists(
     select  * from employees e2 where e1.employee_id = e2.manager_id
    );

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
select salary from employees
#方式一
where department_id = (
    select department_id
    from employees group by department_id having max(salary) =  (
        #查询最低工资的部门
        select min(max_sal)
        from ( # 查詢各个部门得到最高工資
             select max(salary) as max_sal  from employees  group by department_id
            ) t_dept_amx_sal
    )
);

#方二
select salary from employees
where department_id = ((
    select department_id
    from employees group by department_id
    having max(salary) <= all(
            select max(salary) from employees group by department_id
        )
    )
);

#方式三
select min(salary)
from employees
where department_id = (
    select department_id  from employees
    group by department_id
    having max(salary) = (
            select max(salary) as max_sal from employees
            group by department_id
            order by max_sal asc
            limit 0 ,1
        )
    );

#方式四
select min(salary)
from employees e,(
     select department_id, max(salary) max_sal
     from employees
        group by department_id
         order by max_sal asc
         limit 0,1
    ) t_dept_max_sal
where e.department_id =t_dept_max_sal.department_id ;

#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#方式一
select  last_name, department_id, email, salary
from employees where employee_id = any (
     select distinct manager_id
        from employees where department_id = (
            select department_id  from employees group by department_id having avg(salary) = (
                select max(salary) from (
                    select avg(salary) as salary from employees group by department_id
                ) t_dept_avg_sal
            )
        )
    );


#方式二.
select last_name, department_id, email, salary
from employees where employee_id = any  (
    select distinct manager_id from  employees where department_id = (
        select department_id from employees group by department_id having avg(salary) >= all (
            select 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 e,(
            select department_id ,avg(salary) avg_sal
            from employees
            group by department_id
            order by avg_sal desc
            limit 0 ,1
            ) t_dept_avg_sal
    where e.department_id = t_dept_avg_sal.department_id
);

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
select department_id
##查询不job_id不包含ST_CLERK
from departments where department_id not in (
    select distinct department_id
        from employees where job_id = 'ST_CLERK'
    );

#方式二
select department_id
from departments d
where not exists(
    select * from employees e where e.department_id = d.department_id
    );


#16. 选择所有没有管理者的员工的last_name
# 方式一
select last_name
from employees emp
where not exists(
     select * from employees mgr where emp.manager_id = mgr.employee_id
    );


#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#方式一
select employee_id ,last_name,phone_number, salary
from employees where manager_id = (
    select employee_id from employees where last_name = 'De Haan'
    );

#方式二
select employee_id ,last_name,phone_number, salary
from employees e1
    where exists(
         select employee_id from employees e2 where  e1.manager_id = e2.employee_id
         and last_name = 'De Haan'
    );


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
#方式1：使用相关子查询
select department_id, last_name,salary
from employees e1
where salary >(
      select avg(salary) as salary from employees e2 where e2.department_id = e1.department_id
    );

# 方式二 在FROM中声明子查询
select e.last_name,e.salary,e.department_id
from employees e ,(
    select department_id,avg(salary) as salary from employees group by department_id
    )t_dept_avg_sal
where e.department_id = t_dept_avg_sal.department_id
and e.salary > t_dept_avg_sal.salary ;



#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id

FROM employees e,(
        SELECT department_id,AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id) t_dept_avg_sal

WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal ;

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
select department_name
from departments d where 5 < (
     select count(*) from  employees  e where e.department_id = d.department_id
    );


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
select country_id from locations l where  2 < (
    select  count(*) from departments d where l.location_id = d.location_id
);