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.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework