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
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
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
What is the difficulty level of this exercise?