Page 177 - Informatics_Practices_Fliipbook_Class12
P. 177

Execution of the above statement will produce the output shown in Table 4.31.

                                                        Maximum_Salary
                                                             100000
                                           Table 4.31 Maximum salary amongst all employees

                 For example, to find the employees' first name and last name having a maximum salary, it may be tempting to use
               the following SQL statement:
               SELECT FName, LName, MAX(Salary) AS Maximum_Salary
               FROM EMPLOYEE;
               However, on the execution of the above statement, displays only one row for an employee who has the maximum
               salary (Table 4.32).
                                        FName                LName           Maximum_Salary
                                          Raj                Reddy                100000

                                  Table 4.32 First name and last name of employee having a maximum salary
            Let us first find the maximum salary by executing the sub-query (often called a nested query):

               SELECT MAX(Salary)
               FROM EMPLOYEE
            Next, we look for the employees who have the maximum salary (found on the execution of the above query) as follows:

               SELECT FName, LName, Salary
               FROM EMPLOYEE
               WHERE Salary = (SELECT MAX(Salary)
                                   FROM EMPLOYEE);

               Execution of the above query will produce the output shown in Table 4.33.
                                            FName            LName            Salary
                                              Raj            Reddy            100000
                                            Muskan           Taneja           100000
                                             Hiten           Oberoi           100000
                                            Anshul           Verma            100000
                                             Table 4.33 Employees getting maximum salary

               When a query encloses another query, the inner query is called a nested query. In the above example, the query
               SELECT MAX(Salary)
               FROM Employee;
               is an example of a nested query.
            4.  MIN(): MIN() yields the minimum value of the specified attribute.
               For example, to find the minimum salary from the EMPLOYEE table, the SQL query will be:
               SELECT MIN(Salary) AS Minimum_Salary

               FROM EMPLOYEE;
               Execution of the above query will produce the output as given in Table 4.34.

                                                        Minimum_Salary
                                                             50000
                                             Table 4.34 Least salary amongst all employees


                                                                                        Database Query using SQL  163
   172   173   174   175   176   177   178   179   180   181   182