Find the name of all employees who works in the IT department
MySQL Subquery: Exercise-2 with Solution
Write a MySQL query to find the name (first_name, last_name) of all employees who works in the IT department.
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 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 1987-06-27 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 1987-06-28 | FI_ACCOUNT | 7700.00 | 0.00 | 108 | 100 | | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 1987-06-29 | FI_ACCOUNT | 7800.00 | 0.00 | 108 | 100 | | 113 | Luis | Popp | LPOPP | 515.124.4567 | 1987-06-30 | FI_ACCOUNT | 6900.00 | 0.00 | 108 | 100 | | 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 1987-07-01 | PU_MAN | 11000.00 | 0.00 | 100 | 30 | | 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 1987-07-02 | PU_CLERK | 3100.00 | 0.00 | 114 | 30 | | 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 1987-07-03 | PU_CLERK | 2900.00 | 0.00 | 114 | 30 | | 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 1987-07-04 | PU_CLERK | 2800.00 | 0.00 | 114 | 30 | | 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 1987-07-05 | PU_CLERK | 2600.00 | 0.00 | 114 | 30 | | 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 1987-07-06 | PU_CLERK | 2500.00 | 0.00 | 114 | 30 | | 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 1987-07-07 | ST_MAN | 8000.00 | 0.00 | 100 | 50 | | 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 1987-07-08 | ST_MAN | 8200.00 | 0.00 | 100 | 50 | | 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 1987-07-09 | ST_MAN | 7900.00 | 0.00 | 100 | 50 | | 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 1987-07-10 | ST_MAN | 6500.00 | 0.00 | 100 | 50 | | 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 1987-07-11 | ST_MAN | 5800.00 | 0.00 | 100 | 50 | | 125 | Julia | Nayer | JNAYER | 650.124.1214 | 1987-07-12 | ST_CLERK | 3200.00 | 0.00 | 120 | 50 | | 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 1987-07-13 | ST_CLERK | 2700.00 | 0.00 | 120 | 50 | | 127 | James | Landry | JLANDRY | 650.124.1334 | 1987-07-14 | ST_CLERK | 2400.00 | 0.00 | 120 | 50 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 1987-07-15 | ST_CLERK | 2200.00 | 0.00 | 120 | 50 | | 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 1987-07-16 | ST_CLERK | 3300.00 | 0.00 | 121 | 50 | | 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 1987-07-17 | ST_CLERK | 2800.00 | 0.00 | 121 | 50 | | 131 | James | Marlow | JAMRLOW | 650.124.7234 | 1987-07-18 | ST_CLERK | 2500.00 | 0.00 | 121 | 50 | | 132 | TJ | Olson | TJOLSON | 650.124.8234 | 1987-07-19 | ST_CLERK | 2100.00 | 0.00 | 121 | 50 | | 133 | Jason | Mallin | JMALLIN | 650.127.1934 | 1987-07-20 | ST_CLERK | 3300.00 | 0.00 | 122 | 50 | | 134 | Michael | Rogers | MROGERS | 650.127.1834 | 1987-07-21 | ST_CLERK | 2900.00 | 0.00 | 122 | 50 | | 135 | Ki | Gee | KGEE | 650.127.1734 | 1987-07-22 | ST_CLERK | 2400.00 | 0.00 | 122 | 50 | | 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | 1987-07-23 | ST_CLERK | 2200.00 | 0.00 | 122 | 50 | | 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | 1987-07-24 | ST_CLERK | 3600.00 | 0.00 | 123 | 50 | | 138 | Stephen | Stiles | SSTILES | 650.121.2034 | 1987-07-25 | ST_CLERK | 3200.00 | 0.00 | 123 | 50 | | 139 | John | Seo | JSEO | 650.121.2019 | 1987-07-26 | ST_CLERK | 2700.00 | 0.00 | 123 | 50 | | 140 | Joshua | Patel | JPATEL | 650.121.1834 | 1987-07-27 | ST_CLERK | 2500.00 | 0.00 | 123 | 50 | | 141 | Trenna | Rajs | TRAJS | 650.121.8009 | 1987-07-28 | ST_CLERK | 3500.00 | 0.00 | 124 | 50 | | 142 | Curtis | Davies | CDAVIES | 650.121.2994 | 1987-07-29 | ST_CLERK | 3100.00 | 0.00 | 124 | 50 | | 143 | Randall | Matos | RMATOS | 650.121.2874 | 1987-07-30 | ST_CLERK | 2600.00 | 0.00 | 124 | 50 | | 144 | Peter | Vargas | PVARGAS | 650.121.2004 | 1987-07-31 | ST_CLERK | 2500.00 | 0.00 | 124 | 50 | | 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | 1987-08-01 | SA_MAN | 14000.00 | 0.40 | 100 | 80 | | 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | 1987-08-02 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | | 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | 1987-08-03 | SA_MAN | 12000.00 | 0.30 | 100 | 80 | | 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | 1987-08-04 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | | 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | 1987-08-05 | SA_MAN | 10500.00 | 0.20 | 100 | 80 | | 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 1987-08-06 | SA_REP | 10000.00 | 0.30 | 145 | 80 | | 151 | David | Bernstein | DBERNSTE | 011.44.1344.345268 | 1987-08-07 | SA_REP | 9500.00 | 0.25 | 145 | 80 | | 152 | Peter | Hall | PHALL | 011.44.1344.478968 | 1987-08-08 | SA_REP | 9000.00 | 0.25 | 145 | 80 | | 153 | Christopher | Olsen | COLSEN | 011.44.1344.498718 | 1987-08-09 | SA_REP | 8000.00 | 0.20 | 145 | 80 | | 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | 1987-08-10 | SA_REP | 7500.00 | 0.20 | 145 | 80 | | 155 | Oliver | Tuvault | OTUVAULT | 011.44.1344.486508 | 1987-08-11 | SA_REP | 7000.00 | 0.15 | 145 | 80 | | 156 | Janette | King | JKING | 011.44.1345.429268 | 1987-08-12 | SA_REP | 10000.00 | 0.35 | 146 | 80 | | 157 | Patrick | Sully | PSULLY | 011.44.1345.929268 | 1987-08-13 | SA_REP | 9500.00 | 0.35 | 146 | 80 | | 158 | Allan | McEwen | AMCEWEN | 011.44.1345.829268 | 1987-08-14 | SA_REP | 9000.00 | 0.35 | 146 | 80 | | 159 | Lindsey | Smith | LSMITH | 011.44.1345.729268 | 1987-08-15 | SA_REP | 8000.00 | 0.30 | 146 | 80 | | 160 | Louise | Doran | LDORAN | 011.44.1345.629268 | 1987-08-16 | SA_REP | 7500.00 | 0.30 | 146 | 80 | | 161 | Sarath | Sewall | SSEWALL | 011.44.1345.529268 | 1987-08-17 | SA_REP | 7000.00 | 0.25 | 146 | 80 | | 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 1987-08-18 | SA_REP | 10500.00 | 0.25 | 147 | 80 | | 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 1987-08-19 | SA_REP | 9500.00 | 0.15 | 147 | 80 | | 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 1987-08-20 | SA_REP | 7200.00 | 0.10 | 147 | 80 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 1987-08-21 | SA_REP | 6800.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 1987-08-22 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 1987-08-23 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 1987-08-24 | SA_REP | 11500.00 | 0.25 | 148 | 80 | | 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 1987-08-25 | SA_REP | 10000.00 | 0.20 | 148 | 80 | | 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 1987-08-26 | SA_REP | 9600.00 | 0.20 | 148 | 80 | | 171 | William | Smith | WSMITH | 011.44.1343.629268 | 1987-08-27 | SA_REP | 7400.00 | 0.15 | 148 | 80 | | 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 1987-08-28 | SA_REP | 7300.00 | 0.15 | 148 | 80 | | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 1987-08-29 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 1987-08-30 | SA_REP | 11000.00 | 0.30 | 149 | 80 | | 175 | Alyssa | Hutton | AHUTTON | 011.44.1644.429266 | 1987-08-31 | SA_REP | 8800.00 | 0.25 | 149 | 80 | | 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | 1987-09-01 | SA_REP | 8600.00 | 0.20 | 149 | 80 | | 177 | Jack | Livingston | JLIVINGS | 011.44.1644.429264 | 1987-09-02 | SA_REP | 8400.00 | 0.20 | 149 | 80 | | 178 | Kimberely | Grant | KGRANT | 011.44.1644.429263 | 1987-09-03 | SA_REP | 7000.00 | 0.15 | 149 | 0 | | 179 | Charles | Johnson | CJOHNSON | 011.44.1644.429262 | 1987-09-04 | SA_REP | 6200.00 | 0.10 | 149 | 80 | | 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | 1987-09-05 | SH_CLERK | 3200.00 | 0.00 | 120 | 50 | | 181 | Jean | Fleaur | JFLEAUR | 650.507.9877 | 1987-09-06 | SH_CLERK | 3100.00 | 0.00 | 120 | 50 | | 182 | Martha | Sullivan | MSULLIVA | 650.507.9878 | 1987-09-07 | SH_CLERK | 2500.00 | 0.00 | 120 | 50 | | 183 | Girard | Geoni | GGEONI | 650.507.9879 | 1987-09-08 | SH_CLERK | 2800.00 | 0.00 | 120 | 50 | | 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | 1987-09-09 | SH_CLERK | 4200.00 | 0.00 | 121 | 50 | | 185 | Alexis | Bull | ABULL | 650.509.2876 | 1987-09-10 | SH_CLERK | 4100.00 | 0.00 | 121 | 50 | | 186 | Julia | Dellinger | JDELLING | 650.509.3876 | 1987-09-11 | SH_CLERK | 3400.00 | 0.00 | 121 | 50 | | 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | 1987-09-12 | SH_CLERK | 3000.00 | 0.00 | 121 | 50 | | 188 | Kelly | Chung | KCHUNG | 650.505.1876 | 1987-09-13 | SH_CLERK | 3800.00 | 0.00 | 122 | 50 | | 189 | Jennifer | Dilly | JDILLY | 650.505.2876 | 1987-09-14 | SH_CLERK | 3600.00 | 0.00 | 122 | 50 | | 190 | Timothy | Gates | TGATES | 650.505.3876 | 1987-09-15 | SH_CLERK | 2900.00 | 0.00 | 122 | 50 | | 191 | Randall | Perkins | RPERKINS | 650.505.4876 | 1987-09-16 | SH_CLERK | 2500.00 | 0.00 | 122 | 50 | | 192 | Sarah | Bell | SBELL | 650.501.1876 | 1987-09-17 | SH_CLERK | 4000.00 | 0.00 | 123 | 50 | | 193 | Britney | Everett | BEVERETT | 650.501.2876 | 1987-09-18 | SH_CLERK | 3900.00 | 0.00 | 123 | 50 | | 194 | Samuel | McCain | SMCCAIN | 650.501.3876 | 1987-09-19 | SH_CLERK | 3200.00 | 0.00 | 123 | 50 | | 195 | Vance | Jones | VJONES | 650.501.4876 | 1987-09-20 | SH_CLERK | 2800.00 | 0.00 | 123 | 50 | | 196 | Alana | Walsh | AWALSH | 650.507.9811 | 1987-09-21 | SH_CLERK | 3100.00 | 0.00 | 124 | 50 | | 197 | Kevin | Feeney | KFEENEY | 650.507.9822 | 1987-09-22 | SH_CLERK | 3000.00 | 0.00 | 124 | 50 | | 198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 1987-09-23 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 199 | Douglas | Grant | DGRANT | 650.507.9844 | 1987-09-24 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | 1987-09-25 | AD_ASST | 4400.00 | 0.00 | 101 | 10 | | 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | 1987-09-26 | MK_MAN | 13000.00 | 0.00 | 100 | 20 | | 202 | Pat | Fay | PFAY | 603.123.6666 | 1987-09-27 | MK_REP | 6000.00 | 0.00 | 201 | 20 | | 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 1987-09-28 | HR_REP | 6500.00 | 0.00 | 101 | 40 | | 204 | Hermann | Baer | HBAER | 515.123.8888 | 1987-09-29 | PR_REP | 10000.00 | 0.00 | 101 | 70 | | 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 1987-09-30 | AC_MGR | 12000.00 | 0.00 | 101 | 110 | | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table: departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | | 120 | Treasury | 0 | 1700 | | 130 | Corporate Tax | 0 | 1700 | | 140 | Control And Credit | 0 | 1700 | | 150 | Shareholder Services | 0 | 1700 | | 160 | Benefits | 0 | 1700 | | 170 | Manufacturing | 0 | 1700 | | 180 | Construction | 0 | 1700 | | 190 | Contracting | 0 | 1700 | | 200 | Operations | 0 | 1700 | | 210 | IT Support | 0 | 1700 | | 220 | NOC | 0 | 1700 | | 230 | IT Helpdesk | 0 | 1700 | | 240 | Government Sales | 0 | 1700 | | 250 | Retail Sales | 0 | 1700 | | 260 | Recruiting | 0 | 1700 | | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Code:
-- Selecting the first name and last name of employees
SELECT first_name, last_name
-- Selecting data from the employees table
FROM employees
-- Filtering the result set to include only employees whose department_id is in the set of department_ids where the department_name is 'IT'
WHERE department_id
IN (SELECT department_id FROM departments WHERE department_name='IT');
Explanation :
- This MySQL code selects the first name and last name of employees from a table named "employees".
- It filters the results to only include employees whose department_id is in the set of department_ids associated with the department named 'IT'.
- This is achieved by using a subquery to select the department_id from the "departments" table where the department_name is 'IT', and then comparing it with the department_ids of employees in the outer query.
MySQL Subquery Syntax:
operand comparison_operator operand IN (subquery) operand comparison_operator SOME (subquery)
Where comparison_operator is one of these operators
= > < >= <= <> !=
and IN operator checks whether a value is within a set of values.
For example :
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot.
MySQL Code Editor:
Structure of 'hr' database :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous:Write a MySQL query to find the name (first_name, last_name) and the salary of the employees who have a higher salary than the employee whose last_name='Bull'.
Next:Write a MySQL query to find the name (first_name, last_name) of the employees who have a manager and worked in a USA based department.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/mysql-exercises/subquery-exercises/find-the-names-of-all-employees-who-works-in-the-it-department.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics