w3resource
.
SQL exercises

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

Write a SQL statement to find the 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%.

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:

Syntax of list of customers who appointed a salesman for their jobs who does not live in same city where there customer lives, and gets a commission is above 12%

Pictorial presentation:

Result of list of customers who appointed a salesman for their jobs who does not live in same city where there customer lives, and gets a commission is above 12%

Query Visualization:

Duration:

Query visualization of 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% - Duration

Rows:

Query visualization of 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% - Rows

Cost:

Query visualization of 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% - Cost

 

Practice Online


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

Previous: Write a SQL statement to find the list of customers who appointed a salesman for their jobs who gets a commission from the company is more than 12%.
Next: Write a SQL statement to find the details of a order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and how much commission he gets for an order.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming