JAVA sql 查询2

SELECT * FROM employees order by salayr DESC

SELECT employee_id,first_name,salary
from employees
ORDER BY salary,employee_id desc

-- 最大值 最小值  总和 平均值
SELECT max(salary),MIN(salary),sum(salary),AVG(salary) FROM employees
-- 总共有多少员工
select COUNT(*) from employees
select COUNT(employee_id) from employees
-- 有绩效的员工有多少人
select count(commission_pct) from employees
-- 查询没有绩效的员工有多少人
SELECT count(employee_id) from employees where commission_pct is NULL

-- 分组 查询共有那些部门ID及每个部门多少人
SELECT department_id,count(employee_id)-- 3。基于分组结果
FROM employees -- 1.确定基于员工表操作
group by department_id -- 2.根据部门id进行分组

-- 查询独有那些岗位及每个岗位有多少人  岗位最高薪资
select job_id,count(*),MAX(salary)
from employees group by job_id

-- 查询部门ID为 10 20 30  的部门  最高薪资  和最低薪资
SELECT department_id,max(salary),min(salary)
FROM employees
WHERE department_id in(10,20,30)
GROUP BY department_id

-- 查询长度》=5的岗位的各个人数
SELECT job_id,COUNT(employee_id)
FROM employees
WHERE LENGTH(job_id)<=5
GROUP BY job_id

-- 查看工资最高的前10名员工信息
SELECT * FROM employees ORDER BY salary desc limit 10

-- 单值子查询:子SOL得到的结果是单行单列的查询员工id为119号员工所在的部门信息

select *
from departments
-- 部门id= 119号员工所在的部门id
where department_id = (select department_id from employees where employee_id=110)

-- 查询id为119号员工的直接领导的员工信息
select *
from employees
-- 员工id= 110号员工的领导idl
where employee_id in (select manager_id from employees where employee_id=110)

-- where多值子查询: 子SQL返回的是单列多行的结果-- 查询工资>1@的员工所在的部门信息
select *
from departments
-- 部门id in 工资>10@00的员工所属的部门id
where department_id in (select department_id from employees where salary>10000)

-- from子查询: 将子SL的查询结果临时看做一张表,起个别名进行外层SOL的查询操作
-- 查询工资最高的前3名员工的平均薪资
select avg(salary) from (SELECT * from employees
 order by salary  DESC LIMIT 3) as e

-- 内连接:对连接双方的表同时做约束,双方的数据都必须满足连接条件才能展示到最终的临时表
-- 查询所有员工信息及其所属部门信息
select e.*,d.*
from employees e join departments d on e.department_id=d.department_id

-- 查询100号员工所对应的员工ID,姓名，工资,部门ID,部门名称
SELECT employee_id,first_name, salary,e.department_id,department_name
from employees e join departments d ON e.department_id=d.department_id 
where employee_id=100