w3resource
SQL exercises

SQL UNION Exercises: Make a report of which salesman produce the largest and smallest orders on each date and arranged the order number in smallest to the largest number

SQL UNION: Exercise-5 with Solution

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

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))
ORDER BY 3

Sample Output:

salesman_id	name		ord_no	?column?	ord_date
5002		Nail Knite	70001	highest on	2012-10-05
5001		James Hoog	70002	lowest on	2012-10-05
5003		Lauson Hen	70003	highest on	2012-10-10
5003		Lauson Hen	70004	highest on	2012-08-17
5001		James Hoog	70005	lowest on	2012-07-27
5001		James Hoog	70005	highest on	2012-07-27
5001		James Hoog	70008	highest on	2012-09-10
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
5002		Nail Knite	70012	lowest on	2012-06-27
5002		Nail Knite	70012	highest on	2012-06-27
5001		James Hoog	70013	lowest on	2012-04-25
5001		James Hoog	70013	highest on	2012-04-25

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Make a report of which salesman produce the largest and smallest orders on each date and arranged the order number in smallest to the largest number - Duration

Rows:

Query visualization of Make a report of which salesman produce the largest and smallest orders on each date and arranged the order number in smallest to the largest number - Rows

Cost:

Query visualization of Make a report of which salesman produce the largest and smallest orders on each date and arranged the order number in smallest to the largest number - Cost

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

Previous: Write a query to make a report of which salesman produce the largest and smallest orders on each date.
Next: Write a query to list all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming