[打卡]33

#2
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#3
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;
#4
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
#5
SELECT MAX(salary),MIN(salary),MAX(salary) - MIN(salary) chaju
FROM employees;
#6
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
#7查询所有部门的名字，loction_name,员工数量和平均工资，并按平均工作降序

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 employee_id,salary,department_name,location_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;


#子查询

#需求：谁的工资你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'
) < salary;
/*
子查询的分类

角度一：从内查询返回的结果条目数

单行子查询 > 多行子查询(工资大于11000的姓名)

角度二：内查询是否被执行多次

相关子查询 vs 不相关子查询

举例

相关子查询：查询工资大于本部门平均工资的员工信息

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

*/