--2列出EMPLOYEES表中各部门的部门号，最高工资，最低工资
select Max(salary) as '最高工资',Min(salary) as '最低工资' ,department_id as '部门号' from Employees group by department_id

--3列出EMPLOYEES表中各部门EMPLOYEE_JOB为'职员'的员工的最低工资，最高工资
select Max(salary) as '最高工资',Min(salary) as '最低工资' ,department_id as '部门号' from Employees
where employee_job='职员' group by department_id

--4对于EMPLOYEES中最低工资小于5000的部门，列出EMPLOYEE_JOB为'职员'的员工的部门号，最低工资，最高工资

select a.department_id as  '部门号',Max(a.salary) as '最高工资',Min(a.salary) as '最低工资' from Employees
as a where 5000>(select Min(salary) from Employees as b where a.Department_Id=b.Department_Id ) and a.Employee_Job='职员'
group by a.department_id 

select a.department_id as  '部门号',Max(a.salary) as '最高工资',Min(a.salary) as '最低工资' from Employees
as a group by a.department_id 


--根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资

select a.employee_name as '姓名',a.department_id as '部门号',a.salary as '工资' from Employees as
a order by a.Department_Id desc,a.salary asc


--6列出'刘备'所在部门中每个员工的姓名与部门号

select a.employee_name as '姓名',a.department_id as '部门号'  from Employees as a 
where a.Department_Id=(select Department_Id from Employees as b where b.Employee_Name='刘备')


--7列出每个员工的姓名，工作，部门号，部门名

select employee_name ,Employee_job ,employees.department_id,department_name from employees inner join
departments on employees.Department_Id=Departments.Department_ID


--8列出EMPLOYEES中工作为’职员'的员工的姓名，工作，部门号，部门名

select employee_name ,Employee_job ,employees.department_id,department_name from employees inner join
departments on employees.Department_Id=Departments.Department_ID where Employees.Employee_Job='职员'


--8对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'职员'的员工名与工作

select Departments.Department_Name as '部门名称',Departments.Department_ID as '部门号',Employees.Employee_Name as '员工名',Employees.Employee_Job as '工作' from Departments
left join Employees on Departments.Department_ID=Employees.Department_Id where Employees.Employee_Job='职员'


--9对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

select a.department_id as '部门号',a.employee_name as '姓名',a.salary as '工资' from Employees
as a where a.Salary>(select avg(salary) from Employees as b where a.Department_Id=b.Department_Id)
order by a.Department_Id

SELECT 
    Employees.department_id,
    Employee_name,
    Salary
FROM
    Employees,
    (SELECT 
        AVG(salary) as 'avg', 
        department_id 
    FROM 
        Employees 
    GROUP BY 
        department_id) as t 
WHERE
    salary > t.avg
    and Employees.Department_Id = t.Department_Id

select a.department_id as '部门号',a.employee_name as '姓名',a.salary as '工资' from Employees
as a

select avg(salary) from Employees where Department_Id=1
select avg(salary) from Employees where Department_Id=2
select avg(salary) from Employees where Department_Id=3

select * from Employees

--10对于EMPLOYEES，列出各个部门中工资高于本部门平均水平的员工数和部门号，按部门号排序


select count(*) as '员工数',a.department_id as '部门号',avg(salary) as '平均工资' from Employees as a
where a.Salary>(select avg(salary) from Employees as b where a.Department_Id=b.Department_Id)
group by a.Department_Id having count(a.Department_Id) >1 order by a.Department_Id 

select salary from Employees where Department_Id=3

--11对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号