Page 178 - Informatics_Practices_Fliipbook_Class12
P. 178

5.  AVG(): AVG() function yields the average value of the specified attribute.
            To find the average salary of all employees, the query will be:

            SELECT AVG(Salary) AS Average_Salary
            FROM EMPLOYEE;

            Execution of the above query will produce the output shown in Table 4.35.

                                                     Average_Salary
                                                       79500.0000
                                Table 4.35 Average salary of  employees rounded to 4 decimal places

        Note that the results of applying the AVG() function will look more elegant if the average salary is shown up to two
        places of decimal. For this purpose, SQL provides a ROUND() function, which rounds a number to a specified number
        of decimal places. In the following query, ROUND() is used to round the average salary in each department to two
        decimal places.

            SELECT ROUND(AVG(Salary),2) AS Average_Salary
            FROM EMPLOYEE;
            Execution of the above query will produce the output shown  in Table 4.36.


                                                     Average_Salary
                                                        79500.00
                               Table 4.36 Average salary of  employees rounded to  two decimal places

        4.4.20 GROUP BY CLAUSE

        GROUP BY clause groups the tuples based on value(s) of the specified attribute(s)
        The GROUP BY  clause returns summary data based on one or more groupings. The grouping may involve one or
        more attributes of the table. For example, we can use the GROUP BY clause to count the number of employees in
        each department of the EMPLOYEE table, or to find the total salary of each department in the EMPLOYEE table. The
        GROUP BY clause can also be used with aggregate functions to generate database summary reports. The syntax for
        GROUP BY clause is given below:
        SELECT attribute1, attribute2, ..., aggregate_function (attributeN)
        FROM table_name

        [WHERE conditions]
        GROUP BY attribute1, attribute2, ...
        [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.

        SELECT Dept_No, MIN(Salary) AS MinSalary,
             MAX(Salary) AS MaxSalary,

             ROUND(AVG(Salary),2) AS AverageSalary
        FROM EMPLOYEE
        GROUP BY Dept_No;
        In the above statement, the ROUND() is used to round the average salary in each department to two decimal places.
        Execution of the above query will produce the output as given in Table 4.37.


          164  Touchpad Informatics Practices-XII
   173   174   175   176   177   178   179   180   181   182   183