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;