Oracle: List the first name, last name, Job id of all the employees except "IT_PROG " & " FI_ACCOUNT" in asc order of Salaries

Oracle Operator: Exercise-10 with Solution

Write a query to list the first name, last name, Job id of all the employees except "IT_PROG " & " FI_ACCOUNT" in asc order of Salaries.

Sample table: employees

Sample Solution :-

Oracle Code :

SELECT first_name, last_name, job_id  
FROM employees  
WHERE job_id  NOT IN ('IT_PROG', 'FI_ACCOUNT') 
ORDER BY salary;

Output:

FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
TJ                   Olson                     ST_CLERK
Steven               Markle                    ST_CLERK
Hazel                Philtanker                ST_CLERK
James                Landry                    ST_CLERK
Ki                   Gee                       ST_CLERK
Karen                Colmenares                PU_CLERK
James                Marlow                    ST_CLERK
Joshua               Patel                     ST_CLERK
Peter                Vargas                    ST_CLERK
Martha               Sullivan                  SH_CLERK
Randall              Perkins                   SH_CLERK
..........

97 rows selected.

Oracle Code :

SELECT first_name, last_name, job_id  
FROM employees  
WHERE job_id  NOT LIKE 'IT_PROG' AND job_id NOT LIKE  'FI_ACCOUNT' 
ORDER BY salary;

Output:

FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
TJ                   Olson                     ST_CLERK
Steven               Markle                    ST_CLERK
Hazel                Philtanker                ST_CLERK
James                Landry                    ST_CLERK
Ki                   Gee                       ST_CLERK
Karen                Colmenares                PU_CLERK
James                Marlow                    ST_CLERK
Joshua               Patel                     ST_CLERK
Peter                Vargas                    ST_CLERK
Martha               Sullivan                  SH_CLERK
Randall              Perkins                   SH_CLERK

..........

97 rows selected.

Oracle Code :

SELECT first_name, last_name, job_id  
FROM employees  
WHERE job_id  != 'IT_PROG' AND job_id != 'FI_ACCOUNT' 
ORDER BY salary;

Output:

FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
TJ                   Olson                     ST_CLERK
Steven               Markle                    ST_CLERK
Hazel                Philtanker                ST_CLERK
James                Landry                    ST_CLERK
Ki                   Gee                       ST_CLERK
Karen                Colmenares                PU_CLERK
James                Marlow                    ST_CLERK
Joshua               Patel                     ST_CLERK
Peter                Vargas                    ST_CLERK
Martha               Sullivan                  SH_CLERK
Randall              Perkins                   SH_CLERK

.......

97 rows selected.

Pictorial Presentation:

Improve this sample solution and post your code through Disqus.

Previous: Write a query to list the name, salary of all the employees where employee first name belongs in a specified list.
Next: Write a query to list the name (first and last name), hire date of all the employees who joined before or after 2005.