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);
| The query returns this many rows … | WHEN this condition evaluates to 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.