Oracle SQL find department based on conditions

Question

I have two tables emp and dept I need to find the names of departments where employees earn the most and the least on average and display the result in one line with the difference. here is my solution

this works but I was wondering if there was a cleaner way of writing this query ? any help is appreciated

Here are the tables:

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) PRIMARY KEY,
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');




CREATE TABLE EMP
       (EMPNO NUMBER(4) PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4) REFERENCES EMP,
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2) REFERENCES DEPT);

INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-MAR-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-MAR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAR-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-MAR-1981', 'DD-MON-YYYY'),  2450, NULL, 10);        

INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-MAR-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-MAR-1981', 'DD-MON-YYYY'), 1250,  500, 30);

INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-MAR-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-MAR-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-MAR-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-MAR-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-MAR-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-MAR-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-MAR-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Answer 1

Here's one option, which uses

• rank analytic function (to "sort" average salaries in ascending (least) or descending (most) order),
• case expression (to find departments that earns the most or the least) and
• some joins

---

SQL> WITH
  2     salaries
  3     AS
  4        (  SELECT e.deptno,
  5                  ROUND (AVG (e.sal)) avg_sal,
  6                  --
  7                  CASE
  8                     WHEN RANK () OVER (ORDER BY AVG (e.sal) DESC) = 1
  9                     THEN
 10                        e.deptno
 11                  END max_deptno,
 12                  CASE
 13                     WHEN RANK () OVER (ORDER BY AVG (e.sal) ASC) = 1
 14                     THEN
 15                        e.deptno
 16                  END min_deptno
 17             FROM emp e
 18         GROUP BY e.deptno)
 19  SELECT    'most earned '
 20         || a.dname
 21         || ', least earned '
 22         || b.dname
 23         || '; difference = '
 24         || TO_CHAR (sa.avg_sal - sb.avg_sal) AS result
 25    FROM salaries sa
 26         JOIN dept a ON a.deptno = sa.max_deptno
 27         CROSS JOIN salaries sb
 28         JOIN dept b ON b.deptno = sb.min_deptno;

RESULT
--------------------------------------------------------------------------------
most earned ACCOUNTING, least earned SALES; difference = 1350

SQL>

Answer 2

You can do it only selecting from each table once and without using self-joins:

SELECT 'Most earned: '||TO_CHAR(MAX(avg_sal), 'fm99999990.00')
       ||' in '||LISTAGG(CASE avg_sal_rank_desc WHEN 1 THEN dname END, ',')
                   WITHIN GROUP (ORDER BY dname)
       ||', Least earned: '||TO_CHAR(MIN(avg_sal), 'fm99999990.00')
       ||' in '||LISTAGG(CASE avg_sal_rank_asc WHEN 1 THEN dname END, ',')
                   WITHIN GROUP (ORDER BY dname)
       ||', Difference: '||TO_CHAR(MAX(avg_sal)-MIN(avg_sal), 'fm99999990.00')
         AS average_salary_details
FROM   (
  SELECT d.deptno,
         MAX(d.dname) AS dname,
         COALESCE(AVG(e.sal), 0) AS avg_sal,
         RANK() OVER (ORDER BY COALESCE(AVG(e.sal), 0) ASC) AS avg_sal_rank_asc,
         RANK() OVER (ORDER BY COALESCE(AVG(e.sal), 0) DESC) AS avg_sal_rank_desc
  FROM   dept d
         LEFT OUTER JOIN emp e
         ON (d.deptno = e.deptno)
  GROUP BY d.deptno
)
WHERE  avg_sal_rank_asc = 1
OR     avg_sal_rank_desc = 1

Note: If there are multiple departments tied for highest or lowest average salary then this will output them all in a comma-delimited list.

Which, for the sample data, outputs:

AVERAGE_SALARY_DETAILS
Most earned: 2916.67 in ACCOUNTING, Least earned: 0.00 in OPERATIONS, Difference: 2916.67

If you do not want to include departments with zero employees then change the LEFT OUTER JOIN to an INNER JOIN and then the output is:

AVERAGE_SALARY_DETAILS
Most earned: 2916.67 in ACCOUNTING, Least earned: 1566.67 in SALES, Difference: 1350.00

db<>fiddle here