SQL Exercise: Employees first name does not contain the letter M
SQL SORTING and FILTERING on HR Database: Exercise-6 with Solution
6. From the following table, write a SQL query to find those employees whose first name does not contain the letter ‘M’. Sort the result-set in ascending order by department ID. Return full name (first and last name together), hire_date, salary and department_id.
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 | | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 2005-09-30 | FI_ACCOUNT | 7700.00 | 0.00 | 108 | 100 | | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 2006-03-07 | FI_ACCOUNT | 7800.00 | 0.00 | 108 | 100 | | 113 | Luis | Popp | LPOPP | 515.124.4567 | 2007-12-07 | FI_ACCOUNT | 6900.00 | 0.00 | 108 | 100 | | 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 2002-12-07 | PU_MAN | 11000.00 | 0.00 | 100 | 30 | | 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 2003-05-18 | PU_CLERK | 3100.00 | 0.00 | 114 | 30 | | 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 2005-12-24 | PU_CLERK | 2900.00 | 0.00 | 114 | 30 | | 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 2005-07-24 | PU_CLERK | 2800.00 | 0.00 | 114 | 30 | | 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 2006-11-15 | PU_CLERK | 2600.00 | 0.00 | 114 | 30 | | 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 2007-08-10 | PU_CLERK | 2500.00 | 0.00 | 114 | 30 | | 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 2004-07-18 | ST_MAN | 8000.00 | 0.00 | 100 | 50 | | 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 2005-04-10 | ST_MAN | 8200.00 | 0.00 | 100 | 50 | | 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 2003-05-01 | ST_MAN | 7900.00 | 0.00 | 100 | 50 | | 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 2005-10-10 | ST_MAN | 6500.00 | 0.00 | 100 | 50 | | 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 2007-11-16 | ST_MAN | 5800.00 | 0.00 | 100 | 50 | | 125 | Julia | Nayer | JNAYER | 650.124.1214 | 2005-07-16 | ST_CLERK | 3200.00 | 0.00 | 120 | 50 | | 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 2006-09-28 | ST_CLERK | 2700.00 | 0.00 | 120 | 50 | | 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.00 | 0.00 | 120 | 50 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2008-03-08 | ST_CLERK | 2200.00 | 0.00 | 120 | 50 | | 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 2005-08-20 | ST_CLERK | 3300.00 | 0.00 | 121 | 50 | | 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 2005-10-30 | ST_CLERK | 2800.00 | 0.00 | 121 | 50 | | 131 | James | Marlow | JAMRLOW | 650.124.7234 | 2005-02-16 | ST_CLERK | 2500.00 | 0.00 | 121 | 50 | | 132 | TJ | Olson | TJOLSON | 650.124.8234 | 2007-04-10 | ST_CLERK | 2100.00 | 0.00 | 121 | 50 | | 133 | Jason | Mallin | JMALLIN | 650.127.1934 | 2004-06-14 | ST_CLERK | 3300.00 | 0.00 | 122 | 50 | | 134 | Michael | Rogers | MROGERS | 650.127.1834 | 2006-08-26 | ST_CLERK | 2900.00 | 0.00 | 122 | 50 | | 135 | Ki | Gee | KGEE | 650.127.1734 | 2007-12-12 | ST_CLERK | 2400.00 | 0.00 | 122 | 50 | | 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | 2008-02-06 | ST_CLERK | 2200.00 | 0.00 | 122 | 50 | | 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | 2003-07-14 | ST_CLERK | 3600.00 | 0.00 | 123 | 50 | | 138 | Stephen | Stiles | SSTILES | 650.121.2034 | 2005-10-26 | ST_CLERK | 3200.00 | 0.00 | 123 | 50 | | 139 | John | Seo | JSEO | 650.121.2019 | 2006-02-12 | ST_CLERK | 2700.00 | 0.00 | 123 | 50 | | 140 | Joshua | Patel | JPATEL | 650.121.1834 | 2006-04-06 | ST_CLERK | 2500.00 | 0.00 | 123 | 50 | | 141 | Trenna | Rajs | TRAJS | 650.121.8009 | 2003-10-17 | ST_CLERK | 3500.00 | 0.00 | 124 | 50 | | 142 | Curtis | Davies | CDAVIES | 650.121.2994 | 2005-01-29 | ST_CLERK | 3100.00 | 0.00 | 124 | 50 | | 143 | Randall | Matos | RMATOS | 650.121.2874 | 2006-03-15 | ST_CLERK | 2600.00 | 0.00 | 124 | 50 | | 144 | Peter | Vargas | PVARGAS | 650.121.2004 | 2006-07-09 | ST_CLERK | 2500.00 | 0.00 | 124 | 50 | | 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | 2004-10-01 | SA_MAN | 14000.00 | 0.40 | 100 | 80 | | 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | 2005-01-05 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | | 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | 2005-03-10 | SA_MAN | 12000.00 | 0.30 | 100 | 80 | | 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | 2007-10-15 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | | 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | 2008-01-29 | SA_MAN | 10500.00 | 0.20 | 100 | 80 | | 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 2005-01-30 | SA_REP | 10000.00 | 0.30 | 145 | 80 | | 151 | David | Bernstein | DBERNSTE | 011.44.1344.345268 | 2005-03-24 | SA_REP | 9500.00 | 0.25 | 145 | 80 | | 152 | Peter | Hall | PHALL | 011.44.1344.478968 | 2005-08-20 | SA_REP | 9000.00 | 0.25 | 145 | 80 | | 153 | Christopher | Olsen | COLSEN | 011.44.1344.498718 | 2006-03-30 | SA_REP | 8000.00 | 0.20 | 145 | 80 | | 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | 2006-12-09 | SA_REP | 7500.00 | 0.20 | 145 | 80 | | 155 | Oliver | Tuvault | OTUVAULT | 011.44.1344.486508 | 2007-11-23 | SA_REP | 7000.00 | 0.15 | 145 | 80 | | 156 | Janette | King | JKING | 011.44.1345.429268 | 2004-01-30 | SA_REP | 10000.00 | 0.35 | 146 | 80 | | 157 | Patrick | Sully | PSULLY | 011.44.1345.929268 | 2004-03-04 | SA_REP | 9500.00 | 0.35 | 146 | 80 | | 158 | Allan | McEwen | AMCEWEN | 011.44.1345.829268 | 2004-08-01 | SA_REP | 9000.00 | 0.35 | 146 | 80 | | 159 | Lindsey | Smith | LSMITH | 011.44.1345.729268 | 2005-03-10 | SA_REP | 8000.00 | 0.30 | 146 | 80 | | 160 | Louise | Doran | LDORAN | 011.44.1345.629268 | 2005-12-15 | SA_REP | 7500.00 | 0.30 | 146 | 80 | | 161 | Sarath | Sewall | SSEWALL | 011.44.1345.529268 | 2006-11-03 | SA_REP | 7000.00 | 0.25 | 146 | 80 | | 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 2005-11-11 | SA_REP | 10500.00 | 0.25 | 147 | 80 | | 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 2007-03-19 | SA_REP | 9500.00 | 0.15 | 147 | 80 | | 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2008-01-24 | SA_REP | 7200.00 | 0.10 | 147 | 80 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2008-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2008-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2008-04-21 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 2005-03-11 | SA_REP | 11500.00 | 0.25 | 148 | 80 | | 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 2006-03-23 | SA_REP | 10000.00 | 0.20 | 148 | 80 | | 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 2006-01-24 | SA_REP | 9600.00 | 0.20 | 148 | 80 | | 171 | William | Smith | WSMITH | 011.44.1343.629268 | 2007-02-23 | SA_REP | 7400.00 | 0.15 | 148 | 80 | | 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 2007-03-24 | SA_REP | 7300.00 | 0.15 | 148 | 80 | | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2008-04-21 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 2004-05-11 | SA_REP | 11000.00 | 0.30 | 149 | 80 | | 175 | Alyssa | Hutton | AHUTTON | 011.44.1644.429266 | 2005-03-19 | SA_REP | 8800.00 | 0.25 | 149 | 80 | | 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | 2006-03-24 | SA_REP | 8600.00 | 0.20 | 149 | 80 | | 177 | Jack | Livingston | JLIVINGS | 011.44.1644.429264 | 2006-04-23 | SA_REP | 8400.00 | 0.20 | 149 | 80 | | 178 | Kimberely | Grant | KGRANT | 011.44.1644.429263 | 2007-05-24 | SA_REP | 7000.00 | 0.15 | 149 | 0 | | 179 | Charles | Johnson | CJOHNSON | 011.44.1644.429262 | 2008-01-04 | SA_REP | 6200.00 | 0.10 | 149 | 80 | | 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | 2006-01-24 | SH_CLERK | 3200.00 | 0.00 | 120 | 50 | | 181 | Jean | Fleaur | JFLEAUR | 650.507.9877 | 2006-02-23 | SH_CLERK | 3100.00 | 0.00 | 120 | 50 | | 182 | Martha | Sullivan | MSULLIVA | 650.507.9878 | 2007-06-21 | SH_CLERK | 2500.00 | 0.00 | 120 | 50 | | 183 | Girard | Geoni | GGEONI | 650.507.9879 | 2008-02-03 | SH_CLERK | 2800.00 | 0.00 | 120 | 50 | | 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | 2004-01-27 | SH_CLERK | 4200.00 | 0.00 | 121 | 50 | | 185 | Alexis | Bull | ABULL | 650.509.2876 | 2005-02-20 | SH_CLERK | 4100.00 | 0.00 | 121 | 50 | | 186 | Julia | Dellinger | JDELLING | 650.509.3876 | 2006-06-24 | SH_CLERK | 3400.00 | 0.00 | 121 | 50 | | 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | 2007-02-07 | SH_CLERK | 3000.00 | 0.00 | 121 | 50 | | 188 | Kelly | Chung | KCHUNG | 650.505.1876 | 2005-06-14 | SH_CLERK | 3800.00 | 0.00 | 122 | 50 | | 189 | Jennifer | Dilly | JDILLY | 650.505.2876 | 2005-08-13 | SH_CLERK | 3600.00 | 0.00 | 122 | 50 | | 190 | Timothy | Gates | TGATES | 650.505.3876 | 2006-07-11 | SH_CLERK | 2900.00 | 0.00 | 122 | 50 | | 191 | Randall | Perkins | RPERKINS | 650.505.4876 | 2007-12-19 | SH_CLERK | 2500.00 | 0.00 | 122 | 50 | | 192 | Sarah | Bell | SBELL | 650.501.1876 | 2004-02-04 | SH_CLERK | 4000.00 | 0.00 | 123 | 50 | | 193 | Britney | Everett | BEVERETT | 650.501.2876 | 2005-03-03 | SH_CLERK | 3900.00 | 0.00 | 123 | 50 | | 194 | Samuel | McCain | SMCCAIN | 650.501.3876 | 2006-07-01 | SH_CLERK | 3200.00 | 0.00 | 123 | 50 | | 195 | Vance | Jones | VJONES | 650.501.4876 | 2007-03-17 | SH_CLERK | 2800.00 | 0.00 | 123 | 50 | | 196 | Alana | Walsh | AWALSH | 650.507.9811 | 2006-04-24 | SH_CLERK | 3100.00 | 0.00 | 124 | 50 | | 197 | Kevin | Feeney | KFEENEY | 650.507.9822 | 2006-05-23 | SH_CLERK | 3000.00 | 0.00 | 124 | 50 | | 198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 2007-06-21 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 199 | Douglas | Grant | DGRANT | 650.507.9844 | 2008-01-13 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | 2003-09-17 | AD_ASST | 4400.00 | 0.00 | 101 | 10 | | 201 | Michael | Hartstein | MHARTSTE | 515.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 | SHIGGINS | 515.123.8080 | 2002-06-07 | AC_MGR | 12008.00 | 0.00 | 101 | 110 | | 206 | William | Gietz | WGIETZ | 515.123.8181 | 2002-06-07 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample Solution:
-- Selecting a concatenated string of 'first_name' and 'last_name' as 'Full_Name', along with 'hire_date', 'salary', and 'department_id' columns from the 'employees' table
SELECT first_name || ' ' || last_name as Full_Name, hire_date, salary, department_id
-- Specifying the table to retrieve data from ('employees')
FROM employees
-- Filtering the results based on the condition that 'first_name' does not contain the letter 'M' (case-insensitive)
WHERE first_name NOT LIKE '%M%'
-- Sorting the results in ascending order based on the 'department_id' column
ORDER BY department_id;
Sample Output:
full_name | hire_date | salary | department_id
-------------------+------------+----------+---------------
Kimberely Grant | 2007-05-24 | 7000.00 | 0
Jennifer Whalen | 2003-09-17 | 4400.00 | 10
Pat Fay | 2005-08-17 | 6000.00 | 20
Guy Himuro | 2006-11-15 | 2600.00 | 30
Alexander Khoo | 2003-05-18 | 3100.00 | 30
Den Raphaely | 2002-12-07 | 11000.00 | 30
Shelli Baida | 2005-12-24 | 2900.00 | 30
Karen Colmenares | 2007-08-10 | 2500.00 | 30
Sigal Tobias | 2005-07-24 | 2800.00 | 30
Susan Mavris | 2002-06-07 | 6500.00 | 40
Laura Bissot | 2005-08-20 | 3300.00 | 50
James Marlow | 2005-02-16 | 2500.00 | 50
TJ Olson | 2007-04-10 | 2100.00 | 50
Jason Mallin | 2004-06-14 | 3300.00 | 50
Ki Gee | 2007-12-12 | 2400.00 | 50
Hazel Philtanker | 2008-02-06 | 2200.00 | 50
Winston Taylor | 2006-01-24 | 3200.00 | 50
Jean Fleaur | 2006-02-23 | 3100.00 | 50
Girard Geoni | 2008-02-03 | 2800.00 | 50
Nandita Sarchand | 2004-01-27 | 4200.00 | 50
Alexis Bull | 2005-02-20 | 4100.00 | 50
Julia Dellinger | 2006-06-24 | 3400.00 | 50
Anthony Cabrio | 2007-02-07 | 3000.00 | 50
Kelly Chung | 2005-06-14 | 3800.00 | 50
Jennifer Dilly | 2005-08-13 | 3600.00 | 50
Timothy Gates | 2006-07-11 | 2900.00 | 50
Randall Perkins | 2007-12-19 | 2500.00 | 50
Sarah Bell | 2004-02-04 | 4000.00 | 50
Britney Everett | 2005-03-03 | 3900.00 | 50
Samuel McCain | 2006-07-01 | 3200.00 | 50
Vance Jones | 2007-03-17 | 2800.00 | 50
Renske Ladwig | 2003-07-14 | 3600.00 | 50
Stephen Stiles | 2005-10-26 | 3200.00 | 50
Joshua Patel | 2006-04-06 | 2500.00 | 50
Trenna Rajs | 2003-10-17 | 3500.00 | 50
Curtis Davies | 2005-01-29 | 3100.00 | 50
Randall Matos | 2006-03-15 | 2600.00 | 50
Peter Vargas | 2006-07-09 | 2500.00 | 50
John Seo | 2006-02-12 | 2700.00 | 50
Adam Fripp | 2005-04-10 | 8200.00 | 50
Payam Kaufling | 2003-05-01 | 7900.00 | 50
Shanta Vollman | 2005-10-10 | 6500.00 | 50
Alana Walsh | 2006-04-24 | 3100.00 | 50
Kevin Mourgos | 2007-11-16 | 5800.00 | 50
Julia Nayer | 2005-07-16 | 3200.00 | 50
Irene Mikkilineni | 2006-09-28 | 2700.00 | 50
James Landry | 2007-01-14 | 2400.00 | 50
Steven Markle | 2008-03-08 | 2200.00 | 50
Douglas Grant | 2008-01-13 | 2600.00 | 50
Donald OConnell | 2007-06-21 | 2600.00 | 50
Kevin Feeney | 2006-05-23 | 3000.00 | 50
Alexander Hunold | 2006-01-03 | 9000.00 | 60
David Austin | 2005-06-25 | 4800.00 | 60
Diana Lorentz | 2007-02-07 | 4200.00 | 60
Valli Pataballa | 2006-02-05 | 4800.00 | 60
Bruce Ernst | 2007-05-21 | 6000.00 | 60
Hermann Baer | 2002-06-07 | 10000.00 | 70
Amit Banda | 2008-04-21 | 6200.00 | 80
John Russell | 2004-10-01 | 14000.00 | 80
Karen Partners | 2005-01-05 | 13500.00 | 80
Alberto Errazuriz | 2005-03-10 | 12000.00 | 80
Gerald Cambrault | 2007-10-15 | 11000.00 | 80
Eleni Zlotkey | 2008-01-29 | 10500.00 | 80
Peter Tucker | 2005-01-30 | 10000.00 | 80
David Bernstein | 2005-03-24 | 9500.00 | 80
Peter Hall | 2005-08-20 | 9000.00 | 80
Christopher Olsen | 2006-03-30 | 8000.00 | 80
Nanette Cambrault | 2006-12-09 | 7500.00 | 80
Oliver Tuvault | 2007-11-23 | 7000.00 | 80
Janette King | 2004-01-30 | 10000.00 | 80
Patrick Sully | 2004-03-04 | 9500.00 | 80
Allan McEwen | 2004-08-01 | 9000.00 | 80
Lindsey Smith | 2005-03-10 | 8000.00 | 80
Louise Doran | 2005-12-15 | 7500.00 | 80
Sarath Sewall | 2006-11-03 | 7000.00 | 80
Clara Vishney | 2005-11-11 | 10500.00 | 80
Danielle Greene | 2007-03-19 | 9500.00 | 80
David Lee | 2008-02-23 | 6800.00 | 80
Sundar Ande | 2008-03-24 | 6400.00 | 80
Lisa Ozer | 2005-03-11 | 11500.00 | 80
Harrison Bloom | 2006-03-23 | 10000.00 | 80
Tayler Fox | 2006-01-24 | 9600.00 | 80
William Smith | 2007-02-23 | 7400.00 | 80
Elizabeth Bates | 2007-03-24 | 7300.00 | 80
Sundita Kumar | 2008-04-21 | 6100.00 | 80
Ellen Abel | 2004-05-11 | 11000.00 | 80
Alyssa Hutton | 2005-03-19 | 8800.00 | 80
Jonathon Taylor | 2006-03-24 | 8600.00 | 80
Jack Livingston | 2006-04-23 | 8400.00 | 80
Charles Johnson | 2008-01-04 | 6200.00 | 80
Steven King | 2003-06-17 | 24000.00 | 90
Lex De Haan | 2001-01-13 | 17000.00 | 90
Neena Kochhar | 2005-09-21 | 17000.00 | 90
John Chen | 2005-09-28 | 8200.00 | 100
Daniel Faviet | 2002-08-16 | 9000.00 | 100
Nancy Greenberg | 2002-08-17 | 12000.00 | 100
Luis Popp | 2007-12-07 | 6900.00 | 100
Ismael Sciarra | 2005-09-30 | 7700.00 | 100
Shelley Higgins | 2002-06-07 | 12000.00 | 110
William Gietz | 2002-06-07 | 8300.00 | 110
(100 rows)
Code Explanation:
The said query in SQL that retrieves first name, last name (concatenated as "Full_Name"), hire date, salary, and department ID columns from the 'employees' table where the first name does not contain the letter 'M'. There is a clause called "NOT LIKE" that can be used to exclude rows that meet a particular condition in the table. The query sorts the resulting rows by department ID in ascending order. The "ORDER BY" clause sorts the rows based on the specified column.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous SQL Exercise: Information about the department Marketing.
Next SQL Exercise: Employees with salaries of given range and commission.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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/sql-exercises/sorting-and-filtering-hr/sql-sorting-and-filtering-hr-exercise-6.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics