SQL Exercise: Display department, grade, and number of SALESMEN
SQL employee Database: Exercise-100 with Solution
[An editor is available at the bottom of the page to write and execute the scripts.]
100. From the following table, write a SQL query to identify departments with at least two SALESMEN in each grade. Return department name, grade and number of employees.
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Solution:
SELECT d.dep_name,
s.grade,
count(*)
FROM employees e,
department d,
salary_grade s
WHERE e.dep_id = d.dep_id
AND e.job_name = 'SALESMAN'
AND e.salary BETWEEN s.min_sal AND s.max_sal
GROUP BY d.dep_name,
s.grade
HAVING count(*) >= 2;
Sample Output:
dep_name | grade | count -----------+-------+------- MARKETING | 2 | 2 MARKETING | 3 | 2 (2 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 SQL Exercise: Grade, number of employees, and salary for each grade.
Next SQL Exercise: List the number of employees in each department.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Count the number of occurrences of a string in a VARCHAR field?
SELECT title, description, ROUND ( ( LENGTH(description) - LENGTH( REPLACE ( description, "value", "") ) ) / LENGTH("value") ) AS count FROM <table>
Ref: https://bit.ly/3PvNdT4
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion