w3resource

SQL Exercise: Employees with salaries of given range and commission

7. From the employees table, write a SQL query to retrieve all employees who either :
earn a salary between 8000 and 12000 (inclusive) and receive a commission (commission_pct is not null),
or
were hired before '2003-06-05' and do not belong to the departments with IDs 40, 120, or 70. Return all fields.

Sample table: employees 
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| 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 |
|         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 2001-01-13 | AD_VP      | 17000.00 |           0.00 |        100 |            90 |
|         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 2006-01-03 | IT_PROG    |  9000.00 |           0.00 |        102 |            60 |
|         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 2007-05-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60 |
|         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 2005-06-25 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 2006-02-05 | IT_PROG    |  4800.00 |           0.00 |        103 |            60 |
|         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 2007-02-07 | IT_PROG    |  4200.00 |           0.00 |        103 |            60 |
|         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 2002-08-17 | FI_MGR     | 12008.00 |           0.00 |        101 |           100 |
|         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 2002-08-16 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100 |
......
|         206 | William     | Gietz       | WGIETZ   | 515.123.8181       | 2002-06-07 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110 |
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+

View the table

Sample Solution: 

SELECT *
FROM employees
WHERE (
         salary BETWEEN 8000 AND 12000 
         AND commission_pct IS NOT NULL
      )
   OR (
         department_id NOT IN (40, 120, 70)
         AND hire_date < '2003-06-05'
      );

Sample Output:

employee_id|first_name |last_name |email   |phone_number      |hire_date |job_id    |salary  |commission_pct|manager_id|department_id|
-----------|-----------|----------|--------|------------------|----------|----------|--------|--------------|----------|-------------|
        102|Lex        |De Haan   |LDEHAAN |515.123.4569      |2001-01-13|AD_VP     |17000.00|          0.00|       100|           90|
        103|Alexander  |Hunold    |AHUNOLD |590.423.4567      |2006-01-03|IT_PROG   | 9000.00|          0.00|       102|           60|
        108|Nancy      |Greenberg |NGREENBE|515.124.4569      |2002-08-17|FI_MGR    |12000.00|          0.00|       101|          100|
        109|Daniel     |Faviet    |DFAVIET |515.124.4169      |2002-08-16|FI_ACCOUNT| 9000.00|          0.00|       108|          100|
        110|John       |Chen      |JCHEN   |515.124.4269      |2005-09-28|FI_ACCOUNT| 8200.00|          0.00|       108|          100|
        114|Den        |Raphaely  |DRAPHEAL|515.127.4561      |2002-12-07|PU_MAN    |11000.00|          0.00|       100|           30|
        115|Alexander  |Khoo      |AKHOO   |515.127.4562      |2003-05-18|PU_CLERK  | 3100.00|          0.00|       114|           30|
        120|Matthew    |Weiss     |MWEISS  |650.123.1234      |2004-07-18|ST_MAN    | 8000.00|          0.00|       100|           50|
        121|Adam       |Fripp     |AFRIPP  |650.123.2234      |2005-04-10|ST_MAN    | 8200.00|          0.00|       100|           50|
        122|Payam      |Kaufling  |PKAUFLIN|650.123.3234      |2003-05-01|ST_MAN    | 7900.00|          0.00|       100|           50|
        147|Alberto    |Errazuriz |AERRAZUR|011.44.1344.429278|2005-03-10|SA_MAN    |12000.00|          0.30|       100|           80|
        148|Gerald     |Cambrault |GCAMBRAU|011.44.1344.619268|2007-10-15|SA_MAN    |11000.00|          0.30|       100|           80|
        149|Eleni      |Zlotkey   |EZLOTKEY|011.44.1344.429018|2008-01-29|SA_MAN    |10500.00|          0.20|       100|           80|
        150|Peter      |Tucker    |PTUCKER |011.44.1344.129268|2005-01-30|SA_REP    |10000.00|          0.30|       145|           80|
        151|David      |Bernstein |DBERNSTE|011.44.1344.345268|2005-03-24|SA_REP    | 9500.00|          0.25|       145|           80|
        152|Peter      |Hall      |PHALL   |011.44.1344.478968|2005-08-20|SA_REP    | 9000.00|          0.25|       145|           80|
        153|Christopher|Olsen     |COLSEN  |011.44.1344.498718|2006-03-30|SA_REP    | 8000.00|          0.20|       145|           80|
        156|Janette    |King      |JKING   |011.44.1345.429268|2004-01-30|SA_REP    |10000.00|          0.35|       146|           80|
        157|Patrick    |Sully     |PSULLY  |011.44.1345.929268|2004-03-04|SA_REP    | 9500.00|          0.35|       146|           80|
        158|Allan      |McEwen    |AMCEWEN |011.44.1345.829268|2004-08-01|SA_REP    | 9000.00|          0.35|       146|           80|
        159|Lindsey    |Smith     |LSMITH  |011.44.1345.729268|2005-03-10|SA_REP    | 8000.00|          0.30|       146|           80|
        162|Clara      |Vishney   |CVISHNEY|011.44.1346.129268|2005-11-11|SA_REP    |10500.00|          0.25|       147|           80|
        163|Danielle   |Greene    |DGREENE |011.44.1346.229268|2007-03-19|SA_REP    | 9500.00|          0.15|       147|           80|
        168|Lisa       |Ozer      |LOZER   |011.44.1343.929268|2005-03-11|SA_REP    |11500.00|          0.25|       148|           80|
        169|Harrison   |Bloom     |HBLOOM  |011.44.1343.829268|2006-03-23|SA_REP    |10000.00|          0.20|       148|           80|
        170|Tayler     |Fox       |TFOX    |011.44.1343.729268|2006-01-24|SA_REP    | 9600.00|          0.20|       148|           80|
        174|Ellen      |Abel      |EABEL   |011.44.1644.429267|2004-05-11|SA_REP    |11000.00|          0.30|       149|           80|
        175|Alyssa     |Hutton    |AHUTTON |011.44.1644.429266|2005-03-19|SA_REP    | 8800.00|          0.25|       149|           80|
        176|Jonathon   |Taylor    |JTAYLOR |011.44.1644.429265|2006-03-24|SA_REP    | 8600.00|          0.20|       149|           80|
        177|Jack       |Livingston|JLIVINGS|011.44.1644.429264|2006-04-23|SA_REP    | 8400.00|          0.20|       149|           80|
        204|Hermann    |Baer      |HBAER   |515.123.8888      |2002-06-07|PR_REP    |10000.00|          0.00|       101|           70|
        205|Shelley    |Higgins   |SHIGGINS|515.123.8080      |2002-06-07|AC_MGR    |12000.00|          0.00|       101|          110|
        206|William    |Gietz     |WGIETZ  |515.123.8181      |2002-06-07|AC_ACCOUNT| 8300.00|          0.00|       205|          110|

Code Explanation:

The said query in SQL that retrieves all columns from the 'employees' table where the salary is between 8000 and 12000 and commission_pct is not null, or the department_id is not in the values 40, 120, 70 and the hire_date is before June 5th, 2003.

Relational Algebra Expression:

Relational Algebra Expression: Find those employees 1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. or 2. Those employees who joined before ‘2003-06-05’ and not included in the department number 40, 120 and 70.


Relational Algebra Tree:

Relational Algebra Tree: Find those employees 1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. or 2. Those employees who joined before ‘2003-06-05’ and not included in the department number 40, 120 and 70


Go to:


PREV : Employees first name does not contain the letter M .
NEXT : Employees who does not earn any commission.

Practice Online


HR database model


Query Visualization:

Duration:

Query visualization of Find those employees 1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. 2. Those employees who joined before ‘2003-06-05’ and not included in the department number 40, 120 and 70 - Duration.


Rows:

Query visualization of Find those employees 1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get some commission. 2. Those employees who joined before ‘2003-06-05’ and not included in the department number 40, 120 and 70 - Rows.


Cost:

Query visualization of Display all the information of employees whose salary is in the range of 8000 and 12000 and commission is not null or department number is except the number 40, 120 and 70 and they have been hired before June 5th, 2003 - Cost.


Contribute your code and comments through Disqus.

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.