SQL Exercises: Find customers details who served by a salesman and commission within a range
SQL Query on Multiple Tables : Exercise-6 with Solution
Write a query that produces all customers with their name, city, salesman and commission, who served by a salesman and the salesman works at a rate of the commission within 12% to 14%.
Sample table: salesman
Sample table: customer
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
Pictorial presentation :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement that shorts out the customer and their grade who made an order. Each of the customers must have a grade and served by at least a salesman, who belongs to a city.
Next: Write a SQL statement that produces all orders with the order number, customer name, commission rate and earned commission amount for those customers who carry their grade more than 200 and served by an existing salesman.
What is the difficulty level of this exercise?