w3resource
SQL exercises

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

SQL employee Database: Exercise-82 with Solution

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

82. Write a query in SQL to 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)

Practice Online


Sample Database: employee

employee database structure

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

Previous: Write a query in SQL to list the employees in ascending order of designations of those, joined after the second half of 1991.
Next: Write a query in SQL to display the total information of the employees along with grades in ascending order.

What is the difficulty level of this exercise?