w3resource

SQL exercises on employee Database: List the employee id, name, location, department of all the departments 1001 and 2001

SQL employee Database: Exercise-94 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

94. From the following table, write a SQL query to find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name.

Sample table: employees


Sample table: department


Sample Solution:

SELECT e.emp_id,
       e.emp_name,
       e.dep_id,
       d.dep_location,
       d.dep_name
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
  AND e.dep_id IN (1001,
                   2001);

Sample Output:

emp_id | emp_name | dep_id | dep_location | dep_name 
--------+----------+--------+--------------+----------
  68319 | KAYLING  |   1001 | SYDNEY       | FINANCE
  67832 | CLARE    |   1001 | SYDNEY       | FINANCE
  65646 | JONAS    |   2001 | MELBOURNE    | AUDIT
  68736 | ADNRES   |   2001 | MELBOURNE    | AUDIT
  69324 | MARKER   |   1001 | SYDNEY       | FINANCE
  67858 | SCARLET  |   2001 | MELBOURNE    | AUDIT
  69062 | FRANK    |   2001 | MELBOURNE    | AUDIT
  63679 | SANDRINE |   2001 | MELBOURNE    | AUDIT
(8 rows)

Relational Algebra Expression:

Relational Algebra Expression: List the employee id, name, location, department of all the departments 1001 and 2001.

Relational Algebra Tree:

Relational Algebra Tree: List the employee id, name, location, department of all the departments 1001 and 2001.

Practice Online


Sample Database: employee

employee database structure

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

Previous: From the following table, write a SQL query to list the employee id, name, department id, location of all the employees.
Next: From the following table, write a SQL query to find those employees whose salary is in the range minimum and maximum salary (Begin and end values are included.). Return employee ID, name, salary and grade.

What is the difficulty level of this exercise?