SQL Exercises: Customers served by a salesman and commission

SQL Query on Multiple Tables: Exercise-6 with Solution

From the following table, write a SQL query to find those customers who are served by a salesperson and the salesperson earns commission in the range of 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".

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 table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- This query selects specific columns ('customer.cust_name' with alias "Customer", 'customer.city' with alias "City",
-- 'salesman.name' with alias "Salesman", 'salesman.commission') from the 'customer' and 'salesman' tables.
-- It retrieves data where the 'salesman_id' column in the 'customer' table matches the 'salesman_id' column in the 'salesman' table,
-- and the 'commission' column in the 'salesman' table is between 0.12 and 0.14.
SELECT customer.cust_name AS "Customer", customer.city AS "City", salesman.name AS "Salesman", salesman.commission
-- Specifies the tables from which to retrieve the data (in this case, 'customer' and 'salesman').
FROM customer, salesman
-- Specifies the conditions for joining the tables and filtering the data.
WHERE customer.salesman_id = salesman.salesman_id
  AND salesman.commission BETWEEN 0.12 AND 0.14;

Output of the query:

Customer	City		Salesman	commission
Graham Zusi	California	Nail Knite	0.13
Julian Green	London		Nail Knite	0.13
Fabian Johnson	Paris		Mc Lyon		0.14
Geoff Cameron	Berlin		Lauson Hen	0.12
Jozy Altidor	Moscow		Paul Adam	0.13

Code Explanation:

The said query in SQL that joins the 'customer' and 'salesman' tables based on the "salesman_id" column. The result set includes the customer name with an alias "Customer", customer city with an alias "City", salesman name with an alias "Salesman", and commission from the salesman table.
The WHERE clause specifies multiple conditions for the join. The first condition is that the salesman_id column of the customer table must be equal to the salesman_id column of the salesman table. The second condition specifies that a particular row of the salesman table must contain a value between 0.12 and 0.14, inclusive, for the commission column.

Explanation:

Visual presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Sort out the customer who made at least an order.
Next SQL Exercise: Find salesman commission details of given customer.