[打卡]谷加伟 —— 子查询

SELECT MAX(salary),MIN(salary),AVG(salary),
SUM(salary)
FROM employees;

SELECT job_id, COUNT(salary),MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;


SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary) chaju
FROM employees;

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#COUNT(*)空值也算一个数值

SELECT department_name,location_id,
COUNT(*),AVG(salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) DESC;

SELECT department_name,location_id,
COUNT(employee_id),AVG(salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) DESC;

SELECT employee_id,salary,department_name,location_id
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;




















# 第8章 子查询

# 需求：谁的工资比Abel高？

# 方式一：

SELECT salary
FROM employees
WHERE last_name='Abel';

SELECT last_name,salary
FROM employees
WHERE salary>11000;

# 方式二：自连接

SELECT e2.last_name,e2.salary
FROM employees e1, employees e2
WHERE e2.salary > e1.salary # 多表的连接
AND e1.last_name = 'Abel';

# 方式三 子查询


SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);

# 称为的规范：外查询（主查询）、内查询（子查询）

/*

子查询（内查询）主查询（外查询）之前一次执行完成

子查询的结果被主查询（外查询）使用

注意事项

子查询要包含在括号内

将子查询发在比较条件的右侧

单行操作符对应单行子查询，多行操作符对应多行子查询
*/
SELECT last_name,salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE last_name='Abel'
)

/*
子查询的分类

角度1：从内查询返回的结果
单行子查询 vs 多行子查询（工资大于11000的姓名）

角度2：内查询是否被执行多次
相关子查询 vs 不相关子查询
举例
相关子查询：查询工资大于本部门平均工资的员工信息

不相关子查询：查询工资大于本公司平均工资的员工信息

*/