SQL exercises on employee Database: Display the Grade, Number of employees, and maximum salary of each grade

SQL employee Database: Exercise-99 with Solution

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

99. From the following table, write a SQL query to list the grade, number of employees, and maximum salary of each grade.

Sample table: employees

Sample table: salary_grade

Sample Solution:

SELECT s.grade,
       count(*),
       max(salary)
FROM employees e,
     salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
GROUP BY s.grade;

Sample Output:

 grade | count |   max
-------+-------+---------
     4 |     5 | 3100.00
     1 |     3 | 1200.00
     5 |     1 | 6000.00
     3 |     2 | 1700.00
     2 |     3 | 1400.00
(5 rows)

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: From the following table, write a SQL query to find those departments where at least two employees work. Return department id, number of employees.
Next: From the following table, write a SQL query to find those departments where at least two employees work as a SALESMAN in each grade. Return department name, grade and number of employees.