SQL Exercises: Using between operator to get a value from a range

SQL Wildcard & Special Operator: Exercise-5 with Solution

From the following table, write a SQL query to find salespeople who receive commissions between 0.12 and 0.14 (begin and end values are included). Return salesman_id, name, city, and commission.

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample Solution:

-- Selecting all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'commission' is between 0.12 and 0.14
WHERE commission BETWEEN 0.12 AND 0.14;

Output of the Query:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13
5003		Lauson Hen	San Jose0.12

Code Explanation:

The said SQL query that selects all columns (*) from a table called 'salesman' where the "commission" is between 0.12 and 0.14. It will return all the rows in the 'salesman' table where the "commission" is greater than or equal to 0.12 and less than or equal to 0.14.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Sort records using in operator.
Next SQL Exercise: Using between, not and in operators to filter records.