/* QUESTION 1 For each order placed in 1998, list the customer name, order number and the date. */
SELECT LAST, FIRST, ORDER_NUMBER, ORDER_DATE
  	 FROM CUSTOMER, ORDERS
  WHERE CUSTOMER.CUSTOMER_NUMBER = ORDERS.CUSTOMER_NUMBER
  	 AND ORDER_DATE LIKE '%98';

/* QUESTION 2  How many customers from outside of Michigan (MI) have placed orders? */
SELECT COUNT (*) NOT_MICHIGAN
 	  FROM CUSTOMER, ORDERS
  	 WHERE STATE <> 'MI'
  	 AND CUSTOMER.CUSTOMER_NUMBER = ORDERS.CUSTOMER_NUMBER;

/* QUESTION 3  How many orders placed prior to Jan. 1, 2000 were never shipped? */
SELECT COUNT (*) UNSHIPPED_ORDERS
  	 FROM ORDERS
  	 WHERE ORDER_DATE < TO_DATE ('01-JAN-00')
 	 AND SHIP_DATE IS NULL;

/* QUESTION 4  List all the sales reps and the names and addresses of their customers. */
SELECT SLSREP_NUMBER, LAST, FIRST, STREET, CITY, STATE, ZIP_CODE
  	 FROM CUSTOMER
  	 ORDER BY SLSREP_NUMBER;

/* QUESTION 5  List each customer and the total amount purchased? (That's the sum of their purchases not their 
balance) */
SELECT CUSTOMER_NUMBER, SUM(NUMBER_ORDERED*QUOTED_PRICE)  
TOTAL_PURCHASES
  FROM ORDERS, ORDER_LINE
 	 WHERE ORDERS.ORDER_NUMBER = ORDER_LINE.ORDER_NUMBER
	GROUP BY CUSTOMER_NUMBER;

/* QUESTION 6  List the names of parts that were never purchased. */
SELECT PART_DESCRIPTION
 	 FROM PART
  	 WHERE PART.PART_NUMBER NOT IN
  		(SELECT PART_NUMBER
 		FROM ORDER_LINE);


/* QUESTION 7  Which part (description) did we sell the most of? */

CREATE OR REPLACE VIEW NUMBERSOLD AS
(SELECT PART_NUMBER, SUM(NUMBER_ORDERED) AMOUNT
FROM ORDER_LINE
GROUP BY PART_NUMBER;

SELECT PART_DESCRIPTION PART
 FROM PART, NUMBERSOLD
 WHERE PART.PART_NUMBER = NUMBERSOLD.PART_NUMBER
 	 AND AMOUNT = 
(SELECT MAX(AMOUNT)
 		  FROM NUMBERSOLD);