w3resource

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

SQL employee Database: Exercise-82 with Solution

[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:

SQL exercises on employee Database: List the total information of employees table along with department, and location of all the employees working under FINANCE and AUDIT in the ascending department no

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 Expression: List the total information of employees table along with department, and location of all the employees working under FINANCE and AUDIT in the ascending department no.

Relational Algebra Tree:

Relational Algebra Tree: List the total information of employees table along with department, and location of all the employees working under FINANCE and AUDIT in the ascending department no.

Practice Online


Sample Database: employee

employee database structure

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.

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.