New Click here to Enroll for SIDBI MSME Expert Classes by Experts

    Register

    Question

    Which of the following SQL queries will return the

    second highest salary from a table Employee with columns Name and Salary?
    A SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 2; Correct Answer Incorrect Answer
    B SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee); Correct Answer Incorrect Answer
    C SELECT Salary FROM Employee WHERE Salary = (SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1); Correct Answer Incorrect Answer
    D SELECT Salary FROM Employee WHERE Salary = (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1); Correct Answer Incorrect Answer
    E SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC; Correct Answer Incorrect Answer

    Solution

    This query uses a subquery to find the maximum salary and excludes it using NOT IN. The outer query then finds the maximum salary among the remaining values, effectively identifying the second highest salary. • Why this works: The subquery isolates the highest salary. The outer query ensures the second highest value is identified without requiring an OFFSET. This method works across most SQL systems and avoids platform-specific syntax like LIMIT or TOP. ________________________________________ Why Other Options Are Incorrect: 1. LIMIT 2: Returns two highest salaries but doesn’t isolate the second highest. 2. OFFSET 1: Many SQL platforms do not support LIMIT ... OFFSET or require modifications for accurate execution. 3. DISTINCT Salary: Redundant use of DISTINCT, which is unnecessary when ORDER BY already ensures unique ordering. 4. TOP 2: TOP is SQL Server-specific and requires additional filtering for the second value.

    Practice Next