SQL Exercises: Display employees not working in certain departments
SQL SUBQUERY: Exercise-11 with Solution
From the following tables, write a SQL query to find those employees who do not work in the departments where managers’ IDs are between 100 and 200 (Begin and end values are included.). Return all the fields of the employees.
Sample table: employees
Sample table: departments
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE manager_id BETWEEN 100 AND 200);
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id 178 Kimberely Grant KGRANT 011.44.1644.429263 2007-05-24 SA_REP 7000.00 0.15 149 0 201 Michael Hartstein MHARTSTE515.123.5555 2004-02-17 MK_MAN 13000.00 0.00 100 20 202 Pat Fay PFAY 603.123.6666 2005-08-17 MK_REP 6000.00 0.00 201 20 203 Susan Mavris SMAVRIS 515.123.7777 2002-06-07 HR_REP 6500.00 0.00 101 40 204 Hermann Baer HBAER 515.123.8888 2002-06-07 PR_REP 10000.00 0.00 101 70 205 Shelley Higgins SHIGGINS515.123.8080 2002-06-07 AC_MGR 12000.00 0.00 101 110 206 William Gietz WGIETZ 515.123.8181 2002-06-07 AC_ACCOUNT8300.00 0.00 205 110
The said query that retrieves all columns (denoted by *) from the employees table where the department ID does not match any of the department IDs in the departments table with a manager ID between 100 and 200, inclusive. The subquery in the NOT IN clause retrieves the department IDs from the departments table that meet the specified manager ID criteria. The result of the query will be all employees who are not associated with departments that have a manager ID between 100 and 200.
SELECT e.* FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.manager_id NOT BETWEEN 100 AND 200 OR d.manager_id IS NULL;
SELECT * FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.manager_id BETWEEN 100 AND 200 );
Contribute your code and comments through Disqus.
Previous SQL Exercise: Employees, salary is between smallest salary and 2500.
Next SQL Exercise: Display employees who get the second highest salary.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join