SQL Exercise: Employees according to the department in ASC order

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

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

84. From the following table, write a SQL query to find the employees according to the department in ascending order. Return name, job name, department, salary, and grade.

Pictorial Presentation:

Sample table: employees

Sample table: department

Sample table: salary_grade

Sample Solution:

SELECT e.emp_name,
       e.job_name,
       d.dep_name,
       e.salary,
       s.grade
FROM employees e,
     department d,
     salary_grade s
WHERE e.dep_id = d.dep_id
  AND e.salary BETWEEN s.min_sal AND s.max_sal
ORDER BY e.dep_id ;

Sample Output:

 emp_name | job_name  | dep_name  | salary  | grade
----------+-----------+-----------+---------+-------
 KAYLING  | PRESIDENT | FINANCE   | 6000.00 |     5
 CLARE    | MANAGER   | FINANCE   | 2550.00 |     4
 MARKER   | CLERK     | FINANCE   | 1400.00 |     2
 SANDRINE | CLERK     | AUDIT     |  900.00 |     1
 SCARLET  | ANALYST   | AUDIT     | 3100.00 |     4
 ADNRES   | CLERK     | AUDIT     | 1200.00 |     1
 FRANK    | ANALYST   | AUDIT     | 3100.00 |     4
 JONAS    | MANAGER   | AUDIT     | 2957.00 |     4
 BLAZE    | MANAGER   | MARKETING | 2750.00 |     4
 JULIUS   | CLERK     | MARKETING | 1050.00 |     1
 ADELYN   | SALESMAN  | MARKETING | 1700.00 |     3
 WADE     | SALESMAN  | MARKETING | 1350.00 |     2
 MADDEN   | SALESMAN  | MARKETING | 1350.00 |     2
 TUCKER   | SALESMAN  | MARKETING | 1600.00 |     3
(14 rows)

Explanation:

The said query in SQL that retrieves the employee name, job name, department name, salary, and salary grade for each employee who falls within a certain salary range and belongs to a department, and orders the results by department ID.

A join happens by matching the 'employees' and 'department' tables based on the department ID column and the 'employees' and 'salary_grade' tables matches the salary falls within the salary range of the salary grade. This orders the results by the department ID.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: List the employees along with grades in ascending order.
Next SQL Exercise: Sort on highest salary of employees except CLERK.