[打卡]2024.4.14

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)

FROM employees;

SELECT job_id, 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) difference

FROM employees;

SELECT manager_id,MIN(salary)

FROM employees

WHERE manager_id IS NOT NULL

GROUP BY manager_id

HAVING MIN(salary)>6000;

SELECT department_name,location_id,COUNT(salary),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的高？

#方式1

SELECT salary

FROM employees

WHERE last_name ='abel';

SELECT last_name,salary

FROM employees

WHERE salary>11000;

#方式2 自链接

SELECT e2.last_name,e2.salary

FROM employees e1,employees e2

WHERE e2.salary > e1.salary

AND e1.last_name='Abel';

#方式3

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;

/*

子查询分类

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

单行子查询 VS 多行子查询

角度2

内查询是否被查询多次

相关子查询 VS 不相关子查询

举例，比如

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

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

/*

#单行子查询的

#单行子查询的操作符：= != > < >= <=

/*

子查询的编写技巧

1 从里往外写

2 从外往里写

*/

#习题：查询工资大于149号员工工资的员工信息

SELECT employee_id,last_name,salary

FROM employees

WHERE salary>(

SELECT salary

FROM employees

WHERE employee_id=149

);

#习题：返回job-id与142号员工相同，salary比143号员工多的员工姓名，job-id的工资

SELECT last_name,job_id,salary

FROM employees

WHERE job_id = (

SELECT job_id

FROM employees

WHERE employee_id = 141

)

AND salary > (

SELECT salary

FROM employees

WHERE employee_id = 143

);

#中英互换

#习题：返回公司工资最少的last_name,job_id和salary

SELECT last_name,job_id,salary

FROM employees

WHERE salary=(

SELECT MIN(salary)

FROM employees

);

#查询与141员工的manager_id的department_id相同的其他员工的employee_id，manmager_id,department_id

SELECT employee_id, manager_id,department_id

FROM employees

WHERE manager_id = (

SELECT manager_id

FROM employees

WHERE employee_id = 141

)

AND department_id = (

SELECT department_id

FROM employees

WHERE employee_id = 141

)

AND employee_id !=141;

#方式2：

SELECT employee_id, manager_id,department_id

FROM employees

WHERE(manager_id,department_id)=(

SELECT manager_id,department_id

FROM employees

WHERE employee_id = 141

)

AND employee_id !=141;

#习题:查询最低工资大于110号部门最低工资的部门的部门id和其最低工资

SELECT department_id,MIN(salary)

FROM employees

WHERE department_id IS NOT null;

GROUP BY department_id

HAVING MIN(salary)>(

SELECT MIN(salary)

FROM employees

WHERE department_id=60

);

#非法使用子查询

SELECT employee_id,last_name

FROM employees

WHERE salary = (

SELECT MIN(salary)

FROM employees

GROUP BY department_id

);