w3resource
SQL exercises

SQL SORTING and FILTERING on HR Database: 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, 1987

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

7. Write a query in SQL to 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, 1987.

Sample table employees


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 < '1987-06-05'

Sample Output:

 employee_id | first_name  | last_name  |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         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
         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
         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
(30 rows)

Practice Online


HR database model

Query Visualization:

Duration:

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, 1987 - Duration

Rows:

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, 1987 - 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, 1987 - Cost

Contribute your code and comments through Disqus.

Previous: Write a query in SQL to display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M and make the result set in ascending order by department number.
Next: Write a query in SQL to display the full name (first and last name), and salary for all employees who does not earn any commission.

What is the difficulty level of this exercise?



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