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.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 

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