w3resource

SQL Exercises: Customers served by a salesman and commission

SQL Query on Multiple Tables: Exercise-6 with Solution

From the following table, write a SQL query to find those customers who are served by a salesperson and the salesperson earns commission in the range of 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".

Sample table: salesman


Sample table: customer


Sample Solution:

SELECT customer.cust_name AS "Customer",
customer.city AS "City",
salesman.name AS "Salesman",
salesman.commission
FROM customer,salesman
WHERE customer.salesman_id = salesman.salesman_id
AND salesman.commission
BETWEEN .12 AND .14;

Output of the query:

Customer	City		Salesman	commission
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

Code Explanation:

The said query in SQL that joins the 'customer' and 'salesman' tables based on the "salesman_id" column. The result set includes the customer name with an alias "Customer", customer city with an alias "City", salesman name with an alias "Salesman", and commission from the salesman table.
The WHERE clause specifies multiple conditions for the join. The first condition is that the salesman_id column of the customer table must be equal to the salesman_id column of the salesman table. The second condition specifies that a particular row of the salesman table must contain a value between 0.12 and 0.14, inclusive, for the commission column.

Explanation:

Syntax to find customers details who served by a salesman and commission within a range

Visual presentation :

Result of customers details who served by a salesman and commission within a range

Practice Online


Query Visualization:

Duration:

Query visualization of Find customers details who served by a salesman and commission within a range - Duration

Rows:

Query visualization of Find customers details who served by a salesman and commission within a range - Rows

Cost:

Query visualization of Find customers details who served by a salesman and commission within a range - Cost

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

Previous SQL Exercise: Sort out the customer who made at least an order.
Next SQL Exercise: Find salesman commission details of given customer.

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