/* let's take a look at what we did */

select * from `Order`;
select * from Product;
select * from LineItem;


/* Now -- the challenge -- can you write a statement that ... */

/* #1 - Shows the order number, product ID number and quantity in order number 1000 */
select orderNumber, productID, quantity from LineItem where orderNumber = 1000; 

/* #2 - Shows all the same information as #1 and also includes product names and prices */
select LineItem.orderNumber, LineItem.productID, quantity, productName, productPrice 
from 
Product, LineItem
where 
Product.productID = LineItem.productID AND 
LineItem.orderNumber = 1000;

/* #3 - shows all the same information as #2 and also includes the customer number who made the order in the first place? */
select `Order`.customerID, LineItem.orderNumber, LineItem.productID, quantity, productName, productPrice from 
Product, LineItem, `Order`
where 
Product.productID = LineItem.productID AND 
`Order`.orderNumber = LineItem.orderNumber AND 
LineItem.orderNumber = 1000;


/* #3b - what happens if i leave off the join criteria.  Is that bad??? */
select Order.customerID, LineItem.orderNumber, LineItem.productID, quantity, productName, productPrice from 
Product, LineItem, `Order`
where 
LineItem.orderNumber = 1000;

/* #4 - can you improve on query #3 to show the amount the customer should be charged for each line item in the order? */
select `Order`.customerID, LineItem.orderNumber, LineItem.productID, quantity, productName, productPrice,
productPrice * quantity as charge  
from 
Product, LineItem, `Order`
where 
Product.productID = LineItem.productID AND 
`Order`.orderNumber = LineItem.orderNumber AND 
LineItem.orderNumber = 1000;