SQL Exercises: Customer details and purchases over a certain amount
From the following table, write a SQL query to find the maximum order (purchase) amount for each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.).Filter the rows for maximum order (purchase) amount is higher than 1000. Return customer id and maximum 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 'customer_id' from the 'orders' table.
-- It only includes rows where the 'customer_id' is between 3002 and 3007.
-- The result set is then further filtered to only include rows where the maximum 'purch_amt' is greater than 1000.
SELECT customer_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 'customer_id' is between 3002 and 3007.
WHERE customer_id BETWEEN 3002 AND 3007
-- Groups the result set by the 'customer_id' column.
GROUP BY customer_id
-- Filters the grouped results to only include those where the maximum 'purch_amt' is greater than 1000.
HAVING MAX(purch_amt) > 1000;
Output of the Query:
customer_id max 3002 5760.00 3007 2400.60 3004 1983.43
Code Explanation:
The given SQL query will retrieve the customer_id as well as the maximum purchase amount (purch_amt) for customers with an ID between 3002 and 3007, but this will only work if the maximum purchase amount exceeds 1000 from the 'orders' table.
The "HAVING" clause filters the result set by only showing customers whose maximum purchase amount is greater than 1000. The result will show the maximum purchase amount made by each customer in the specified range, with the customer_id being grouped together.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation:
Visual presentation :
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to find the maximum order amount for customers with IDs in the range 3001 to 3005, but only for orders with a purchase amount greater than 1000.
- Write a SQL query to find the maximum order amount for customers with IDs in the range 3003 to 3007, but only for orders placed in the month of September 2012.
- Write a SQL query to find the maximum order amount for customers with IDs in the range 3004 to 3008, but only for orders with a purchase amount greater than 2000.
- Write a SQL query to find the maximum order amount for customers with IDs in the range 3002 to 3006, but only for customers assigned to salesman ID 5002.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Highest purchase amount from customers.
Next SQL Exercise: Customer with the highest purchase amount.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.