w3resource

SQL Exercise: Find employees whose salary is within 9000 to 17000

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

9. From the following table, write a SQL query to find the employees whose salary is in the range 9000,17000 (Begin and end values are included). Return full name, contact details and salary.

Sample table : employees


Sample Solution: 

SELECT first_name ||' '||last_name AS Full_Name,
       phone_number ||' - '|| email AS Contact_Details,
       salary  AS Remuneration
         FROM employees
           WHERE salary BETWEEN 9000 AND 17000;

Sample Output:

     full_name     |        contact_details        | remuneration
-------------------+-------------------------------+--------------
 Neena Kochhar     | 515.123.4568 - NKOCHHAR       |     17000.00
 Lex De Haan       | 515.123.4569 - LDEHAAN        |     17000.00
 Alexander Hunold  | 590.423.4567 - AHUNOLD        |      9000.00
 Nancy Greenberg   | 515.124.4569 - NGREENBE       |     12000.00
 Daniel Faviet     | 515.124.4169 - DFAVIET        |      9000.00
 Den Raphaely      | 515.127.4561 - DRAPHEAL       |     11000.00
 John Russell      | 011.44.1344.429268 - JRUSSEL  |     14000.00
 Karen Partners    | 011.44.1344.467268 - KPARTNER |     13500.00
 Alberto Errazuriz | 011.44.1344.429278 - AERRAZUR |     12000.00
 Gerald Cambrault  | 011.44.1344.619268 - GCAMBRAU |     11000.00
 Eleni Zlotkey     | 011.44.1344.429018 - EZLOTKEY |     10500.00
 Peter Tucker      | 011.44.1344.129268 - PTUCKER  |     10000.00
 David Bernstein   | 011.44.1344.345268 - DBERNSTE |      9500.00
 Peter Hall        | 011.44.1344.478968 - PHALL    |      9000.00
 Janette King      | 011.44.1345.429268 - JKING    |     10000.00
 Patrick Sully     | 011.44.1345.929268 - PSULLY   |      9500.00
 Allan McEwen      | 011.44.1345.829268 - AMCEWEN  |      9000.00
 Clara Vishney     | 011.44.1346.129268 - CVISHNEY |     10500.00
 Danielle Greene   | 011.44.1346.229268 - DGREENE  |      9500.00
 Lisa Ozer         | 011.44.1343.929268 - LOZER    |     11500.00
 Harrison Bloom    | 011.44.1343.829268 - HBLOOM   |     10000.00
 Tayler Fox        | 011.44.1343.729268 - TFOX     |      9600.00
 Ellen Abel        | 011.44.1644.429267 - EABEL    |     11000.00
 Michael Hartstein | 515.123.5555 - MHARTSTE       |     13000.00
 Hermann Baer      | 515.123.8888 - HBAER          |     10000.00
 Shelley Higgins   | 515.123.8080 - SHIGGINS       |     12000.00
(26 rows)

Code Explanation:

The said query in SQL that retrieves the first name, last name concatenated as "Full_Name", phone number, and email concatenated as "Contact_Details", and salary as "Remuneration" from the 'employees' table where the salary is between 9000 and 17000.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Write a query in SQL to display the full name, phone number with email jointly, and salary, for employees whose salary is within 9000 and 17000 - Duration

Rows:

Query visualization of Write a query in SQL to display the full name, phone number with email jointly, and salary, for employees whose salary is within 9000 and 17000 - Rows

Cost:

Query visualization of Write a query in SQL to display the full name, phone number with email jointly, and salary, for employees whose salary is within 9000 and 17000 - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Employees who does not earn any commission.
Next SQL Exercise: Employees whose first name is ending with the letter m.

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