#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122
#Lecture: Union and subqueries:


	Tables in used: BookOrder_Details

	Structure of BookOrder_Details table:

	+-----------+---------------+------+-----+---------+-------+
	| Field     | Type          | Null | Key | Default | Extra |
	+-----------+---------------+------+-----+---------+-------+
	| OrderID   | varchar(10)   |      |     |         |       |
	| ISBN      | varchar(13)   |      |     |         |       |
	| UnitPrice | decimal(10,2) | YES  |     | NULL    |       |
	| Qty       | mediumint(9)  | YES  |     | NULL    |       |
	| Discount  | decimal(5,2)  | YES  |     | NULL    |       |
	+-----------+---------------+------+-----+---------+-------+


	Purpose: To compute the amount of each sub-item for each Order id in the table 
		BookOrder_Details. This amount should reflect the discount included, if
		there is any from the 'Discount' field.
		Once this is done, then sum up the subtotal for each order id. Since there
		may be more than one item in each order id, then we have to sum up the 
		subtotal for each of these order id.
		One way to do this is to create some temporary tables (or sub-queries, or 
		VIEWS, if available)
		then UNION the results of these tables to get the subtotal for each order id.


	select OrderID, UnitPrice*Qty - UnitPrice*Qty*Discount AS Amount from BookOrder_Details;

	+---------+----------+
	| OrderID | Amount   |
	+---------+----------+
	| 1       |   490.00 |
	| 1       |   477.82 |
	| 2       |   238.00 |
	| 2       |     8.50 |
	| 2       |   382.50 |
	| 3       |    90.25 |
	| 3       |   133.00 |
	| 3       |   185.25 |
	| 3       |  2194.50 |
		.
		.
		.


	select OrderID,sum(UnitPrice*Qty - UnitPrice*Qty*Discount) AS Subtotal 
	   from 
		BookOrder_Details 
	   group by OrderID;

	+---------+----------+
	| OrderID | Subtotal |
	+---------+----------+
	| 1       |   967.82 |
	| 10      |   649.00 |
	| 100     |   110.00 |
	| 101     |   458.75 |
	| 102     |   471.20 |
	| 103     |  4441.25 |
	| 104     |  1659.53 |
	| 105     |    74.40 |
	| 106     |   677.00 |
	| 107     |  1762.70 |
	| 108     |   781.00 |
	| 11      |  1078.69 |
		.
		.
		.

	- Create the table 'amount' to contain the amount (with discount) and the OrderID
	from the BookOrder_Details table. For demo purpose we will select the first 10 
	Order id's.

	create temporary table amount as  
	   select OrderID,UnitPrice*Qty-UnitPRice*Qty*Discount AS Amount 
		from BookOrder_Details 
		where OrderID <= 10;

	select * from amount;
	+---------+---------+
	| OrderID | Amount  |
	+---------+---------+
	| 1       |  490.00 |
	| 1       |  477.82 |
	| 2       |  238.00 |
	| 2       |    8.50 |
	| 2       |  382.50 |
	| 3       |   90.25 |
	| 3       |  133.00 |
	| 3       |  185.25 |
	| 3       | 2194.50 |
		.
		.
		.

	- Now, we create a 2nd table from the BookOrder_Details tbale to compute the 
	subtotal of each Order id.

	create temporary table amount2 as  
	   select OrderID,sum(UnitPrice*Qty - UnitPrice*Qty*Discount) AS Subtotal 
		from BookOrder_Details 
		where OrderID <= 10
		group by ORderID;

	 select * from amount2;

	+---------+----------+
	| OrderID | Subtotal |
	+---------+----------+
	| 1       |   967.82 |
	| 10      |   649.00 |
	| 2       |   629.00 |
	| 3       |  2603.00 |
	| 4       |  2984.00 |
	| 5       |   625.00 |
	| 6       |  2966.50 |
	| 7       |  2227.89 |
	| 8       |   660.00 |
	| 9       |  2048.21 |
	+---------+----------+
	10 rows in set (0.01 sec)

	
	- Now let's put the result of these 2 tables together. The reason for adding the 
	'Subtotal' is two folds: 
	   (a) To clarify the display.
	   (b) To be able to arrange the records in accord with the Order id.

	select * from amount  
	UNION  
	select concat(OrderID,'-Subtotal'),Subtotal 
	   from amount2 
	   order by 1,2;
	
	+-------------+---------+
	| OrderID     | Amount  |
	+-------------+---------+
	| 1           |  477.82 |
	| 1           |  490.00 |
	| 1-Subtotal  |  967.82 |
	| 10          |   60.00 |
	| 10          |  150.00 |
	| 10          |  439.00 |
	| 10-Subtotal |  649.00 |
	| 2           |    8.50 |
	| 2           |  238.00 |
	| 2           |  382.50 |
	| 2-Subtotal  |  629.00 |
	| 3           |   90.25 |
	| 3           |  133.00 |
	| 3           |  185.25 |
	| 3           | 2194.50 |
	| 3-Subtotal  | 2603.00 |
		.
		.	
		.


	- Now create a 3rd table with the ISBN field included. This is just to show you
	how we can create an empty field in the SELECT-statement in order to match the
	missing field from another table when we UNION these tables together.

	create temporary table amount3 as  
	   select OrderID,UnitPrice*Qty-UnitPRice*Qty*Discount AS Amount,ISBN 
		from BookOrder_Details 
		where OrderID in (1,2,3,4,5,6,7,8,9,10);


	select * from amount3;

	+---------+---------+---------------+
	| OrderID | Amount  | ISBN          |
	+---------+---------+---------------+
	| 1       |  490.00 | 8-91-335678-7 |
	| 1       |  477.82 | 0-11-345678-9 |
	| 2       |  238.00 | 0-222-45678-9 |
	| 2       |    8.50 | 0-123-45678-0 |
	| 2       |  382.50 | 0-12-345678-9 |
	| 3       |   90.25 | 0-321-32132-1 |
	| 3       |  133.00 | 0-55-123456-9 |
			.
			.
			.

	- Now we UNION the amount3 and amount3 tables. Note that the amount2 table does
	not contain the field ISBN. Therefore we create a blank field so that we can
	UNION these fields together.

	select OrderID,ISBN,Amount 
	   from amount3  
	UNION  
	select concat(OrderID,'-Subtotal'),' ',Subtotal 
	   from amount2 
	   order by 1,2;

	+-------------+---------------+---------+
	| OrderID     | ISBN          | Amount  |
	+-------------+---------------+---------+
	| 1           | 0-11-345678-9 |  477.82 |
	| 1           | 8-91-335678-7 |  490.00 |
	| 1-Subtotal  |               |  967.82 |
	| 10          | 0-12-333433-3 |   60.00 |
	| 10          | 0-222-45777-9 |  439.00 |
	| 10          | 0-222-45999-9 |  150.00 |
	| 10-Subtotal |               |  649.00 |
	| 2           | 0-12-345678-9 |  382.50 |
	| 2           | 0-123-45678-0 |    8.50 |
	| 2           | 0-222-45678-9 |  238.00 |
	| 2-Subtotal  |               |  629.00 |
	| 3           | 0-321-32132-1 |   90.25 |
	| 3           | 0-55-123456-9 |  133.00 |
	| 3           | 0-555-55555-9 |  185.25 |
	| 3           | 0-91-045678-5 | 2194.50 |
	| 3-Subtotal  |               | 2603.00 |
			.	
			.
			.