SQL Exercise: Using AND operator with a specified condition

Last update on February 10 2025 06:59:41 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the details of those salespeople whose commissions range from 0.10 to0.12. 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 :

-- This query selects specific columns 'salesman_id', 'name', 'city', and 'commission' from the 'salesman' table.
SELECT salesman_id, name, city, commission
-- Specifies the table from which to retrieve the data (in this case, 'salesman').
FROM salesman
-- Filters the rows to only include those where the 'commission' column is greater than 0.10 and less than 0.12.
WHERE (commission > 0.10 AND commission < 0.12);

Output of the Query:

salesman_id	name		city	commission
5005		Pit Alex	London	0.11

Code Explanation:

The given SQL query that is selecting specific columns "salesman_id, name, city, commission" from the 'salesman' table where the value of the "commission" column is greater than 0.10 and less than 0.12.
The query will return all rows from the ‘salesman’ table where the commission is greater than 0.10 and less than 0.12 along with the selected columns.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation :

Visual presentation:

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find salespeople whose commission is between 0.13 and 0.15.
• Write a SQL query to retrieve salespeople with a commission greater than 0.14.
• Write a SQL query to display salespeople whose commission is less than 0.11.
• Write a SQL query to list salespeople with a commission between 0.12 and 0.14, sorted by commission in ascending order.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Using AND, OR operator with a specified condition.
Next SQL Exercise: Using NOT, AND operators with specified condition.