w3resource
Run SQL Query

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 Expression: Display the department name, grade, no. of employees where at least two employees are working as a SALESMAN.

Relational Algebra Tree:

Relational Algebra Tree: Display the department name, grade, no. of employees where at least two employees are working as a SALESMAN.

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: 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.



Share this Tutorial / Exercise on : Facebook and Twitter

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