HR SCHEMA: the year of most employees hires [closed]

IN the HR Schema in oracle how do I get the year in which the most employees were hired?

Answer

I have Scott’s schema so you’ll apply this query to yours.

Employees and dates (years) when they were hired. Apparently, 1981 seems to be the most common year.

SQL> select ename, hiredate, extract(year from hiredate) yr
  2  from emp
  3  order by 3 ;

ENAME      HIREDATE           YR
---------- ---------- ----------
SMITH      17.12.1980       1980
WARD       22.02.1981       1981
JONES      02.04.1981       1981
MARTIN     28.09.1981       1981
BLAKE      01.05.1981       1981
ALLEN      20.02.1981       1981
CLARK      09.06.1981       1981
FORD       03.12.1981       1981
JAMES      03.12.1981       1981
KING       17.11.1981       1981
TURNER     08.09.1981       1981
SCOTT      09.12.1982       1982
MILLER     23.01.1982       1982
ADAMS      12.01.1983       1983

14 rows selected.

Query you might be interested in: use rank analytic function:

SQL> select yr
  2  from (select extract(year from hiredate) yr,
  3               rank() over (order by count(*) desc) rnk
  4        from emp
  5        group by extract(year from hiredate)
  6       )
  7  where rnk = 1;

        YR
----------
      1981