Problem

Given a table of data with employee id, name, and salary, write a stored procedure that returns the name and salary of the highest paid employee.
Input:
Table:
+--------+----------+--------+
| emp_id | emp_name | salary |
+--------+----------+--------+
| 1      | John     | 10000  |
| 2      | Jane     | 12000  |
| 3      | Joe      | 11000  |
+--------+----------+--------+
Output:
+----------+--------+
| emp_name | salary |
+----------+--------+
| Jane     | 12000  |
+----------+--------+

Solution

SELECT emp_name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee)

/*

The solution above is optimal because it uses a subquery to find the maximum salary and then returns the name and salary of the employee with that salary.

*/