Page 179 - Informatics_Practices_Fliipbook_Class12
P. 179
Dept_No MinSalary MaxSalary AverageSalary
1 85000 100000 92500.00
2 70000 100000 90000.00
4 60000 100000 73333.33
5 50000 70000 60000.00
Table 4.37 Minimum, maximum, and average salary (rounded) in each department
4.4.21 HAVING CLAUSE
Condition in the HAVING clause applies to a group as a whole, which is used with GROUP BY clause to specify
conditions.
The HAVING clause is used to specify conditions for choosing the groups that will appear in the SQL output. The
conditions in the WHERE clause apply to the column attributes for selecting the rows. The conditions in the HAVING
clause apply to the groups that the GROUP BY clause has generated.
The following syntax of SELECT statement includes FROM, WHERE, GROUP BY, HAVING, ORDER BY clauses
is as follows:
SELECT attribute1, attribute2, ..., aggregate_function (attributeJ)
FROM table_name
[WHERE conditions]
GROUP BY attribute1, attribute2, ...
[HAVING conditions]
[ORDER BY attribute [ ASC | DESC ]];
Query: To display the minimum, maximum, and average salary (rounded to two decimal places) of each department
from the EMPLOYEE table having the average salary greater than 70000, the query will be
SELECT Dept_No, MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary,
ROUND(AVG(Salary),2) AS AverageSalary
FROM EMPLOYEE
GROUP BY Dept_No
HAVING AVG(Salary)>70000;
On executing the above statement, SQL will display the output shown in Table 4.38.
Dept_No MinSalary MaxSalary AverageSalary
1 85000 100000 92500.00
2 70000 100000 90000.00
4 60000 100000 73333.33
Table 4.38 Minimum, maximum, and average salary (rounded to two decimal places) of the departments
having the average salary greater than 70000
Query: Display the range of salary for employees residing in the same city having the range of salary greater than or
equal to 30000.
SELECT City, (MAX(Salary) - MIN(Salary)) AS RangeSalary
FROM EMPLOYEE
GROUP BY City
HAVING (MAX(Salary) - MIN(Salary)) >= 30000;
Database Query using SQL 165

