/******************************************************************************/
/*																				                                    */
/*	Kroenke, Auer, Vandeberg, and Yoder 				*/
/*	Database Concepts (8th Edition) Chapters 06, 07, 08		*/
/*																				                                    */
/*	Marcia's Dry Cleaning Project Alter Table and View 									              */
/*																				                                    */
/*	These are the Oracle Express 11g R2 SQL code solutions	                    */
/*																				                                    */
/******************************************************************************/


/*****   Case Question  7.E   *************************************************/

ALTER TABLE INVOICE 
	ADD	Status		Char(12)	NULL;

ALTER TABLE INVOICE 
	ADD	CONSTRAINT	StatusValueCheck	CHECK
		(Status IN ('Waiting', 'In-process', 'Finished', 'Pending'));


/* Assume all current invoices with a date out have status "Finished"; all others have status "In-process" */

UPDATE INVOICE 
	SET  	Status = 'Finished'
	WHERE	DateOut IS NOT NULL;

UPDATE INVOICE 
	SET  	Status = 'In-process'
	WHERE	DateOut IS NULL;
    
/*****   Project Question  7.F   ************************************************/
/******  Create View  ***********************************************************/

CREATE VIEW 	CustomerInvoiceView AS
	SELECT 		C.LastName, C.FirstName, C.Phone, I.InvoiceNumber,
				    I.DateIn, I.DateOut, I.TotalAmount, I.Status
	FROM 		  CUSTOMER C, INVOICE I
	WHERE		  C.CustomerID = I.CustomerID;

/******  Query View *************************************************************/

SELECT      *
FROM        CustomerInvoiceView
ORDER BY    InvoiceNumber;


/*****   Project Question  8.C   ************************************************/
/******  Insert New Data ********************************************************/
/*  The question suggests adding 11 new invoices, but three more will suffice */

INSERT INTO INVOICE (InvoiceNumber, CustomerID, DateIn, Subtotal, Tax, TotalAmount, Status)
    VALUES(
		2017010, 110, TO_DATE('10/14/17', 'MM/DD/YY'), 7.00, 0.55, 7.55, 'In-process');
INSERT INTO INVOICE VALUES(
		2017011, 130, TO_DATE('10/15/17', 'MM/DD/YY'), NULL, 140.50, 11.10, 151.60, 'In-process');
INSERT INTO INVOICE VALUES(
		2017012, 120, TO_DATE('10/17/17', 'MM/DD/YY'), NULL, 27.00, 2.13, 29.13, 'Waiting');

INSERT INTO INVOICE_ITEM VALUES(2017020, 1, 16, 2,  3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2017021, 1, 16, 3,  3.50, 10.50);
INSERT INTO INVOICE_ITEM VALUES(2017021, 2, 11, 12, 2.50, 30.00);
INSERT INTO INVOICE_ITEM VALUES(2017021, 3, 20, 8,  5.00, 40.00);
INSERT INTO INVOICE_ITEM VALUES(2017021, 4, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2017022, 1, 40, 3,  9.00, 27.00);



/********************************************************************************/













