w3resource

SQL Exercise: Find employees who is working given departments

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

21. From the following table, write a SQL query to find the employees whose department numbers are included in 30, 40, or 90. 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 IN (30 , 40 , 90);

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
         114 | Den        | PU_MAN   |            30
         115 | Alexander  | PU_CLERK |            30
         116 | Shelli     | PU_CLERK |            30
         117 | Sigal      | PU_CLERK |            30
         118 | Guy        | PU_CLERK |            30
         119 | Karen      | PU_CLERK |            30
         203 | Susan      | HR_REP   |            40
(10 rows)

Code Explanation:

The said query in SQL that retrieve data from the 'employees' table and select specific columns such as "employee_id", "first_name", "job_id", and "department_id".
The rows of the result are filtered based on the value of the "department_id" column, which must be either 30, 40, or 90.

Relational Algebra Expression:

Relational Algebra Expression: Display the employee Id, first name, job id, and department number for those employees whose department number equals 30, 40 or 90.

Relational Algebra Tree:

Relational Algebra Tree: Display the employee Id, first name, job id, and department number for those employees whose department number equals 30, 40 or 90.

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 whose department number equals 30, 40 or 90 - Duration

Rows:

Query visualization of Display the employee Id, first name, job id, and department number for those employees whose department number equals 30, 40 or 90 - Rows

Cost:

Query visualization of Display the employee Id, first name, job id, and department number for those employees whose department number equals 30, 40 or 90 - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find employees who is working except given departments.
Next SQL Exercise: Find employees who did two or more jobs in the past.

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