sql语句

1、         编写sql语句，查询各个部门中，工资高于10000的员工个数，要求显示部门的id号和人数，其中人数按照降序排列。

with c as (select department_id from employees where salary>10000)

select department_id,count(*) from c group by department_id order by 2 desc;

2、         编写sql语句，查询在10到50部门中，那些年份入职的员工人数超过3个，要求：显示年份和人数，其中年份为4位，如2011，人数按照降序排列。

with q as (select to_char(hire_date,'yyyy') as s,count(*) as rs from employees  where department_id between 10 and 50 group by to_char(hire_date,'yyyy'))

select s,rs from q where rs>3 order by 2 desc;

3、         编写sql语句，查询哪些员工的工资高于所在部门的平均工资，要求:显示姓名，工资，其中工资按照降序排列。

with w as (select department_id,avg(salary) as avg from employees group by department_id)

select employees.name,employees.salary,employees.department_id from employees,w where employees.salary>w.avg and  employees.department_id=w.department_id order by 2 desc;