w3resource

PostgreSQL Restricting and Sorting Data: Display the information for the employees who hasn't worked in some specific job and not drawing any specific salary


6. Write a query to display the last name, job, and salary for all those employees who hasn't worked as a Programmer or a Shipping Clerk, and not drawing the salary $4,500, $10,000, or $15,000.

Sample Solution:

Code:

SELECT last_name, job_id, salary 
FROM employees 
WHERE job_id NOT IN ('IT_PROG', 'SH_CLERK') 
AND salary NOT IN (4500,10000, 15000);

Sample table: employees


Output:

pgex=> SELECT last_name, job_id, salary 
pgex-> 
pgex-> FROM employees 
pgex-> 
pgex-> WHERE job_id NOT IN ('IT_PROG', 'SH_CLERK') 
pgex-> 
pgex-> AND salary NOT IN (4500,10000, 15000);
last_name  |   job_id   |  salary  
-------------+------------+----------
 King        | AD_PRES    | 24000.00
 Kochhar     | AD_VP      | 17000.00
 De Haan     | AD_VP      | 17000.00
 Raphaely    | PU_MAN     | 11000.00
 Khoo        | PU_CLERK   |  3100.00
 Baida       | PU_CLERK   |  2900.00
 Tobias      | PU_CLERK   |  2800.00
 Greenberg   | FI_MGR     | 12000.00
 Faviet      | FI_ACCOUNT |  9000.00
 Chen        | FI_ACCOUNT |  8200.00
 Sciarra     | FI_ACCOUNT |  7700.00
 Urman       | FI_ACCOUNT |  7800.00
 Popp        | FI_ACCOUNT |  6900.00
 Mallin      | ST_CLERK   |  3300.00
 Rogers      | ST_CLERK   |  2900.00
 Gee         | ST_CLERK   |  2400.00
 Philtanker  | ST_CLERK   |  2200.00
 Ladwig      | ST_CLERK   |  3600.00
 Stiles      | ST_CLERK   |  3200.00
 Seo         | ST_CLERK   |  2700.00
 Patel       | ST_CLERK   |  2500.00
 Bissot      | ST_CLERK   |  3300.00
 Atkinson    | ST_CLERK   |  2800.00
 Marlow      | ST_CLERK   |  2500.00
 Olson       | ST_CLERK   |  2100.00
 Rajs        | ST_CLERK   |  3500.00
 Davies      | ST_CLERK   |  3100.00
 Matos       | ST_CLERK   |  2600.00
 Vargas      | ST_CLERK   |  2500.00
 Russell     | SA_MAN     | 14000.00
 Partners    | SA_MAN     | 13500.00
 Errazuriz   | SA_MAN     | 12000.00
 Cambrault   | SA_MAN     | 11000.00
 Zlotkey     | SA_MAN     | 10500.00
 Himuro      | PU_CLERK   |  2600.00
 Colmenares  | PU_CLERK   |  2500.00
 Weiss       | ST_MAN     |  8000.00
 Fripp       | ST_MAN     |  8200.00
 Kaufling    | ST_MAN     |  7900.00
 Vollman     | ST_MAN     |  6500.00
 Mourgos     | ST_MAN     |  5800.00
 Bernstein   | SA_REP     |  9500.00
 Hall        | SA_REP     |  9000.00
 Olsen       | SA_REP     |  8000.00
 Cambrault   | SA_REP     |  7500.00
 Tuvault     | SA_REP     |  7000.00
 Sully       | SA_REP     |  9500.00
 McEwen      | SA_REP     |  9000.00
 Smith       | SA_REP     |  8000.00
 Nayer       | ST_CLERK   |  3200.00
 Mikkilineni | ST_CLERK   |  2700.00
 Landry      | ST_CLERK   |  2400.00
 Markle      | ST_CLERK   |  2200.00
 Doran       | SA_REP     |  7500.00
 Sewall      | SA_REP     |  7000.00
 Vishney     | SA_REP     | 10500.00
 Greene      | SA_REP     |  9500.00
 Marvins     | SA_REP     |  7200.00
 Lee         | SA_REP     |  6800.00
 Ande        | SA_REP     |  6400.00
 Banda       | SA_REP     |  6200.00
 Ozer        | SA_REP     | 11500.00
 Fox         | SA_REP     |  9600.00
 Smith       | SA_REP     |  7400.00
 Bates       | SA_REP     |  7300.00
 Kumar       | SA_REP     |  6100.00
 Abel        | SA_REP     | 11000.00
 Hutton      | SA_REP     |  8800.00
 Taylor      | SA_REP     |  8600.00
 Livingston  | SA_REP     |  8400.00
 Grant       | SA_REP     |  7000.00
 Johnson     | SA_REP     |  6200.00
 Whalen      | AD_ASST    |  4400.00
 Hartstein   | MK_MAN     | 13000.00
 Fay         | MK_REP     |  6000.00
 Mavris      | HR_REP     |  6500.00
 Higgins     | AC_MGR     | 12000.00
 Gietz       | AC_ACCOUNT |  8300.00

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to get the first name of the employee who holds the letter 'c' and 'e' in the first name.
Next: Write a query to display the last name of employees whose name contain exactly six characters.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.