SQL Exercise: Employees under a given department ORDER BY dep_id ASC

Last update on December 20 2024 11:40:43 (UTC/GMT +8 hours)

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

82. From the following table, write a SQL query to find the location of all the employees working in the FINANCE or AUDIT department. Sort the result-set in ascending order by department ID. Return complete information about the employees.

Pictorial Presentation:

Sample table: employees

Sample table: department

Sample Solution:

SELECT *
FROM employees e,
     department d
WHERE (dep_name = 'FINANCE'
       OR dep_name ='AUDIT')
  AND e.dep_id = d.dep_id
ORDER BY e.dep_id ASC;

OR

SELECT *
FROM employees e,
     department d
WHERE d.dep_name IN ('FINANCE',
                     'AUDIT')
  AND e.dep_id = d.dep_id
ORDER BY e.dep_id ASC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id | dep_id | dep_name | dep_location
--------+----------+-----------+------------+------------+---------+------------+--------+--------+----------+--------------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001 |   1001 | FINANCE  | SYDNEY
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001 |   2001 | AUDIT    | MELBOURNE
(8 rows)

Explanation:

The said query in SQL that selects all columns (*) from the employees table and the department table where the department name is either 'FINANCE' or 'AUDIT', and the dep_id column in the employees table matches the dep_id column in the department table.

The WHERE clause includes only those employees who belong to the 'FINANCE' or 'AUDIT' and joins the employees and department tables on the dep_id column.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Sort employees by designation, joining after 1991.
Next SQL Exercise: List the employees along with grades in ascending order.