w3resource

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

SQL Query on Multiple Tables: Exercise-7 with Solution

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.

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

Practice Online


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

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

Previous: 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".
Next: From the following table, write a SQL query to find those customers who made orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.

What is the difficulty level of this exercise?