The following SELECT statement retrieves the employees with the highest salary and the most years of experience:
SELECT emp_no, name, job_title, salary, yrs_exp FROM employee WHERE (salary,yrs_exp) >= ALL (SELECT salary,yrs_exp FROM employee);
| Number of Rows Returned | Result When ALL is TRUE |
|---|---|
| 0 | Employees with the highest salary do not also have the most years experience. |
| 1 | There is only one employee with the highest salary and years experience. |
| Multiple | There is more than one employee with the highest salary and years experience. |
For the current data in the employee table, the result looks like this:
EmpNo Name JobTitle Salary YrsExp ----- --------- --------- --------- ------- 10018 Russell S President 65,000.00 25
The result shows that one employee has both the highest salary and the most years of experience.