SQL Exercises 4 — Aggregate Functions
Orders Table
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 50011. Calculate total purchase amount of all orders. Return total purchase amount.
SELECT SUM(purch_amt) AS total_purchase_amount
FROM orders
2. Calculate average purchase amount of all orders. Return average purchase amount.
SELECT AVG(purch_amt) AS average_purchase_amount
FROM orders
3. Count the number of unique salespeople. Return number of salespeople.
SELECT COUNT(DISTINCT(salesman_id)) AS number_of_salespeople
FROM orders
4. Find the maximum purchase amount.
SELECT MAX(purch_amt)
FROM orders
5. Find the minimum purchase amount.
SELECT MIN(purch_amt)
FROM orders
6. Find the highest purchase amount ordered by each customer. Return customer ID, maximum purchase amount.
SELECT customer_id, MAX(purch_amt) AS max_purch_amt
FROM orders
GROUP BY customer_id
ORDER BY customer_id
7. Find the highest purchase amount ordered by each customer on a particular date. Return, order date and highest purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt) AS highest_purch_amt
FROM orders
GROUP BY ord_date, customer_id
ORDER BY ord_date, customer_id
8. Find the highest purchase amount on ‘2012–08–17’ by each salesperson. Return salesperson ID, purchase amount.
SELECT salesman_id, MAX(purch_amt)
FROM orders
WHERE ord_date = ‘2012–08–17’
GROUP BY salesman_id
9. Find highest order (purchase) amount by each customer in a particular order date. Filter the result by highest order (purchase) amount above 2000.00. Return customer id, order date and maximum purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt)
FROM orders
GROUP BY customer_id, ord_date
HAVING MAX(purch_amt) > 2000
ORDER BY ord_date, customer_id
10. 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. Return customer id, order date and maximum purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt)
FROM orders
GROUP BY customer_id, ord_date
HAVING MAX(purch_amt) BETWEEN 2000 AND 6000
ORDER BY ord_date, customer_id
11. 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. Return customer id, order date and maximum purchase amount.
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)
ORDER BY ord_date, customer_id
12. 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.). Return customer id and maximum purchase amount.
SELECT customer_id, MAX(purch_amt)
FROM orders
WHERE customer_id BETWEEN 3002 AND 3007
GROUP BY customer_id
ORDER BY customer_id
13. 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.
SELECT customer_id, MAX(purch_amt)
FROM orders
WHERE customer_id BETWEEN 3002 AND 3007
GROUP BY customer_id
HAVING MAX(purch_amt) > 1000
ORDER BY customer_id
14. 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.). Return salesperson id and maximum purchase amount.
SELECT salesman_id, MAX(purch_amt)
FROM orders
WHERE salesman_id BETWEEN 5003 AND 5008
GROUP BY salesman_id
ORDER BY salesman_id
15. Count all the orders generated on ‘2012–08–17’. Return number of orders.
SELECT COUNT(*)
FROM orders
WHERE ord_date = ‘2012–08–17’
16. Count number of orders by the combination of each order date and salesperson. Return order date, salesperson id.
SELECT ord_date, salesman_id, COUNT(*)
FROM orders
GROUP BY salesman_id, ord_date
ORDER BY ord_date, salesman_id
Customer Table
customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 50051. Count the number of customers. Return number of customers.
SELECT COUNT(*) AS number_of_customers
FROM customer
2. Find the number of customers who got at least a gradation for his/her activity.
SELECT COUNT(ALL grade)
FROM customer
3. Find the highest grade of the customers for each of the city. Return city, maximum grade.
SELECT city, MAX(grade) AS max_grade
FROM customer
GROUP BY city
ORDER BY max_grade DESC, city
Salesman Table
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.121. Count number of salespeople who belongs to a city. Return number of salespeople.
SELECT COUNT(*)
FROM salesman
WHERE city IS NOT NULL
Item Master Table
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 121. Calculate the average product price. Return average product price.
SELECT AVG(pro_price) AS “Average Price”
FROM item_mast
2. Count number of products where product price is higher than or equal to 350. Return number of products.
SELECT COUNT(*) AS “Number of Products”
FROM item_mast
WHERE pro_price >= 350
3. 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
ORDER BY AVG(pro_price) DESC
Employee Department Table
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 750001. Compute the sum of the allotment amount of all departments. Return sum of the allotment amount.
SELECT SUM(dpt_allotment)
FROM emp_department
Employee Details Table
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT
--------- --------------- --------------- ----------
127323 Michale Robbin 57
526689 Carlos Snares 63
843795 Enric Dosio 57
328717 Jhon Snares 63
444527 Joseph Dosni 47
659831 Zanifer Emily 47
847674 Kuleswar Sitaraman 57
748681 Henrey Gabriel 47
555935 Alex Manuel 57
539569 George Mardy 27
733843 Mario Saule 63
631548 Alan Snappy 27
839139 Maria Foster 571. Find the number of employees in each department. Return department code and number of employees.
SELECT emp_dept, COUNT(*)
FROM emp_details
GROUP BY emp_dept
ORDER BY emp_dept
