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:
Practice Online
Query Visualization:
Duration:
Rows:
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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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