SQL Exercises: Highest purchase amount on a date for each salesman

Last update on December 20 2024 11:33:37 (UTC/GMT +8 hours)

From the following table, write a SQL query to determine the highest purchase amount made by each salesperson on '2012-08-17'. Return salesperson ID, purchase amount.

Sample table: orders

ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

Sample Solution :

-- This query retrieves the maximum 'purch_amt' value for each 'salesman_id' from the 'orders' table where the 'ord_date' is '2012-08-17'.
SELECT salesman_id, MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Filters the rows to only include those where the 'ord_date' is '2012-08-17'.
WHERE ord_date = '2012-08-17'
-- Groups the result set by the 'salesman_id' column.
GROUP BY salesman_id;

Output of the Query:

salesman_id	max
5003		110.50
5007		75.29

Code Explanation:

The said SQL query retrieves the salesman id and the maximum purchase amount for orders that have an order date of '2012-08-17' in the 'orders' table. The result will be grouped by salesman id, and only the maximum purchase amount will be returned for each group.

Relational Algebra Expression: