Oracle SQL - Write SQL to List all emp, sorted by job, and per job by age (from young to old).

Requirements

Here is the table

Demo

SQL>
SQL> drop table emp;
SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>-- w  ww . j  a v  a 2 s . c om
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>

Write SQL to List all emp, sorted by job, and per job by age (from young to old).

Demo

SQL>
SQL>-- w  w  w. jav  a2  s.c o m
SQL> select *
  2  from   emp
  3  order  by job, bdate desc;

     EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM
---------- -------- ----- -------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7012 JONES    R     ADMIN          7006 03-OCT-79       8000
        30

      7934 MARY     ABC   ADMIN          7007 23-JAN-72       1300
        10

      7009 KING     CC    DIRECTOR            17-OCT-72       5000
        10


     EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM
---------- -------- ----- -------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7004 JACK     JM    MANAGER        7009 02-APR-77       2975
        20

      7007 CLARK    AB    MANAGER        7009 09-JUN-75       2450
        10

      7006 BLAKE    R     MANAGER        7009 01-NOV-73       2850
        10


     EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM
---------- -------- ----- -------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7010 BREAD    JJ    SALESREP       7006 28-SEP-78       1500          0
        30

      7005 BROWN    P     SALESREP       7006 28-SEP-76       1250       1400
        30

      7003 WARD     TF    SALESREP       7006 02-MAR-72       1250        500
        10


     EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM
---------- -------- ----- -------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7002 ALLEN    JAM   SALESREP       7006 20-MAY-71       1600        300
        30

      7008 SCOTT    DEF   TRAINER        7004 26-NOV-79       3000
        20

      7902 FORD     MG    TRAINER        7004 13-FEB-79       3000
        20


     EMPNO ENAME    INIT  JOB             MGR BDATE           MSAL       COMM
---------- -------- ----- -------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7011 ADAMS    AA    TRAINER        7008 30-DEC-76       1100
        20

      7001 SMITH    N     TRAINER        7902 17-DEC-75       1800
        20


14 rows selected.

SQL>

• Previous
• Next