SQL UNION Exercises: Make a report of which salesman produce the largest and smallest orders on each date

SQL UNION: Exercise-4 with Solution

4. Write a query to make a report of which salesman produce the largest and smallest orders on each date.

Sample table: Salesman

Sample table: Orders

Sample Solution:

SELECT a.salesman_id, name, ord_no, 'highest on', ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
	(SELECT MAX (purch_amt)
	FROM orders c
	WHERE c.ord_date = b.ord_date)
UNION
(SELECT a.salesman_id, name, ord_no, 'lowest on', ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
	(SELECT MIN (purch_amt)
	FROM orders c
	WHERE c.ord_date = b.ord_date))

Sample Output:

salesman_id	name	   	ord_no	?column?	ord_date
5001		James Hoog	70002	lowest on	2012-10-05
5001		James Hoog	70005	highest on	2012-07-27
5001		James Hoog	70005	lowest on	2012-07-27
5001		James Hoog	70008	highest on	2012-09-10
5001		James Hoog	70013	highest on	2012-04-25
5001		James Hoog	70013	lowest on	2012-04-25
5002		Nail Knite	70001	highest on	2012-10-05
5002		Nail Knite	70012	highest on	2012-06-27
5002		Nail Knite	70012	lowest on	2012-06-27
5003		Lauson Hen	70003	highest on	2012-10-10
5003		Lauson Hen	70004	highest on	2012-08-17
5005		Pit Alex	70009	lowest on	2012-09-10
5006		Mc Lyon		70010	lowest on	2012-10-10
5007		Paul Adam	70011	lowest on	2012-08-17

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to display all the salesmen and customer involved in this inventory management system.
Next: Write a query to make a report of which salesman produce the largest and smallest orders on each date and arranged the orders number in smallest to the largest number.