w3resource

SQL Exercise: Customers and salespeople live in different cities

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:

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:

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%

Practice Online


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

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook