Customer ID and purchase amount for customers whose IDs are

Customer ID and purchase amount for customers whose IDs are within the range 3002 and 3007 and their highest purchase amount is more than 1000;

The highest purchase amount and salesman ID, for only those salesmen whose ID is within the range 5003 and 5008;

Total number of customers who placed at least one order on August 17th, 2012 (Attention: make sure each customer is not counted more than once);

1. Write SQL statements to find the following items from salesman table: Salesman table ord no urch amt ord date customer id salesman id 70001 70009 70002 70004 70007 70005 70008 70010 70003 70012 70011 70013 150.5 270.65 65.26 110.5 948.5 2400.6 5760 1983.43 2480.4 250.45 75.29 3045.6 2012-10-05 2012-09-10 2012-10-05 2012-08-17 2012-09-10 2012-07-27 2012-09-10 2012-10-10 2012-10-10 2012-06-27 2012-08-17 2012-04-25 3005 3001 3002 3009 3005 3007 3002 3004 3009 3008 3003 3002 5002 5005 5001 5003 5002 5001 5001 5006 5003 5002 5007 5001

Solution

1.

SELECT customer_id, MAX(purch_amt)

FROM Salesman

WHERE customer_id BETWEEN 3002 and 3007

GROUP BY customer_id

HAVING MAX(purch_amt) > 1000;

2.

SELECT salesman_id, MAX(purch_amt)

FROM Salesman

GROUP BY salesman_id

HAVING salesman_id BETWEEN 5003 AND 5008;

3.

SELECT count(*)

FROM Salesman

WHERE ord_date = \'2012-08-17\';