w3resource
SQL exercises

SQL Exercises: Find highest purchase amount by the customer from the salesman with a specified ID range

SQL Aggregate Functions: Exercise-17 with Solution

Write a SQL statement to find the highest purchase amount with their ID, for only those salesmen whose ID is within the range 5003 and 5008.

Sample table: orders


Sample Solution:

SELECT salesman_id,MAX(purch_amt) 
FROM orders 
GROUP BY salesman_id 
HAVING salesman_id BETWEEN 5003 AND 5008;

Output of the Query:

salesman_id	max
5005		270.65
5003		2480.40
5007		75.29
5006		1983.43

Explanation:

Syntax of highest purchase amount with specied customer ID range

Pictorial presentation:

Result of highest purchase amount with specied customer ID range

Query Visualization:

Duration:

Query visualization of Find highest purchase amount by the customer from the salesman with a specified ID range - Duration

Rows:

Query visualization of Find highest purchase amount by the customer from the salesman with a specified ID range - Rows

Cost:

Query visualization of Find highest purchase amount by the customer from the salesman with a specified ID range - Cost

 

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to display customer details (ID and purchase amount) whose IDs are within the range 3002 and 3007 and highest purchase amount is more than 1000.
Next: Write a SQL statement that counts all orders for a date August 17th, 2012.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming