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
SELECT employee_id, first_name, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%T%' );
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
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".
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%' );
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to select rows with no matching entry in another table?
SELECT t1.ID FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL
- 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
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook