
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.
What is the difficulty level of this exercise?
New Content: Composer: Dependency manager for PHP, R Programming