Listing all columns for one employee (dept, emp, salgrade)

Question

I am using Emp/Dept data base from ORACLE. Here is the structure if someone doesn't know it:

 CREATE TABLE EMP
           (EMPNO NUMBER(4) NOT NULL,
            ENAME VARCHAR2(10),
            JOB VARCHAR2(9),
            MGR NUMBER(4),
            HIREDATE DATE,
            SAL NUMBER(7, 2),
            COMM NUMBER(7, 2),
            DEPTNO NUMBER(2));

    INSERT INTO EMP VALUES
            (7369, 'SMITH',  'CLERK',     7902,
            TO_DATE('17-MAR-1980', 'DD-MON-YYYY'),  800, NULL, 20);
    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
            (7566, 'JONES',  'MANAGER',   7839,
            TO_DATE('2-MAR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
    INSERT INTO EMP VALUES
            (7654, 'MARTIN', 'SALESMAN',  7698,
            TO_DATE('28-MAR-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
    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
            (7788, 'SCOTT',  'ANALYST',   7566,
            TO_DATE('09-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO EMP VALUES
            (7839, 'KING',   'PRESIDENT', NULL,
            TO_DATE('17-MAR-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
    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
            (7934, 'MILLER', 'CLERK',     7782,
            TO_DATE('23-MAR-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

    CREATE TABLE DEPT
           (DEPTNO NUMBER(2),
            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 SALGRADE
            (GRADE NUMBER,
             LOSAL NUMBER,
             HISAL NUMBER);

    INSERT INTO SALGRADE VALUES (1,  700, 1200);
    INSERT INTO SALGRADE VALUES (2, 1201, 1400);
    INSERT INTO SALGRADE VALUES (3, 1401, 2000);
    INSERT INTO SALGRADE VALUES (4, 2001, 3000);
    INSERT INTO SALGRADE VALUES (5, 3001, 9999);

I need to list in one query all columns for one employee (let's say KING). Everything would be easy but there is the Salgrade table that I don't know how to link with others... I was trying something like this but it doesn't work...

SELECT e.Empno, e.Ename, e.Job, e.Mgr, e.Hiredate, e.Sal, e.Comm, e.Deptno, d.Dname, d.Loc, s.Grade
FROM Emp e, Salgrade s
INNER JOIN Dept d ON d.Deptno=e.Deptno
WHERE e.Ename = 'KING' AND e.Sal BETWEEN s.Losal AND s.Hisal

user_10676716 · Answer

You are on the right track with the join on inequality conditions on Salgrade. however you should never mix implicit and explicit joins. Their order of evaluation is different, so this leads to hard-to-debug failures. Your query yields error:

ORA-00904: "E"."DEPTNO": invalid identifier

Matter of fact, always use standard, explicit joins (with the on keyword).

This should do it:

SELECT e.Empno, e.Ename, e.Job, e.Mgr, e.Hiredate, e.Sal, e.Comm, e.Deptno, d.Dname, d.Loc, s.Grade
FROM Emp e
INNER JOIN Dept d ON d.Deptno=e.Deptno
INNER JOIN Salgrade s ON e.Sal BETWEEN s.Losal AND s.Hisal
WHERE e.Ename = 'KING'

Demo on DB Fiddle:

EMPNO | ENAME | JOB       |  MGR | HIREDATE  |  SAL | COMM | DEPTNO | DNAME      | LOC      | GRADE
----: | :---- | :-------- | ---: | :-------- | ---: | ---: | -----: | :--------- | :------- | ----:
 7839 | KING  | PRESIDENT | null | 17-MAR-81 | 5000 | null |     10 | ACCOUNTING | NEW YORK |     5

Listing all columns for one employee (dept, emp, salgrade)

1 Answers