SQL Exercises: Find salesman commission details where customer grade is 200 or more

SQL Query on Multiple Tables: Exercise-7 with Solution

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 is 200 or more and served by an existing salesman.

Sample table: salesman

Sample table: customer

Sample table: orders

Sample Solution:

SELECT ord_no, cust_name, commission AS "Commission%",
purch_amt*commission AS "Commission"
FROM salesman,orders,customer
WHERE orders.customer_id = customer.customer_id
AND orders.salesman_id = salesman.salesman_id
AND customer.grade>=200;

Output of the query:

ord_no	cust_name	Commission%	Commission
70005	Brad Davis	0.15		360.0900
70010	Fabian Johnson	0.14		277.6802
70011	Jozy Altidor	0.13		9.7877
70001	Graham Zusi	0.13		19.5650
70007	Graham Zusi	0.13		123.3050
70012	Julian Green	0.13		32.5585

Explanation:

Pictorial presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: 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%.
Next: SQL JOINS Exercises Home