[打卡]lulu

#1
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#2
SELECT job_id,MAX(salary),MIN(salary),SUM(salary),SUM(salary)
FROM employees
GROUP BY job_id;
#3
SELECT job_id ,COUNT(*)
FROM employees
GROUP BY job_id;
#4
SELECT MAX(salary),MIN(salary),MAX(salary)-MIN(salary)
DIFFERENCE
FROM employees;
#5
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id is NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;
#6
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;

#第八章 子查询
#需求：谁的工资比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;


/*
子查询的分类
角度一：从内查询返回的结果条件数
单行子查询 VS 多行子查询（工资大于11000的姓名）
角度二：内查询是否被执行多次

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

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

*/