SQL Exercise: Highest paid PERTH employees before the latest grade 2

SQL subqueries on employee Database: Exercise-71 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

71. From the following tables, write a SQL query to find the highest paid employees of PERTH who joined before recently hired employee of grade 2. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample table: salary_grade

Sample Solution:

SELECT *
FROM employees
WHERE salary =
    (SELECT max(salary)
     FROM employees e,
          department d
     WHERE e.dep_id = d.dep_id
       AND d.dep_location = 'PERTH'
       AND hire_date <
         (SELECT max(hire_date)
          FROM employees e,
               salary_grade s
          WHERE e.salary BETWEEN s.min_sal AND s.max_sal
            AND s.grade = 2));

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  66928 | BLAZE    | MANAGER  |      68319 | 1991-05-01 | 2750.00 |            |   3001
(1 row)

Explanation:

The said query in SQL that retrieves all columns from the 'employees' table where the employee with the highest salary in the department located in Perth, and whose hire date is earlier than the most recent hire date among all employees in salary grade 2.

The WHERE clause filters the results to include only employees whose salary is equal to the maximum salary in the department located in Perth.

The highest salary in the department located in Perth is obtained by the inner subquery, which joins the 'employees' and 'department' tables on the dep_id column and filters by the dep_location column to only include employees in the Perth department.

The hire date of the selected employee is compared to the maximum hire date of all employees in salary grade 2, obtained by another subquery, which joins the 'employees' and 'salary_grade' tables on the salary column and filters by the grade column to only include employees in grade 2.

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Most recently hired employees of department 3001.
Next SQL Exercise: List the highest paid employees working under KAYLING.