SQL subqueries on employee Database: List the employees of grade 3 who have been hired in most recently and belongs to PERTH

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)

Practice Online

Structure of employee Database:

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

Previous: From the following table, write a SQL query to find the highest paid employees in the department MARKETING. Return complete information about the employees.
Next: From the following table, write a SQL query to find those employees who are senior to recently hired employee and work under KAYLING. Return complete information about the employees.