w3resource

SQL SORTING and FILTERING on HR Database: Display information for employees who earn above 11000 or the seventh character 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. From the following table, write a SQL query to find those employees who earn above 11000 or the seventh character in their phone number is 3. Sort the result-set in descending order by first name. Return full name (first name and last name), hire date, commission percentage, email, and telephone separated by '-', and salary.

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 information for employees who earn above 11000 or the seventh character in their phone number is 3 and arranged the result descendingly by the first name - Duration

Rows:

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

Cost:

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

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees whose first name contains the letters D, S, or N. Sort the result-set in descending order by salary. Return all fields.
Next: From the following table, write a SQL query to find those employees whose first name contains a character 's' in 3rd position. Return first name, last name and department id.

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