SQL Exercise: Customers and salespeople live in different cities

Last update on April 22 2025 10:56:14 (UTC/GMT +8 hours)

5. Different City & High Commission

From the following tables write a SQL query to locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company. Return Customer Name, customer city, Salesman, salesman city, commission.

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 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 specific columns and renaming them for clarity
SELECT a.cust_name AS "Customer Name", 
       a.city, 
       b.name AS "Salesman", 
       b.city, b.commission  
-- Specifying the tables to retrieve data from ('customer' as 'a' and 'salesman' as 'b')
FROM customer a  
-- Performing an inner join based on the salesman_id
INNER JOIN salesman b  
ON a.salesman_id = b.salesman_id 
-- Filtering the results based on two conditions (commission greater than 0.12 and customer city not equal to salesman city)
WHERE b.commission > 0.12 
AND a.city <> b.city;

Output of the Query:

Customer Name	city		Salesman	city	commission
Graham Zusi	California	Nail Knite	Paris	0.13
Julian Green	London		Nail Knite	Paris	0.13
Jozy Altidor	Moscow		Paul Adam	Rome	0.13

Explanation:

The said SQL query that is used to select specific columns from two tables, customer and salesman, and join them using the 'salesman_id' column.
The query selects the 'cust_name' column from the customer table as 'Customer Name', the 'city' column from the customer table, the 'name' column from the salesman table as 'Salesman', the 'city' column from the salesman table and the 'commission' column from the salesman table.
The query uses an inner join, which only returns rows where there is a match in both tables on the specified join column. Additionally, it has a WHERE clause that filters out the rows where the commission of the Salesman is less than or equal to 0.12 and it also have a condition on City that it should not be equal to City of the salesman.
So, only the rows where commission of the Salesman is greater than 0.12 and City of the customer is not equal to City of Salesman will be returned.

Visual Explanation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

For more Practice: Solve these Related Problems:

• Write a SQL query to retrieve customer name, customer city, salesperson name, salesperson city, and commission for cases where the customer and salesperson reside in different cities and the commission is over 12%.
• Write a SQL query to list details of mismatched city pairs where the salesperson’s commission exceeds 12%, ordering the results by customer name.
• Write a SQL query to find records with non-matching customer and salesperson cities, filtering by commission greater than 12% and including only customers with a valid grade.
• Write a SQL query to display mismatched city details for customers and salespeople with commission above 12%, grouping the results by the salesperson’s city.

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

Previous SQL Exercise: A salesperson who gets a commission of at least 12%.
Next SQL Exercise: Display commission of the salesman for an order.