子查询

多行比较操作符

IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 实际上是ANY的别名，一般常用ANY

//题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的
//员工号、姓名、job_id 以及salary
select employee_id, last_name, job_id, salary from employees where salary < any (SELECT salary from employees WHERE job_id = 'IT_PROG') and job_id <> 'IT_PROG'
//返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的
//员工号、姓名、job_id以及salary
select employee_id, last_name, job_id, salary from employees where salary < all (SELECT salary from employees WHERE job_id = 'IT_PROG')

//查询平均工资最低的部门id 
select department_id from employees GROUP BY department_id HAVING AVG(salary) <= all(select avg(salary) from employees GROUP BY department_id)

相关子查询

//查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name, salary, department_id from employees outers where salary > (select avg(salary) from employees where department_id = outers.department_id)

//查询员工的id,salary,按照department_name 排序
SELECT employee_id, salary from employees e GROUP BY (select department_name from departments d WHERE e.department_id = d.department_id)

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

//在employees中增加一个department_name字段，数据为员工对应的部门名称
alter table employees add(department_name varchar(30))
UPDATE employees e set department_name=(select department_name from departments d where e.department_id = d.department_id)

相关删除

//删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in  
           (SELECT employee_id
            FROM   emp_history 
            WHERE employee_id = e.employee_id);

子查询与自连接查询

题目可以使用子查询和自连接查询，一般使用自连接查询。子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分DBMS中都对自连接处理进行了优化。

练习

#1.查询和Zlotkey相同部门的员工姓名和工资
#SELECT last_name, salary from employees where department_id = (SELECT department_id from employees where last_name = 'Zlotkey')
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
#SELECT employee_id, last_name, salary from employees outers where salary > (SELECT avg(salary) from employees where department_id = outers.department_id)
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
#SELECT a.last_name, a.job_id, a.salary from employees a join employees b on  a.salary > b.salary and b.job_id = 'SA_MAN'
-- 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,salary from employees where department_id = any (select department_id from employees where last_name like "%u%")
#5.查询在部门的location_id为1700的部门工作的员工的员工号
-- select employee_id from employees join departments on employees.department_id = departments.department_id where departments.location_id = 1700 
-- SELECT employee_id
-- FROM employees
-- WHERE department_id IN (
--  SELECT department_id
--  FROM departments
--  WHERE location_id = 1700
-- )
#6.查询管理者是King的员工姓名和工资
#select a.last_name, a.salary from employees a join employees b on a.manager_id = b.employee_id and b.last_name = 'King'
-- SELECT last_name, salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = '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) = (select min(dept_avgsal1) from (select avg(salary) dept_avgsal1 from employees group by department_id) avg_sal ))
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
#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) )
#10.查询平均工资最高的 job 信息
#11.查询平均工资高于公司平均工资的部门有哪些?
#SELECT department_id from employees where department_id  GROUP BY department_id having avg(salary) > (SELECT avg(salary) from employees)
#12.查询出公司中所有 manager 的详细信息

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
#select department_id, min(msalary) from (SELECT department_id, max(salary) msalary FROM employees where department_id is not null GROUP BY department_id) a

#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

#16. 选择所有没有管理者的员工的last_name
#SELECT a.last_name from employees a left JOIN employees b on a.manager_id = b.employee_id and b.employee_id is null
-- SELECT last_name
-- FROM employees e1
-- WHERE NOT EXISTS (
--  SELECT *
--  FROM employees e2
--  WHERE e1.manager_id = e2.employee_id
--  );
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
#select employee_id, last_name, salary from employees a where salary > (select avg(salary) from employees b where a.department_id=b.department_id )
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
#select department_name,department_id from departments where department_id in (select department_id from( select COUNT(1) nums,department_id from employees GROUP BY department_id) count_id where nums>5)
#SELECT department_name,department_id FROM departments d WHERE 5 < ( SELECT COUNT(*) FROM employees e WHERE d.`department_id` = e.`department_id` ); 
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT country_id from locations l where 2 <(SELECT count(*) from departments d where d.location_id = l.location_id)