-- One query to satisfy each of the 4 system requirements in
-- Comp2400/Comp6240 2007 Assignment 1


-- 1. Do we have enough stock to fill today's orders?  ie. list
-- productID's of the products of which we have insufficient stock

SELECT productID AS insufficientStockProduct
FROM productStockOrdered
WHERE stock < ordered;


-- 2. Assuming orders in the coming days are the same as todays, how
-- many days stock do we have of each product?

SELECT productID, stock/ordered as estimatedDaysOfStock
FROM productStockOrdered;


-- 3. How many orders for each of the types of packaging?

SELECT package, count(*) as numberOfOrders
FROM orderPackage
GROUP BY package;

-- 4. How many person-hours will it take to pick and pack the orders?

SELECT sum(picktime + packtime) AS personHours
FROM orderDerived;