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
   174   175   176   177   178   179   180   181   182   183   184