w3resource
SQL exercises

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 :

Syntax to find salesman commission details where customer grade more than 200

Pictorial presentation:

Result of salesman commission details where customer grade more than 200

Query Visualization:

Duration:

Query visualization of Find salesman commission details where customer grade more than 200 - Duration

Rows:

Query visualization of Find salesman commission details where customer grade more than 200 - Rows

Cost:

Query visualization of Find salesman commission details where customer grade more than 200 - Cost

 

Practice Online


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?



New Content: Composer: Dependency manager for PHP, R Programming