SQL SORTING and FILTERING on HR Database: Display some information for employees whose first name does not containing the letter M and make the result set in ascending order by department number

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

Sample Solution:

SELECT first_name || ' ' || last_name as Full_Name, hire_date,  
 salary,  department_id
  FROM employees
   WHERE first_name NOT LIKE '%M%'
     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)

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find the details of 'Marketing' department. Return all fields.
Next: From the following table, write a SQL query to find those employees whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. These employees have joined before ‘1987-06-05’ and not included in the department number 40, 120 and 70. Return all fields.