SQL subqueries on employee Database: List the ID, name, location, salary, and department of the all the employees belonging 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)

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 those managers who are senior to KAYLING and who are junior to SANDRINE. Return complete information about the employees.
Next: From the following tables, write a SQL query to find those employees whose salary grade is greater than the grade of MARKER. Return complete information about the employees.