sql连接查询

#查询和Zlokey相同部门的员工姓名和工资
select last_name,salary 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 department_id,avg(salary) from employees group by department_id;

select employee_id,last_name,salary,avg_sal.department_id,avg_sal.avg_sals 部门平均工资  from employees 
left join  (select department_id,avg(salary) avg_sals from employees group by department_id) avg_sal on employees.department_id = avg_sal.department_id 
where employees.salary >avg_sal.avg_sals;
#查询管理者是K_ing的员工姓名、编号，要求last_name和first_name在同一列 ；
select concat(last_name,"_",first_name),employee_id from employees where manager_id in 
(select employee_id from employees where last_name = 'K_ing');

#查询平均工资最低的部门的部门信息和该部门的平均工资；
select departments.*, sal_dep.avg_sal from departments, (SELECT department_id,avg(salary) avg_sal from employees group by department_id order by avg(salary) Asc limit 1 ) sal_dep 
where departments.department_id = sal_dep.department_id;

#查询平均工资最高的部门的manager信息：last_name,department_id,email,salary
select last_name,e.department_id,email,salary from employees e inner join departments d on d.manager_id = e.employee_id where e.department_id = 
(SELECT department_id from employees GROUP BY department_id ORDER BY avg(salary) desc limit 1);