Write SQL statements to find the following items based on sa

Write SQL statements to find the following items based on salesman/customer/order tables: List of salesmen whose average customer grade is greater than 200 sorted in descending order (by the average customer grade). i. List of top 10 salesmen with the highest sum of customers\' purchase. j. List of salesmen assigned to more than 10 customers along with number of assigned customers sorted in descending order. k. List of salesmen who have at least one customer with a purchase amount over $10,000 along with the maximum purchase amount of their customers.

Solution

SELECT DISTINCT s.salesman_id FROM salesman s
JOIN customer c
ON s.salesman_id = c.salesman_id
WHERE c.grade >= 200
ORDER BY c.grade DESC

SELECT top 10 * FROM
(SELECT s.salesman_id, SUM(o.purch_amt) as totalAmt FROM salesman s
JOIN order o
ON s.salesman_id = o.salesman_id
GROUP BY s.salesman_id) temp
ORDER BY temp.totalAmt DESC

SELECT s1.* FROM
(
SELECT s.salesman_id,COUNT(s.customer_id) as totalCust FROM salesman
GROUP BY s.salesman_id
HAVING COUNT(s.customer_id) > 10
) temp
JOIN salesman s1
ON temp.salesman_id = s1.salesman_id
ORDER BY temp.totalCust DESC

SELECT s1.*, temp.maxPurchAmt FROM
(
SELECT s.salesman_id, MAX(s.purch_amt) as maxPurchAmt FROM salesman s
WHERE s.purch_amt > 10000
GROUP BY s.salesman_id
) temp
JOIN salesman s1
ON temp.salesman_id = s1.salesman_id