INF 282 Dewm Quiz 2

SELECT o.Order_ID, c.* FROM
    stephensow1_Order o, stephensow1_Customer c
    WHERE o.Customer_ID = c.Customer_ID
    AND LOWER(c.Address_Line1) LIKE 'tom''s hardware';

SELECT o.Date_Ordered, c.Customer_ID, c.Address_Line1, c.Address_Line3
    FROM stephensow1_Order o, stephensow1_Customer c
    WHERE o.Customer_ID = c.Customer_ID
    AND o.Date_Ordered >= '01-JUN-2011'
    AND o.Date_Ordered <= '07-JUN-2011';

SELECT SUM(i.Unit_Price * p.Quantity), o.Customer_ID, c.Address_Line1
    FROM stephensow1_Purchase p, stephensow1_Item i,
    stephensow1_Order o, stephensow1_Customer c
    WHERE p.Item_ID = i.Item_ID AND p.Order_ID = o.Order_ID
    AND o.Customer_ID = c.Customer_ID
    GROUP BY p.Order_ID, o.Customer_ID, c.Address_Line1;

SELECT o.Order_ID, p.Quantity, (SELECT SUM(p.Quantity)
        FROM stephensow1_Purchase p, stephensow1_Item i, stephensow1_Order o
        WHERE p.Item_ID = i.Item_ID AND p.Order_ID = o.Order_ID AND i.Description LIKE 'Red Widgets') AS TOTAL
    FROM stephensow1_Purchase p, stephensow1_Item i, stephensow1_Order o
    WHERE p.Item_ID = i.Item_ID AND p.Order_ID = o.Order_ID AND i.Description LIKE 'Red Widgets'
    GROUP BY o.Order_ID, p.Quantity;