Written by Jen Chen
Copyrighted by Jen Chen

Lesson 5
   - Database: Your own.
   - Table: (i)  Orders2 (based on the data of the file "Orders2Modified.xls")
            (ii) Shipping (based on the data of the file "Shipping.xls")
   *Task: To sum the shipping for 2 states: 'CA' and 'GA'.
	  Display the Carrier's Id, State, Country, and the sum of the shipping on the
	result set.

Solution:

   select   O.CarrierId, O.ShipStateOrProvince, O.ShipCountry, sum(S.ShippingCharge)
     from
     Orders O,
     Shipping S
   where
     O.CarrierId = S.CarriersId
     AND O.ShipCountry='USA'
     AND O.ShipStateOrProvince in( 'GA','CA')
     AND O.ShipStateOrProvince = S.ShipStateOrProvince
     GROUP BY O.CarrierId, O.ShipStateOrProvince, O.ShipCountry;


======================
/* The use of "UNION" to display all ALL records, excluding duplicates on the result
set
*/

   select   O.CarrierId, O.ShipStateOrProvince, O.ShipCountry, S.ShippingCharge
     from
       Orders O,
       Shipping S
   where
     O.CarrierId = S.CarriersId
     AND O.ShipCountry='USA'
     AND O.ShipStateOrProvince ='CA'
     AND O.ShipStateOrProvince = S.ShipStateOrProvince
   UNION
   select   O.CarrierId, O.ShipStateOrProvince, O.ShipCountry, S.ShippingCharge
     from
       Orders O,
       Shipping S
   where
     O.CarrierId = S.CarriersId
     AND O.ShipCountry='USA'
     AND O.ShipStateOrProvince ='GA'
     AND O.ShipStateOrProvince = S.ShipStateOrProvince

=======================
/* Compare this result with the result above to see the difference between "UNION" and
"UNION ALL" */

   select   O.CarrierId, O.ShipStateOrProvince, O.ShipCountry, S.ShippingCharge
     from
       Orders O,
       Shipping S
   where
     O.CarrierId = S.CarriersId
     AND O.ShipCountry='USA'
     AND O.ShipStateOrProvince ='CA'
     AND O.ShipStateOrProvince = S.ShipStateOrProvince
   UNION ALL
   select   O.CarrierId, O.ShipStateOrProvince, O.ShipCountry, S.ShippingCharge
     from
       Orders O,
       Shipping S
   where
     O.CarrierId = S.CarriersId
     AND O.ShipCountry='USA'
     AND O.ShipStateOrProvince ='GA'
     AND O.ShipStateOrProvince = S.ShipStateOrProvince