w3resource

SQL Exercises: Find customers details who served by a salesman and commission within a range

SQL Query on Multiple Tables: Exercise-6 with Solution

From the following table, write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 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

Explanation:

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

Pictorial 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: From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade".
Next: From the following tables, write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.

What is the difficulty level of this exercise?