w3resource

SQL Exercise: List the employees along with grades in ascending order

SQL employee Database: Exercise-83 with Solution

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

83. From the following tables, write a SQL query to find the employees along with grades in ascending order. Return complete information about the employees.

Pictorial Presentation:

SQL exercises on employee Database: Display the total information of the employees along with grades in ascending order

Sample table: employees


Sample table: salary_grade


Sample Solution:

SELECT *
FROM employees e,
     salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
ORDER BY grade ASC;

OR

SELECT *
FROM employees e,
     salary_grade s
WHERE e.salary >= s.min_sal
  AND e.salary <= s.max_sal
ORDER BY s.grade ASC;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id | grade | min_sal | max_sal
--------+----------+-----------+------------+------------+---------+------------+--------+-------+---------+---------
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001 |     1 |     800 |    1300
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001 |     1 |     800 |    1300
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001 |     1 |     800 |    1300
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 |     2 |    1301 |    1500
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001 |     2 |    1301 |    1500
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001 |     2 |    1301 |    1500
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 |     3 |    1501 |    2100
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001 |     3 |    1501 |    2100
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001 |     4 |    2101 |    3100
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001 |     4 |    2101 |    3100
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001 |     4 |    2101 |    3100
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001 |     4 |    2101 |    3100
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001 |     4 |    2101 |    3100
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001 |     5 |    3101 |    9999
(14 rows)

Explanation:

The said query in SQL that selects all columns from the tables 'employees' and 'salary_grade' where the employee's salary falls within the minimum and maximum salary range of a certain grade. The results are ordered by grade in ascending order.

The implicit join between 'employees' and 'salary_grade' table where the employee's salary must be between the minimum and maximum salary of the grade.

Relational Algebra Expression:

Relational Algebra Expression: Display the total information of the employees along with grades in ascending order.

Relational Algebra Tree:

Relational Algebra Tree: Display the total information of the employees along with grades in ascending order.

Relational Algebra Expression:

Relational Algebra Expression: Display the total information of the employees along with grades in ascending order.

Relational Algebra Tree:

Relational Algebra Tree: Display the total information of the employees along with grades in ascending order.

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: Employees under a given department ORDER BY dep_id ASC.
Next SQL Exercise: Employees according to the department in ASC order.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/employee-database-exercise/sql-employee-database-exercise-83.php