Database Testing - SQL - Group By & Having Clause Examples

Group By & Having Clause:

22. How many employees who are working in different departments wise in the organization

Select department_id, count(*), from employee group by department_id

23. List out the department wise maximum salary, minimum salary, average salary of the employees

Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

24. List out the job wise maximum salary, minimum salary, average salaries of the employees.

Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

25. List out no. of employees joined in every month in ascending order

Select datepart(month, hiredate) 'Month' , count(*) 'No of Employees' from employee group by datepart(month, hiredate) order by datepart(month, hiredate)

26. List out the no.of employees for each month and year, in the ascending order based on the year, month.

Select datepart(Year, hiredate) 'Year', datepart(month, hiredate) 'month', count(*) 'No of employees' from employee group by datepart(year, hiredate), datepart(month, hiredate)

27. List out the department id having atleast four employees.

Select department_id, count(*) 'no of employees' from employee group by department_id having count(*)>=4

28. How many employees in January month.

Select datepart(month, hiredate) 'month', count(*) 'no of employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate)=1 --here 1 refers to January

29. How many employees who are joined in February or August month.

Select datepart(month, hiredate) 'month', count(*) 'No of Employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate) in (2,8)

30. How many employees who are joined in 1985.

Select datepart(Year, hiredate) 'Year', count(*) 'No of Employees' from employee group by datepart(Year, hiredate)having datepart(Year, hiredate) =1985

31. How many employees joined each month in 1985.

Select datepart(year,hiredate )'Year', datepart(month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(year, hiredate)=1985 group by datepart(year, hiredate),datepart(month, hiredate)

32. How many employees who are joined in May 1985.

Select datepart(Year, hiredate) 'Year', datepart(Month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(Year, hiredate)=1985 and datepart(month, hiredate)=5 group by datepart(Year, hiredate),datepart(Month, hiredate)

33. Which is the department id, having less than or equal to 3 employees joined in April 1985.

Select department_id, count(*) 'No. of employees' from employee where datepart(Year, hiredate)=1985 and datepart(Month, hiredate) = 4 group by datepart(Year, hiredate), datepart(Month, hiredate), department_id having count(*)<=3