SQL> create table employees
  2  (
  3  emp_id number(2),
  4  name varchar(30),
  5  department_id number(4),
  6  job_id varchar(30),
  7  salary number(8,2),
  8  hire_date date);


insert into employees values (1,'zs',10,'xs',2000,to_date('2011-11-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (2,'ls',20,'mj',12000,to_date('2010-11-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));


insert into employees values (3,'zs1',10,'xs',5000,to_date('2010-01-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (4,'ls1',20,'mj',6000,to_date('2010-12-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (5,'zs2',10,'xs',11000,to_date('2001-11-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (6,'ls2',20,'mj',18000,to_date('2010-01-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (7,'zs3',10,'xs',50000,to_date('2018-11-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));
insert into employees values (8,'ls3',20,'mj',80000,to_date('2018-19-11 11:11:11','yyyy-mm-dd hh24:mi:ss'));

 

 

1、查询1到50部门中 哪些年份入职的员工超过1人.
显示年份,人数,其中年份为4位 ,人数按降序排列

 

SQL> select substr(to_char(hire_date,'yyyy-mm-dd hh24:mi:ss'),1,4),count(*)  from employees  group by substr(to_char(hire_date,'yyyy-mm-dd hh24:mi:ss'),1,4)  having count(*) >1 order by 2 desc;

SUBS   COUNT(*)
---- ----------
2010          4
2018          2


 

2、那些员工的工资高于该部门的平均工资，显示姓名工资，以工资降序排列



SQL> with t1 as (select department_id,avg(salary) salary from employees group by department_id) select employees.name,employees.salary from employees,t1 where employees.department_id=t1.department_id and employees.salary>t1.salary order by employees.salary desc;

NAME                               SALARY
------------------------------ ----------
ls3                                 80000
zs3                                 50000