w3resource

SQL Exercise: Employees have the given letters in their first name

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

17. From the following table, write a SQL query to find employees whose first names contain the letters D, S, or N. Sort the result-set in descending order by salary. Return all fields.

Sample table : employees


Sample Solution: 

SELECT *
 FROM employees
  WHERE first_name LIKE '%D%'
   OR first_name LIKE '%S%'
   OR first_name LIKE '%N%'
    ORDER BY salary DESC;

Sample Output:

 employee_id | first_name | last_name |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         100 | Steven     | King      | SKING    | 515.123.4567       | 2003-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90
         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568       | 2005-09-21 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         205 | Shelley    | Higgins   | SHIGGINS | 515.123.8080       | 2002-06-07 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         108 | Nancy      | Greenberg | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12000.00 |           0.00 |        101 |           100
         114 | Den        | Raphaely  | DRAPHEAL | 515.127.4561       | 2002-12-07 | PU_MAN     | 11000.00 |           0.00 |        100 |            30
         151 | David      | Bernstein | DBERNSTE | 011.44.1344.345268 | 2005-03-24 | SA_REP     |  9500.00 |           0.25 |        145 |            80
         163 | Danielle   | Greene    | DGREENE  | 011.44.1346.229268 | 2007-03-19 | SA_REP     |  9500.00 |           0.15 |        147 |            80
         109 | Daniel     | Faviet    | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100
         154 | Nanette    | Cambrault | NCAMBRAU | 011.44.1344.987668 | 2006-12-09 | SA_REP     |  7500.00 |           0.20 |        145 |            80
         161 | Sarath     | Sewall    | SSEWALL  | 011.44.1345.529268 | 2006-11-03 | SA_REP     |  7000.00 |           0.25 |        146 |            80
         165 | David      | Lee       | DLEE     | 011.44.1346.529268 | 2008-02-23 | SA_REP     |  6800.00 |           0.10 |        147 |            80
         123 | Shanta     | Vollman   | SVOLLMAN | 650.123.4234       | 2005-10-10 | ST_MAN     |  6500.00 |           0.00 |        100 |            50
         203 | Susan      | Mavris    | SMAVRIS  | 515.123.7777       | 2002-06-07 | HR_REP     |  6500.00 |           0.00 |        101 |            40
         166 | Sundar     | Ande      | SANDE    | 011.44.1346.629268 | 2008-03-24 | SA_REP     |  6400.00 |           0.10 |        147 |            80
         173 | Sundita    | Kumar     | SKUMAR   | 011.44.1343.329268 | 2008-04-21 | SA_REP     |  6100.00 |           0.10 |        148 |            80
         105 | David      | Austin    | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60
         184 | Nandita    | Sarchand  | NSARCHAN | 650.509.1876       | 2004-01-27 | SH_CLERK   |  4200.00 |           0.00 |        121 |            50
         107 | Diana      | Lorentz   | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60
         192 | Sarah      | Bell      | SBELL    | 650.501.1876       | 2004-02-04 | SH_CLERK   |  4000.00 |           0.00 |        123 |            50
         194 | Samuel     | McCain    | SMCCAIN  | 650.501.3876       | 2006-07-01 | SH_CLERK   |  3200.00 |           0.00 |        123 |            50
         138 | Stephen    | Stiles    | SSTILES  | 650.121.2034       | 2005-10-26 | ST_CLERK   |  3200.00 |           0.00 |        123 |            50
         116 | Shelli     | Baida     | SBAIDA   | 515.127.4563       | 2005-12-24 | PU_CLERK   |  2900.00 |           0.00 |        114 |            30
         117 | Sigal      | Tobias    | STOBIAS  | 515.127.4564       | 2005-07-24 | PU_CLERK   |  2800.00 |           0.00 |        114 |            30
         198 | Donald     | OConnell  | DOCONNEL | 650.507.9833       | 2007-06-21 | SH_CLERK   |  2600.00 |           0.00 |        124 |            50
         199 | Douglas    | Grant     | DGRANT   | 650.507.9844       | 2008-01-13 | SH_CLERK   |  2600.00 |           0.00 |        124 |            50
         128 | Steven     | Markle    | SMARKLE  | 650.124.1434       | 2008-03-08 | ST_CLERK   |  2200.00 |           0.00 |        120 |            50
(26 rows)

Code Explanation:

The said query in SQL that retrieves all columns (*) from the 'employees' table where the value in the "first_name" column contains either "D", "S", or "N". The result is sorted in descending order based on the values in the "salary" column. There will be a set of rows in the "employees" table whose first names match the pattern specified, sorted from the highest to the lowest salary, that will appear in the employee table.

Relational Algebra Expression:

Relational Algebra Expression: Display all the information of all employees who have the letters D, S, or N in their first name and arrange the result in descending order by salary.

Relational Algebra Tree:

Relational Algebra Tree: Display all the information of all employees who have the letters D, S, or N in their first name and arrange the result in descending order by salary.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display all the information of all employees who have the letters D, S, or N in their first name and arrange the result in descending order by salary - Duration

Rows:

Query visualization of Display all the information of all employees who have the letters D, S, or N in their first name and arrange the result in descending order by salary - Rows

Cost:

Query visualization of Display all the information of all employees who have the letters D, S, or N in their first name and arrange the result in descending order by salary - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find employees whose managers are hold given IDs.
Next SQL Exercise: Employees earn over 11000 or 7th digit in their phone.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook