SQL Exercises: Make a list of customers who appointed a salesman for their jobs who does not live in the same city where their customer lives, and gets a commission is above 12%

SQL JOINS: Exercise-5 with Solution

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

Sample table: salesman

Sample Solution:

SELECT a.cust_name AS "Customer Name", 
a.city, b.name AS "Salesman", b.city,b.commission  
FROM customer a  
INNER JOIN salesman b  
ON a.salesman_id=b.salesman_id 
WHERE b.commission>.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:

Pictorial presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following tables write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.
Next: From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.