In MySQL, you want the salary that appears in the nth row after sorting all…

In MySQL, you want the salary that appears in the nth row after sorting all salaries from the Employees table in descending (highest-first) order, where n is a positive integer chosen at run time.

Several of the options compute an offset into a session variable and then run a prepared statement that binds that value into the LIMIT clause (MySQL allows LIMIT parameters as ? placeholders in a prepared statement, but not as bare arithmetic expressions). Which option returns exactly the nth-highest salary?

  1. A.
    SET @off = n;
    PREPARE stmt FROM
      'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
    EXECUTE stmt USING @off;
  2. B.
    SET @off = n - 1;
    PREPARE stmt FROM
      'SELECT SALARY FROM Employees ORDER BY SALARY ASC LIMIT ?, 1';
    EXECUTE stmt USING @off;
  3. C.
    SET @off = n - 1;
    PREPARE stmt FROM
      'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT 1, ?';
    EXECUTE stmt USING @off;
  4. D.
    SET @off = n - 1;
    PREPARE stmt FROM
      'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
    EXECUTE stmt USING @off;
  5. E.
    SELECT MAX(SALARY)
    FROM Employees
    WHERE SALARY < n;

Attempted by 221 students.

Sign up free to check your answer

Sign up free

Explore the full course: Gate Guidance By Sanchit Sir

Loading lesson…