子查询（一）

子查询（一）

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

SELECT last_name,salary from employees where department_id =
(SELECT department_id from employees where last_name like "Zlotkey");

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT employee_id,last_name,salary from employees where salary > (
SELECT AVG(salary) from employees);

#3.选择工资大于所有JOB_ID ='SA_MAN’的员工的工资的员工的last_name，job_id，salary

select last_name,job_id,salary from employees where salary > all
(select salary from employees where JOB_ID ='SA_MAN');

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

SELECT employee_id , last_name ,department_id from employees where department_id in (
SELECT DISTINCT e.department_id from employees e  WHERE last_name REGEXP "u");

#5.查询在部门的location_id为1708的部门工作的员工的员工号

#方式一
SELECT employee_id  from (employees join departments USING (department_id))
join locations USING (location_id)  WHERE location_id = 1700;

#方式二
SELECT employee_id from employees WHERE department_id in (
SELECT department_id FROM departments WHERE location_id = 1700);

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

select last_name,salary from employees WHERE manager_id in(
select employee_id from employees WHERE last_name LIKE "King");

#7.查询工资最低的员工信息: last_name， salary

SELECT last_name, salary from employees where salary =
(select min(salary) from employees);

#8.查询平均工资最低的部门信息

#方式一
 SELECT * FROM departments 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 d.*  from departments d , 
(SELECT department_id,avg(salary) a from employees GROUP BY department_id ORDER BY  a LIMIT 1) t_min
WHERE d.department_id = t_min.department_id;

#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

SELECT d.* ,t_min.a as "平均工资" from departments d , 
(SELECT department_id,avg(salary) a from employees GROUP BY department_id ORDER BY  a LIMIT 1) t_min
WHERE d.department_id = t_min.department_id;

#10.查询平均工资最高的 job 信息

SELECT j.* from jobs j,
(SELECT job_id,avg(salary) a from employees GROUP BY job_id ORDER BY  a DESC  LIMIT 1) t_max
where j.job_id = t_max.job_id;