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