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
What is the difficulty level of this exercise?
- New Content published on w3resource:
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- React - JavaScript Library
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework