/*
CS 122
Spring 2004
Lesson 3: Aggregate functions and UNION.
Author: Jen Chen
Copyrighted by Jen Chen

   - DataBase: inventory.mdb
   - Table: Skids2.
   Task: TO find out the maximal value from the column CURRENTVALUE.
   Solution: Use the MAX() funxtion.
*/

SELECT MAX(CURRENTVALUE) AS MAX
FROM
   Skids2
WHERE
   OrderNumber = '3343';
======================================

/* - DataBase: inventory.mdb
   - Table: Skids2.
   Task: To find out the number of dayss elapsed between two given dates.
   Solution: Use the function DATEDIFF() with the option 'd'.
*/

SELECT ORDERNUMBER, PONUMBER, DATEDIFF('d',#04-01-2003#,#04-08-2004#)
FROM
   Skids2
WHERE
   ORDERNUMBER='2333';

=======================================

/* - DataBase: inventory.mdb
   - Table: Skids2.
   Task: To find out the number of weeks elapsed between two given dates.
   Solution: Use the function DATEDIFF() with the option 'w'.
*/

SELECT ORDERNUMBER, PONUMBER, DATEDIFF('w',#04-01-2003#,#04-08-2004#)
FROM
   Skids2
WHERE
   ORDERNUMBER='2333';

=======================================
/* - DataBase: inventory.mdb
   - Table: Skids2, Supplier.
   Task: Find out all Suppliers whose ID's are 2, 3, and 5 and the orders are
         placed such that the quantity is > 0.
         Give 5% deduction to all orders where the quantity is >= 3000.
*/

SELECT   Su.Supplier, S2.PONUMBER, S2.QUANTITY, S2.CURRENTVALUE,
ROUND(S2.QUANTITY*S2.CURRENTVALUE - S2.QUANTITY*S2.CURRENTVALUE*0.05,2) AS [TOTAL(DISC)]
FROM
   Skids2 S2,
   Supplier Su
WHERE
   S2.SupplierID = Su.SupplierID
   AND S2.SupplierId  IN (2,3,5)
   AND S2.QUANTITY > 0
   AND S2.QUANTITY >= 3000
   ORDER BY QUANTITY;

======================================
/* - DataBase: inventory.mdb
   - Table: Skids2, Supplier.
   Task: Find out all Suppliers whose ID's are 2, 3, and 5 and the orders are
         placed such that the quantity is > 0.
	 Print out the value of the quantity*CurrentValue, where the quantity is less
	 than 3000, then round the result to
	 2 decimals.
*/

SELECT   Su.Supplier, S2.PONUMBER, S2.QUANTITY, S2.CURRENTVALUE,
ROUND(S2.QUANTITY*S2.CURRENTVALUE, 2) AS [TOTAL(DISC)]
FROM
   Skids2 S2,
   Supplier Su
WHERE
   S2.SupplierID = Su.SupplierID
   AND S2.SupplierId  IN (2,3,5)
   AND S2.QUANTITY > 0
   AND S2.QUANTITY < 3000
   ORDER BY QUANTITY;

======================================

/* - DataBase: inventory.mdb
   - Table: Skids2, Supplier.
   Task: To print out records with the following conditions:
       (i) If the quantity ordered is < 3000, then there is no discount.
       (ii) If the quantity ordered is >= 3000 then give 5% discount to the
	total purchase.
   Solution: Combine the above two result sets using Union!
*/

SELECT   Su.Supplier, S2.PONUMBER, S2.QUANTITY, S2.CURRENTVALUE,
ROUND(S2.QUANTITY*S2.CURRENTVALUE - S2.QUANTITY*S2.CURRENTVALUE*0.05,2) AS [TOTAL(DISC)]
FROM
   Skids2 S2,
   Supplier Su
WHERE
   S2.SupplierID = Su.SupplierID
   AND S2.SupplierId  IN (2,3,5)
   AND S2.QUANTITY > 0
   AND S2.QUANTITY >= 3000
   ORDER BY QUANTITY
UNION
SELECT   Su.Supplier, S2.PONUMBER, S2.QUANTITY, S2.CURRENTVALUE,
ROUND(S2.QUANTITY*S2.CURRENTVALUE, 2) AS [TOTAL(DISC)]
FROM
   Skids2 S2,
   Supplier Su
WHERE
   S2.SupplierID = Su.SupplierID
   AND S2.SupplierId  IN (2,3,5)
   AND S2.QUANTITY > 0
   AND S2.QUANTITY < 3000
   ORDER BY QUANTITY;

=======================================

/* - DataBase: Student.mdb
   - Table: Section
  Task: To find the total number of classes taught by Instructors Hillary and
        Rodriguez in 1998 AND 1999.
  Solution: Find out how many classes Hillary taught in '98 and '99 then find
            out how many classes taught by Rodriguez in '98 and '99, then Union
 	    these results.
*/


select INSTRUCTOR, count(COURSE_NUM)
  from
     SECTION
  where
     INSTRUCTOR='RODRIGUEZ'
     AND YEAR IN('98','99')
     GROUP BY INSTRUCTOR
UNION
select INSTRUCTOR, count(COURSE_NUM)
  from
     SECTION
  where
     INSTRUCTOR='HILLARY'
     AND YEAR IN('98','99')
     GROUP BY INSTRUCTOR;

======================================