SQL Exercise: A list of which salesmen work for which customers
3. Salesman-Customer Representation
From the following tables write a SQL query to find the salesperson(s) and the customer(s) he represents. Return Customer Name, city, Salesman, 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.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;
Output of the Query:
Customer Name city Salesman commission Nick Rimando New York James Hoog 0.15 Brad Davis New York James Hoog 0.15 Graham Zusi California Nail Knite 0.13 Julian Green London Nail Knite 0.13 Fabian Johnson Paris Mc Lyon 0.14 Geoff Cameron Berlin Lauson Hen 0.12 Jozy Altidor Moscow Paul Adam 0.13 Brad Guzan London Pit Alex 0.11
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', 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.
Visual Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to retrieve customer name, city, salesperson name, and commission, ensuring that the commission is formatted as a percentage.
- Write a SQL query to list each customer along with their representing salesperson, filtering the result to include only customers whose names start with 'J'.
- Write a SQL query to fetch details of customers and their assigned salespeople, ordering the results by customer grade in descending order.
- Write a SQL query to display customer name, city, and corresponding salesperson details only when both reside in the same city.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Customers and their cities for given range of orders.
Next SQL Exercise: A salesperson who gets a commission of at least 12%.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.