w3resource
SQL exercises

SQL SORTING and FILTERING on HR Database: Display informations for the employees who earn above 11000 or the seventh digit in their phone number is 3 and arranged the result descendingly by the first name

SQL SORTING and FILTERING on HR Database: Exercise-18 with Solution

18. Write a query in SQL to display the full name (first name and last name), hire date, commission percentage, email and telephone separated by '-', and salary for those employees who earn the salary above 11000 or the seventh digit in their phone number equals 3 and make the result set in a descending order by the first name.

Sample table : employees


Sample Solution:

SELECT first_name ||' '||last_name AS Full_Name, hire_date , 
  commission_pct, email ||' -  '||phone_number AS Contact_Details, salary 
     FROM employees 
       WHERE salary > 11000
        OR phone_number LIKE '______3%'
          ORDER BY first_name DESC;

Sample Output:

     full_name     | hire_date  | commission_pct |        contact_details         |  salary
-------------------+------------+----------------+--------------------------------+----------
 William Gietz     | 2002-06-07 |           0.00 | WGIETZ -  515.123.8181         |  8300.00
 Valli Pataballa   | 2006-02-05 |           0.00 | VPATABAL -  590.423.4560       |  4800.00
 Susan Mavris      | 2002-06-07 |           0.00 | SMAVRIS -  515.123.7777        |  6500.00
 Steven King       | 2003-06-17 |           0.00 | SKING -  515.123.4567          | 24000.00
 Shelley Higgins   | 2002-06-07 |           0.00 | SHIGGINS -  515.123.8080       | 12000.00
 Shanta Vollman    | 2005-10-10 |           0.00 | SVOLLMAN -  650.123.4234       |  6500.00
 Payam Kaufling    | 2003-05-01 |           0.00 | PKAUFLIN -  650.123.3234       |  7900.00
 Pat Fay           | 2005-08-17 |           0.00 | PFAY -  603.123.6666           |  6000.00
 Neena Kochhar     | 2005-09-21 |           0.00 | NKOCHHAR -  515.123.4568       | 17000.00
 Nancy Greenberg   | 2002-08-17 |           0.00 | NGREENBE -  515.124.4569       | 12000.00
 Michael Hartstein | 2004-02-17 |           0.00 | MHARTSTE -  515.123.5555       | 13000.00
 Matthew Weiss     | 2004-07-18 |           0.00 | MWEISS -  650.123.1234         |  8000.00
 Lisa Ozer         | 2005-03-11 |           0.25 | LOZER -  011.44.1343.929268    | 11500.00
 Lex De Haan       | 2001-01-13 |           0.00 | LDEHAAN -  515.123.4569        | 17000.00
 Kevin Mourgos     | 2007-11-16 |           0.00 | KMOURGOS -  650.123.5234       |  5800.00
 Karen Partners    | 2005-01-05 |           0.30 | KPARTNER -  011.44.1344.467268 | 13500.00
 John Russell      | 2004-10-01 |           0.40 | JRUSSEL -  011.44.1344.429268  | 14000.00
 Jennifer Whalen   | 2003-09-17 |           0.00 | JWHALEN -  515.123.4444        |  4400.00
 Hermann Baer      | 2002-06-07 |           0.00 | HBAER -  515.123.8888          | 10000.00
 Diana Lorentz     | 2007-02-07 |           0.00 | DLORENTZ -  590.423.5567       |  4200.00
 David Austin      | 2005-06-25 |           0.00 | DAUSTIN -  590.423.4569        |  4800.00
 Bruce Ernst       | 2007-05-21 |           0.00 | BERNST -  590.423.4568         |  6000.00
 Alexander Hunold  | 2006-01-03 |           0.00 | AHUNOLD -  590.423.4567        |  9000.00
 Alberto Errazuriz | 2005-03-10 |           0.30 | AERRAZUR -  011.44.1344.429278 | 12000.00
 Adam Fripp        | 2005-04-10 |           0.00 | AFRIPP -  650.123.2234         |  8200.00
(25 rows)

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display informations for the employees who earn above 11000 or the seventh digit in their phone number is 3 and arranged the result descendingly by the first name - Duration

Rows:

Query visualization of Display informations for the employees who earn above 11000 or the seventh digit in their phone number is 3 and arranged the result descendingly by the first name - Rows

Cost:

Query visualization of Display informations for the employees who earn above 11000 or the seventh digit in their phone number is 3 and arranged the result descendingly by the first name - Cost

Contribute your code and comments through Disqus.

Previous: Write a query in SQL to display all the information for all employees who have the letters D, S, or N in their first name and also arrange the result in descending order by salary.
Next: Write a query in SQL to display the first and last name, and department number for those employees who holds a letter s as a 3rd character in their first name.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming