-- write a SQL query to calculate total purchase amount of all orders
SELECT SUM (purch_amt)
From orders;

-- write a SQL query to calculate average purchase amount of all orders
SELECT AVG (purch_amt)
FROM orders;

-- write a SQL query to count the number of unique salespeople.
SELECT Count (DISINCT salesman_id)
FROM orders;

-- write a SQL query to count the number of customers.
SELECT COUNT (*)
FROM customer;

-- write a SQL query to find the number of customers who got at least a gradation for his/her activity.
SELECT count (ALL grade)
FROM customer ;

-- write a SQL query to find the maximum purchase amount.
SELECT MAX (purch_amt)
FROM orders;

-- write a SQL query to find the minimum purchase amount.
SELECT MIN (purch_amt)
FROM orders;

-- write a SQL query to find the highest grade of the customers for each of the city.

SELECT city,MAX(grade)
FROM customer
group by city;

-- write a SQL query to find the highest purchase amount ordered by each customer.
SELECT customer_id,MAX(purch_amt) FROM orders GROUP BY customer_id;

-- write a SQL query to find the highest purchase amount ordered by each customer on a particular date.
SELECT customer_id,ord_date,MAX(purch_amt) FROM orders GROUP BY customer_id,ord_date;

-- write a SQL query to find the highest purchase amount on '2012-08-17' by each salesperson.

SELECT salesman_id,MAX(purch_amt)
From orders WHERE ord_date = '2012-08-17'GROUP BY salesman_id;

-- write a SQL query to find highest order (purchase) amount by each customer in a particular order date. Filter the result by highest order (purchase) amount above 2000.00.
SELECT ord_date,customer_id,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX (purch_amt)>2000.00;

-- write a SQL query to find the maximum order (purchase) amount in the range 2000, 6000 (Begin and end values are included.) by combination of each customer and order date.

SELECT ord_date,customer_id,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX (purch_amt) BETWEEN 2000 AND 6000;

-- write a SQL query to find the maximum order (purchase) amount by the combination of each customer and order date. Filter the rows for maximum order (purchase) amount is either 2000, 3000, 5760, 6000.
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);

-- write a SQL query to find the maximum order (purchase) amount by each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.).

SELECT customer_id,MAX(purch_amt)
FROM orders
WHERE customer_id BETWEEN 3002 and 3007
GROUP BY customer_id;

--   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.

SELECT customer_id,MAX (purch_amt)FROM orders
WHERE customer_id BETWEEN 3002 and 3007 GROUP BY customer_id
HAVING MAX(purch_amt)>1000;

-- write a SQL query to find the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.)
SELECT salesman_id,MAX(purch_amt) FROM orders GROUP BY salesman_id HAVING salesman_id BETWEEN 5003 AND 5008;


-- write a SQL query to count all the orders generated on '2012-08-17'. Return number of orders.
SELECT count(*)
FROM orders
WHERE ord_date = '2012-08-17';

-- write a SQL query to count number of salespeople who belongs to a city.
SELECT count(*)
FROM salesman
WHERE CITY IS NOT NULL;

-- write a SQL query to count number of orders by the combination of each order date and salesperson.
SELECT ord_date,salesman_id,count (*)
FROM orders
GROUP BY ord_date,salesman_id;

-- write a SQL query to calculate the average product price.
SELECT AVG(pro_price) AS "Average Price"
FROM item_mast;

-- write a SQL query to count number of products where product price is higher than or equal to 350.
SELECT pro_price,count(*)
FROM item_mast
WHERE PRO_price>=350;

-- write a SQL query to compute the average price for unique companies. Return average price and company id.
SELECT AVG(pro_price) AS "Average Price",
pro_com as "Company ID"
FROM item_mast
GROUP BY pro_com;

-- write a SQL query to compute the sum of the allotment amount of all departments.
SELECT SUM(dpt_allotment)
FROM emp_department;

-- write a SQL query to find the number of employees in each department.
SELECT EMP_DEPT,Count (*)
FROM EMP_details
GROUP BY EMP_DEPT;