SQL exercises on employee Database: Display the department name, grade, no. of employees where at least two employees are working as a SALESMAN

SQL employee Database: Exercise-100 with Solution

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

100. Write a query in SQL to display the department name, grade, no. of employees where at least two employees are working as a SALESMAN.

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)

Practice Online

Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to display the Grade, Number of employees, and maximum salary of each grade.
Next: Write a query in SQL to list the no. of employees in each department where the no. is less than 4.