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

