SQL Exercise: Employees belong to the department where KAYLING works

SQL subqueries on employee Database: Exercise-65 with Solution

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

65. From the following tables, write a SQL query to find those employees who work in the department where KAYLING works. Return employee ID, employee name, department location, salary department name.

Sample table: employees

Sample table: department

Sample Solution:

SELECT e.emp_id,
       e.emp_name,
       d.dep_location,
       e.salary,
       d.dep_name
FROM employees e,
     department d
WHERE e.dep_id=d.dep_id
  AND e.dep_id IN
    (SELECT dep_id
     FROM employees
     WHERE emp_name = 'KAYLING'
       AND employees.emp_id <> e.emp_id);

Sample Output:

 emp_id | emp_name | dep_location | salary  | dep_name
--------+----------+--------------+---------+----------
  67832 | CLARE    | SYDNEY       | 2550.00 | FINANCE
  69324 | MARKER   | SYDNEY       | 1400.00 | FINANCE
(2 rows)

Explanation:

The said query in SQL that retrieves employee ID, employee name, department location, employee salary, and department name of all employees from the 'employees' and and 'department' tables where the department ID of the employee is the same as the department ID of the department they belong to, and the department ID is the department ID of employees other than the employee with name 'KAYLING'.

The WHERE clause joins the 'employees' and 'department' tables based on the department ID column.

The AND operator further filters the results to include only those rows where the department ID of the employee is same as the department ID obtained from a subquery.

The subquery selects the department ID of all employees except the employee with name 'KAYLING'.

The AND operator at last in the subquery excludes the employee with name 'KAYLING' from the list of employees.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Managers senior to KAYLING and junior to SANDRINE.
Next SQL Exercise: Employees with salary grades higher than MARKER.