SQL Exercise: Grade, number of employees, and salary for 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)

Explanation:

The said query in SQL that retrieves information about the number of employees and the maximum salary within each salary grade.

A join is performed between the 'employees' and 'salary_grade' tables based on the employee's salary within the "min_sal" and "max_sal" columns in the 'salary_grade' table.

The "GROUP BY" clause groups the results by salary grade, so the query returns one row for each salary grade with the count and maximum salary for employees in that grade.

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: Number of employee for each job in each department.
Next SQL Exercise: Display department, grade, and number of SALESMEN.