Possible Solutions for Hw3 
** See the clarification for e and g

a. List the Order# and Ship_date for all orders shipped from Warehouse# "W2".

          select Order#, Ship_date 
        from Shipment 
        where Warehouse# = 'W2'

b. List the Warehouse information from which the Customer named "Jose Lopez" was
supplied his orders. Produce a listing of Order#, Warehouse#.

        select Order.Order#, Shipment.Warehouse# 
        from Customer, Shipment, Order
        where Order.Order# = Shipment.Order#
                and Customer.Cust# = Order.Cust#
                and Customer.Cname = 'Jose Lopez'

c. Produce a listing: Cname, #ofOrders, Avg_Order_Amt, where the middle column is
the total number of orders by the customer and the last column is the average order
amount for that customer. (Use aggregate functions -- Figure 2.29 on page 64 (5th
ed) is a good example for incorporating multiple aggregate functions on different
attributes but using the same attribute to group results))

        select Cname, count(*) as #ofOrders, avg(Ord_Amt) as Avg_Order_Amt
        from Customer, Order
        where Customer.Cust# = Order.Cust#
        group by Cname

d. List the Order# for orders that were shipped from warehouses in New York.

    select Shipment.Order#
    from Shipment, Warehouse 
    where Shipment.Warehouse# = Warehouse.Warehouse# 
                    and Warehouse.City = "New York"

e. Delete all orders for customer named "Jose Lopez" 

Note: the order is important; delete from Order last. 

    Delete from Order_Item 
    where Order# in (select Order.Order# 
                     from Customer, Order
                     where Customer.Cname = "Jose Lopez"
                                 and Customer.Cust# = Order.Cust#)

    Delete from Shipment 
    where Order# in (select Order.Order# 
                     from Customer, Order
                     where Customer.Cname = "Jose Lopez"
                                 and Customer.Cust# = Order.Cust#)

    Delete from Order 
    where Order# in (select Order.Order# 
                     from Customer, Order
                     where Customer.Cname = "Jose Lopez"
                                 and Customer.Cust# = Order.Cust#)

f. Move the shipping date by a week for all those orders originating from warehouses
in Baltimore.

    update Shipment
    set Ship_date = Ship_date + 7
    where Shipment.Warehouse# in (select Warehouse.Warehouse# from Warehouse where
City = "Baltimore")

g. List all items that have a price greater than the average price.

    select Item#
    from Item
    where Unit_Price > all (select avg(Unti_Price) as Unit_Price
                            from Item)                   

Note: we are using > all construct here and the word "all" is needed and not optional, 
even though the subquery results in a single value relation. 

h. Find out the maximum number of orders shipped out of warehouses in Baltimore.

	select max(Num_Orders)
        from (Select Warehouse#, count(#Orders)
              from Shipment as S, Warehouse as W
              where S.Warehouse# = W.Warehouse# and W.City = "Baltimore"
              group by Warehouse#) as WOrders (Warehouse#, Num_Orders)

i. Find the item with the maximum unit price.

    Select Item# 
    from Item
    where Unit_Price in (select max(Unit_Price) as Unit_Price
                         from Item)

j. List all customer names whose orders were shipped from a warehouse in the same
city as they live in.
 
    Select Cname 
    from Customer as C, Order as O, Shipment as S, Warehouse as W
    where C.Cust# = O.Cust# 
          and O.Order# = S.Order#
          and S.warehouse# = W.warehouse#
          and C.City = W.City