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:
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 Tree:
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: employee
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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics