w3resource

SQL Subquery Exercises: Display all the information of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200

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


Sample Solution:

SELECT * 
FROM employees 
WHERE department_id NOT IN 
(SELECT department_id 
FROM departments 
WHERE manager_id BETWEEN 100 AND 200);

Sample Output:

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

Pictorial Presentation:

SQL Subqueries: Display all the information of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200.

Practice Online


Query Visualization:

Duration:

Query visualization of Display all the information of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200 - Duration

Rows:

Query visualization of Display all the information of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200 - Rows

Cost:

Query visualization of Display all the information of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200 - Cost

Contribute your code and comments through Disqus.

Previous: From the following table and write a SQL query to find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.
Next: From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ