Practice SQL Queries On Having Clause

Practice Question 1: Filtering by Average Salary

Problem Statement:

Write a SQL query to find departments where the average salary is strictly greater than 55,000. Display the department name and its average salary.

Solution

SQL

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 55000;

Explanation

1. GROUP BY Department: Groups the rows in the table by each unique department (IT, HR, Sales, Finance, Marketing).

2. AVG(Salary): Calculates the average salary for each of those department groups.

3. HAVING AVG(Salary) > 55000: Filters the grouped results, keeping only the departments where the calculated average meets the condition. (WHERE cannot be used here because WHERE filters individual rows before grouping takes place).

Practice Question 2: Finding Large Teams (Counting Grouped Rows)

Problem Statement:

Write a SQL query to find departments that have more than 3 employees. Display the department name and the total employee count.

Solution

SQL

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 3;

Explanation

1. COUNT(*): Counts how many employee rows belong to each department group.

2. HAVING COUNT(*) > 3: Ensures that only departments with a headcount greater than 3 are returned in the final output. For instance, if HR has 3 employees and IT has 4, HR will be filtered out.

Practice Question 3: Combining WHERE and HAVING

Problem Statement:

Write a SQL query to find departments where the average salary of employees who have more than 1 year of experience is greater than 60,000. Display the department and that filtered average salary.

Solution

SQL

SELECT Department, AVG(Salary) AS FilteredAvgSalary
FROM Employees
WHERE Experience > 1
GROUP BY Department
HAVING AVG(Salary) > 60000;

Explanation

1. WHERE Experience > 1: This row-level filter runs first, removing any employee with 1 year or less of experience before any grouping happens.

2. GROUP BY Department: Groups the remaining filtered employees by department.

3. HAVING AVG(Salary) > 60000: Filters the groups to only include departments where the average salary of the experienced staff exceeds 60,000.

Practice Question 4: Maximum Salary Threshold per Department

Problem Statement:

Write a SQL query to find departments where the maximum salary paid is at least 75,000. Display the department name and the highest salary in that department.

Solution

SQL

SELECT Department, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) >= 75000;

Explanation

1. MAX(Salary): Finds the highest salary within each department group.

2. HAVING MAX(Salary) >= 75000: Filters out any department where the peak earner makes less than 75,000. Departments like IT or Finance that feature high salaries will appear, while departments with lower top-end pay will be excluded.