SQL Exercises: Employees in a department beginning with T

SQL SUBQUERY : Exercise-14 with Solution

From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains the letter 'T'. Return employee ID, first name and last name.

Sample table: employees

Sample Solution:

SELECT employee_id, first_name, last_name  
FROM employees  
WHERE department_id IN  
( SELECT department_id  
FROM employees  
WHERE first_name LIKE '%T%' );

Sample Output:

employee_id	first_name	last_name
120		Matthew		Weiss
121		Adam		Fripp
122		Payam		Kaufling
123		Shanta		Vollman
124		Kevin		Mourgos
125		Julia		Nayer
126		Irene		Mikkilineni
127		James		Landry
128		Steven		Markle
129		Laura		Bissot
130		Mozhe		Atkinson
131		James		Marlow
132		TJ		Olson
133		Jason		Mallin
134		Michael		Rogers
135		Ki		Gee
136		Hazel		Philtanker
137		Renske		Ladwig
138		Stephen		Stiles
139		John		Seo
140		Joshua		Patel
141		Trenna		Rajs
142		Curtis		Davies
143		Randall		Matos
144		Peter		Vargas
145		John		Russell
146		Karen		Partners
147		Alberto		Errazuriz
148		Gerald		Cambrault
149		Eleni		Zlotkey
150		Peter		Tucker
151		David		Bernstein
152		Peter		Hall
153		Christopher	Olsen
154		Nanette		Cambrault
155		Oliver		Tuvault
156		Janette		King
157		Patrick		Sully
158		Allan		McEwen
159		Lindsey		Smith
160		Louise		Doran
161		Sarath		Sewall
162		Clara		Vishney
163		Danielle	Greene
164		Mattea		Marvins
165		David		Lee
166		Sundar		Ande
167		Amit		Banda
168		Lisa		Ozer
169		Harrison	Bloom
170		Tayler		Fox
171		William		Smith
172		Elizabeth	Bates
173		Sundita		Kumar
174		Ellen		Abel
175		Alyssa		Hutton
176		Jonathon	Taylor
177		Jack		Livingston
179		Charles		Johnson
180		Winston		Taylor
181		Jean		Fleaur
182		Martha		Sullivan
183		Girard		Geoni
184		Nandita		Sarchand
185		Alexis		Bull
186		Julia		Dellinger
187		Anthony		Cabrio
188		Kelly		Chung
189		Jennifer	Dilly
190		Timothy		Gates
191		Randall		Perkins
192		Sarah		Bell
193		Britney		Everett
194		Samuel		McCain
195		Vance		Jones
196		Alana		Walsh
197		Kevin		Feeney
198		Donald		OConnell
199		Douglas		Grant

Code Explanation:

The said query in SQL that retrieves the employee ID, first name, and last name of employees from the 'employees' table, filtered by the department ID of an employee whose first name contains the letter "T".
It retrieves all department IDs of employees with the letter "T" in their first names.
The final result set includes employees who work in the same department as employees whose first name contains the letter "T".
The WHERE clause uses the IN operator to match multiple values against a single value.
The LIKE operator in the subquery is used to match the pattern '%T%', which means any string that contains the letter "T".

Visual Presentation:

Alternative Statements:

Using ANY:

SELECT employee_id, first_name, last_name  
FROM employees  
WHERE department_id = ANY (
    SELECT department_id  
    FROM employees  
    WHERE first_name LIKE '%T%'
);

Using IN Subquery:

SELECT employee_id, first_name, last_name  
FROM employees  
WHERE department_id IN (
    SELECT DISTINCT department_id  
    FROM employees  
    WHERE first_name LIKE '%T%'
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employee names and hire dates for Claras department.
Next SQL Exercise: J-named employees earn more than average.