New Enroll in our GIC Finance Online Course Here!

    Register

    Question

    Which of the following SQL queries is used to find the

    second highest salary from an employee table? SELECT MAX (salary)         FROM employee WHERE salary < ( SELECT MAX (salary) FROM employee);
    A The query will return the second highest salary correctly. Correct Answer Incorrect Answer
    B The query will return the highest salary instead of the second highest Correct Answer Incorrect Answer
    C The query will cause an error because of the nested SELECT statement Correct Answer Incorrect Answer
    D The query will return an empty result set Correct Answer Incorrect Answer
    E The query will return multiple rows instead of one. Correct Answer Incorrect Answer

    Solution

    The query uses a nested SELECT statement to retrieve the second-highest salary from the employee table. Let's break it down:

    1. Inner query: SELECT MAX(salary) FROM employee retrieves the highest salary in the employee table.
    2. Outer query: The WHERE salary < (SELECT MAX(salary) FROM employee) ensures that only those salaries smaller than the highest salary are considered, and the outer MAX(salary) retrieves the largest salary from that set, effectively giving the second-highest salary.
    Why other options are incorrect:
    • B is incorrect because the query does not return the highest salary but rather the second-highest salary by excluding the maximum salary.
    • C is incorrect because nested SELECT statements are valid in SQL and do not cause errors here.
    • D is incorrect because the query will return a value unless there are no records in the employee table.
    • E is incorrect because the query will return a single value—the second-highest salary.

    Practice Next