SQL Exercises: Employees in a department beginning with T
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
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | .................... +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample Solution:
-- Selecting specific columns (employee_id, first_name, last_name) from the 'employees' table
SELECT employee_id, first_name, last_name
-- Filtering rows based on the condition that the 'department_id' is in the result set of a subquery
FROM employees
-- Subquery to find 'department_id' values where the first name contains the letter 'T'
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.