SQL Exercise: Employees of grade 2 and 3 who belongs to a city

SQL subqueries on employee Database: Exercise-61 with Solution

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

61. From the following tables, write a SQL query to list the employees of grade 2 or 3 and the department where he or she works, is located in the city PERTH. Return complete information about the employees.

Sample table: employees

Sample table: department

Sample table: salary_grade

Sample Solution:

SELECT *
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 IN (2,
                       3))
  AND dep_id IN
    (SELECT dep_id
     FROM department
     WHERE dep_LOCATION='PERTH');

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
(4 rows)

Explanation:

The said query in SQL that retrieves all employees from the 'employees' table where the employee's ID is in the list of employees with a salary grade of 2 or 3 and the department ID of the employee is in the list of department IDs located in Perth.

The WHERE clause filters the results to include only those rows where the employee ID is in a subquery.

The subquery joins the 'employees' table with the 'salary_grade' table by checking that the employee's salary is between the minimum and maximum salary for a grade of 2 or 3.

The outer AND clause further filters the results to include only those records where the department ID of the employee is in a subquery. The subquery returns a list of department IDs from the 'department' table that are located in Perth.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: List the details of the employees working at PERTH.
Next SQL Exercise: Designation is same as ADLYNE or salary more than WADE.