SQL Exercise: Find employees who is working except given departments

Last update on March 31 2023 06:07:50 (UTC/GMT +8 hours)

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

20. From the following table, write a SQL query to find those employees work in the departments that are not part of the department 50 or 30 or 80. Return employee_id, first_name,job_id, department_id.

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)

Code Explanation:

The said query in SQL that retrieves "employee_id", "first_name", "job_id", and "department_id" from a table named 'employees'. The query returns only the rows where the "department_id" is not equal to 50, 30, or 80.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees with s as their 3rd character in first name.
Next SQL Exercise: Find employees who is working given departments.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook