SQL Exercise: Employees with salaries of given range and commission
SQL SORTING and FILTERING on HR Database: Exercise-7 with Solution
7. From the following table, write a SQL query to find those employees who earn between 8000 and 12000 (Begin and end values are included.) and get some commission. These employees joined before ‘1987-06-05’ and were not included in the department numbers 40, 120 and 70. Return all fields.
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 < '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 Tree:

Practice Online

Query Visualization:
Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.
Previous SQL Exercise: Employees first name does not contain the letter M .
Next SQL Exercise: Employees who does not earn any commission.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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