SQL Exercise: List the grade 3 employees from Perth hired recently

SQL subqueries on employee Database: Exercise-20 with Solution

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

20. From the following tables, write a SQL query to find the employees of grade 3 who joined recently and location at PERTH. Return employee ID, employee name, job name, hire date, and salary.

Sample table: employees

Sample table: department

Sample table: salary_grade

Sample Solution:

SELECT e.emp_id, e.emp_name, e.job_name, e.hire_date,e.salary
FROM employees e
WHERE e.dep_id IN
    (SELECT d.dep_id
     FROM department d
     WHERE d.dep_location = 'PERTH')
  AND e.hire_date IN
    (SELECT max(hire_date)
     FROM employees
     WHERE emp_id IN
         (SELECT emp_id
          FROM employees e,
               salary_grade s
          WHERE e.salary BETWEEN s.min_sal AND s.max_sal
            AND s.grade = 3)) ;

OR

SELECT e.emp_id, e.emp_name, e.job_name, e.hire_date,e.salary
FROM employees e,
     department d
WHERE d.dep_location='PERTH'
  AND hire_date IN
    (SELECT max(hire_date)
     FROM employees e,
          salary_grade s
     WHERE salary BETWEEN min_sal AND max_sal
       AND grade=3);

Sample Output:

 emp_id | emp_name | job_name | hire_date  | salary
--------+----------+----------+------------+---------
  68454 | TUCKER   | SALESMAN | 1991-09-08 | 1600.00
(1 row)

Explanation:

The said query in SQL that selects the employee ID, name, job title, hire date, and salary of the employees from the employees table who work in Perth and were hired most recently among those who satisfy the salary criteria for the third salary grade.

The subquery within the IN operator filters only those employees who work in the departments located in Perth.

The second subquery selects the maximum hire_date among the employees whose salary falls within the range of the third salary grade, as defined in the salary_grade table. The subquery is then filters to only consider employees who were hired on that latest hire_date among employees who satisfy the salary criteria.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List jobs of department ID 1001, not found in ID 2001.
Next SQL Exercise: Employees senior to most recently hired under KAYLING.