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. Write a query in SQL to display the full name (first and last), hire date, salary, and department number for those employees whose first name does not contain the letter M. The result set must be in ascending order by department number.

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: Write a query in SQL to display all the information about the department Marketing.
Next: Write a query in SQL to display all the information of employees whose salary is in the range of 8000 and 12000 and commission is not null or department number is except the number 40, 120 and 70 and they have been hired before June 5th, 1987.