w3resource

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. Write a query to extract the data from the orders table for those salesman who earned the maximum commission.

Sample table: Customer


Sample table: Orders


Sample table: salesman


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:

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

Practice Online


Inventory database model

Contribute your code and comments through Disqus.

Previous: Write a query to count the customers with grades above New York's average.
Next: Write a query to display all the customers with orders issued on date 17th August, 2012.

What is the difficulty level of this exercise?