w3resource
SQL exercises

SQL SORTING and FILTERING on HR Database: Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80

SQL SORTING and FILTERING on HR Database: Exercise-20 with Solution

20. Write a query in SQL to display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80.

Sample table : employees


Sample Solution:

SELECT employee_id, first_name, job_id, department_id
 FROM employees
  WHERE department_id NOT IN (50, 30, 80);

Sample Output:

 employee_id | first_name  |   job_id   | department_id
-------------+-------------+------------+---------------
         100 | Steven      | AD_PRES    |            90
         101 | Neena       | AD_VP      |            90
         102 | Lex         | AD_VP      |            90
         103 | Alexander   | IT_PROG    |            60
         104 | Bruce       | IT_PROG    |            60
         105 | David       | IT_PROG    |            60
         106 | Valli       | IT_PROG    |            60
         107 | Diana       | IT_PROG    |            60
         108 | Nancy       | FI_MGR     |           100
         109 | Daniel      | FI_ACCOUNT |           100
         110 | John        | FI_ACCOUNT |           100
         111 | Ismael      | FI_ACCOUNT |           100
         112 | Jose Manuel | FI_ACCOUNT |           100
         113 | Luis        | FI_ACCOUNT |           100
         178 | Kimberely   | SA_REP     |             0
         200 | Jennifer    | AD_ASST    |            10
         201 | Michael     | MK_MAN     |            20
         202 | Pat         | MK_REP     |            20
         203 | Susan       | HR_REP     |            40
         204 | Hermann     | PR_REP     |            70
         205 | Shelley     | AC_MGR     |           110
         206 | William     | AC_ACCOUNT |           110
(22 rows)

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Duration

Rows:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Rows

Cost:

Query visualization of Display the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80 - Cost

Contribute your code and comments through Disqus.

Previous: Write a query in SQL to display the first and last name, and department number for those employees who holds a letter s as a 3rd character in their first name.
Next: Write a query in SQL to display the employee Id, first name, job id, and department number for those employees whose department number equals 30, 40 or 90.

What is the difficulty level of this exercise?