SQL Subquery Exercises: Write a query to extract the data from the orders table for those salesman who earned the maximum commission.

SQL SUBQUERY : Exercise-9 with Solution

9. From the following tables, write a SQL query to find those salespeople who earned the maximum commission. Return ord_no, purch_amt, ord_date, and salesman_id.

Sample table: Salesman

Sample table: Customer

Sample table: Orders

Sample Solution:

SELECT ord_no, purch_amt, ord_date, salesman_id 
FROM orders 
WHERE salesman_id IN(
SELECT salesman_id 
FROM salesman
WHERE commission = (
SELECT MAX(commission) 
FROM salesman));

Output of the Query:

ord_no | purch_amt |  ord_date  | salesman_id 
--------+-----------+------------+-------------
  70002 |     65.26 | 2012-10-05 |        5001
  70005 |   2400.60 | 2012-07-27 |        5001
  70008 |   5760.00 | 2012-09-10 |        5001
  70013 |   3045.60 | 2012-04-25 |        5001
(4 rows)

Explanation:

Practice Online

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to count number of customers with grades above the average grades of New York City. Return grade and count.
Next: From the following tables, write a SQL query to find the customers whose orders issued on 17th August, 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name.