1.
Write a SQL statement to find the total purchase amount of all
orders.
Sample table : orders
SELECT SUM(purch_amt) FROM orders;
2. Write a SQL statement to find the average purchase amount of
all orders.
Sample table : orders
SELECT AVG(purch_amt) FROM orders;
3. Write a SQL statement to find the number of salesmen
currently listing for all of their customers.
Sample table : orders
SELECT COUNT(DISTINCT (salesman_id)) FROM orders;
4. Write a SQL statement know how many customer have listed
their names.
Sample table : customer
SELECT COUNT(DISTINCT (cust_name)) FROM customer;
5. Write a SQL statement find the number of customers who gets
at least a gradation for his/her performance.
Sample table : customer
SELECT COUNT(grade) FROM customer;
6. Write a SQL statement to get the maximum purchase amount
of all the orders.
Sample table : orders
SELECT MAX(purch_amt) FROM orders;
7. Write a SQL statement to get the minimum purchase amount
of all the orders.
Sample table : orders
SELECT Min(purch_amt) FROM orders;
8. Write a SQL statement which selects the highest grade for
each of the cities of the customers.
Sample table : customer
SELECT MAX(grade),city FROM customer GROUP BY city;
9. Write a SQL statement to find the highest purchase amount
ordered by the each customer with their ID and highest purchase
amount.
Sample table : orders
SELECT customer_id, MAX(purch_amt) FROM orders GROUP BY
customer_id;
10. Write a SQL statement to find the highest purchase amount
ordered by the each customer on a particular date with their ID,
order date and highest purchase amount.
Sample table : orders
SELECT customer_id,ord_date MAX(purch_amt) FROM orders GROUP BY
customer_id,ord_date;
11. Write a SQL statement to find the highest purchase amount
on a date '2012-08-17' for each salesman with their ID.
Sample table : orders
SELECT salesman_id,max(purch_amt) FROM orders WHERE
ord_date =
TO_DATE(‘2012-08-17’,’yyyy-mm-dd’) GROUP BY salesman_id;
12. Write a SQL statement to find the highest purchase amount
with their ID and order date, for only those customers who have
highest purchase amount in a day is more than 2000.
Sample table : orders
SELECT customer_id,ord_date,MAX(purch_amt) FROM orders
WHERE purch_amt > 2000 GROUP BY customer_id,ord_date;
13. Write a SQL statement to find the highest purchase amount
with their ID and order date, for those customers who have
a higher purchase amount in a day is within the range 2000 and
6000.
Sample table : orders
SELECT customer_id, ord_date,MAX(purch_amt) FROM orders GROUP BY
customer_id,ord_date HAVING max(purch_amt) BETWEEN 2000 AND 6000;
14. Write a SQL statement to find the highest purchase amount
with their ID and order date, for only those customers who have a
higher purchase amount in a day is within the list 2000, 3000,
5760 and 6000.
Sample table : orders
SELECT customer_id, ord_date,MAX(purch_amt) FROM orders GROUP BY
customer_id,ord_date HAVING max(purch_amt) IN (2000,3000,5760,6000);
15. Write a SQL statement to find the highest purchase amount
with their ID, for only those customers whose ID is within the
range 3002 and 3007.
Sample table : orders
SELECT customer_id,MAX(purch_amt) FROM orders WHERE customer_id
BETWEEN 3002 AND 3007 GROUP BY customer_id;
16. 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.
Sample table : orders
SELECT customer_id,MAX(purch_amt) FROM orders WHERE customer_id
BETWEEN 3002 AND 3007 GROUP BY customer_id HAVING MAX(purch_amt) > 1000;
17. 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
SELECT salesman_id,MAX(purch_amt) FROM orders WHERE salesman_id BETWEEN
5003 AND 5008 GROUP BY salesman_id;
18. Write a SQL statement that counts all orders for a date
August 17th, 2012.
Sample table : orders
SELECT COUNT(*) FROM orders WHERE ord_date =
TO_DATE(‘2012-08-17’, ‘yyyy-mm-dd’);
19. Write a SQL statement that counts the number of different
non NULL city values for salesmen.
Sample table : salesman
SELECT COUNT(city) FROM salesman WHERE city IS NOT NULL;
20. Write a query that counts the number of salesmen with their
order date and ID registering orders for each day.
Sample table : orders
SELECT salesman_id,ord_date, count(*) FROM orders GROUP BY salesman_id,ord_date;
