w3resource

SQL Exercise: Find employees who is working except given departments

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 Expression: Display  the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80.

Relational Algebra Tree:

Relational Algebra Tree: Display  the employee ID, first name, job id, and department number for those employees who is working except the departments 50,30 and 80.

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 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.



Follow us on Facebook and Twitter for latest update.

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