查询和Zlotkey相同部门的员工姓名和工资

#①查询Zlotkey所在的部门
show databases;
use myemployees;
select department_id from employees 
where last_name = 'Zlotkey';
#②查询部门号=①的员工姓名和工资
select last_name,salary from employees 
where department_id = (
    select department_id from employees where 
    last_name = 'Zlotkey'
    );

查询工资比平均工资高的员工的员工号、姓名和工资

select avg(salary) from employees;
select employee_id,last_name,salary from employees where salary>(
    select avg(salary) from employees
);

查询各部门中工资比本部门平均工资高的员工号，姓名和工资

select avg(salary),department_id from employees group by department_id;
#连接①结果集和employees表，进行筛选
select employee_id,last_name,salary from (
    select avg(salary) ag,department_id from employees group by department_id 
    ) avg_sal
inner join employees e on e.department_id = avg_sal.department_id 
where salary > avg_sal.ag;

查询和姓名中包含字母u的员工在相同部门的员工的员工号和员工名

select distinct department_id from employees where last_name like '%u%';
select employee_id,last_name from employees where department_id in (
    select distinct department_id from employees where last_name like '%u%'
);

查询在部门的location_id=1700的部门工作的员工的员工号

select department_id from departments where location_id = 1700;
select employee_id from employees where department_id in (
    select department_id from departments where location_id =1700
);#这里的in关键字可以换成是=any()

查询管理者是King的员工姓名和工资

select employee_id from employees where last_name = 'K_ing';
select last_name,salary from employees where manager_id in (
    select employee_id from employees where last_name = 'K_ing'
);

查询工资最高的员工姓名，要求first_name和last_name 显示为一列，列名为姓.名

select max(salary) from employees;
select concat(first_name,last_name) "姓.名" from employees where salary = (
    select max(salary) from employees
);