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

# 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 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,
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
``````

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,
WHERE salary BETWEEN min_sal AND max_sal
``````

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿