/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				*/
/*	Wedgewood Pacific (WP) Insert Data FOR NEW APPENDIX E TABLES ONLY										*/
/*																				*/
/*	These are the Oracle Database XE SQL code solutions				*/
/*																				*/
/********************************************************************************/



/***** PRODUCTION_ITEM DATA *************************************************/
INSERT INTO PRODUCTION_ITEM VALUES( 
150102001, 'Alpha II, Black', TO_DATE('15-OCT-14','DD-MON-YY'), TO_DATE('30-NOV-15','DD-MON-YY'), 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150102005, 'Alpha II, White', TO_DATE('15-NOV-14','DD-MON-YY'), TO_DATE('31-OCT-15','DD-MON-YY'), 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150201001, 'Bravo I, Black', TO_DATE('15-DEC-14','DD-MON-YY'), TO_DATE('30-NOV-15','DD-MON-YY'), 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150201005, 'Bravo I, White', TO_DATE('15-DEC-14','DD-MON-YY'), TO_DATE('30-NOV-15','DD-MON-YY'), 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150303001, 'Delta III, Black', TO_DATE('15-JAN-15','DD-MON-YY'), TO_DATE('02-JAN-16','DD-MON-YY'), 5, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150303005, 'Delta III, White', TO_DATE('15-JAN-15','DD-MON-YY'), TO_DATE('02-JAN-16','DD-MON-YY'), 15, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160103001, 'Alpha III, Black', TO_DATE('15-NOV-15','DD-MON-YY'), NULL, 100, 100); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160103005, 'Alpha III, White', TO_DATE('15-NOV-15','DD-MON-YY'), NULL, 100, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160202001, 'Bravo II, Black', TO_DATE('15-DEC-15','DD-MON-YY'), NULL, 200, 100); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160202005, 'Bravo II, White', TO_DATE('15-DEC-15','DD-MON-YY'), NULL, 150, 50); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160304001, 'Delta IV, Black', TO_DATE('15-JAN-16','DD-MON-YY'), NULL, 300, 200); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160304005, 'Delta IV, White', TO_DATE('15-JAN-16','DD-MON-YY'), NULL, 200, 100);

/***** CATALOG_SKU_2015 *****************************************************/

INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150102001, 'Our low price Alpha II model in black.', 10, TO_DATE('01-JAN-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150102005,'Our low price Alpha II model in white.', 12, TO_DATE('01-JAN-15','DD-MON-YY'));
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150201001,'Our new Bravo I model in black.', 18, TO_DATE('01-JAN-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150201005,'Our new Bravo I model in white.', 20, TO_DATE('01-JAN-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150303001,'Our high performance Delta III model in black.', 24, TO_DATE('01-JAN-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
150303005,'Our high performance Delta III model in white.', 26, TO_DATE('01-JAN-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
160103001,'New, updated Alpha III model in black.', NULL, TO_DATE('01-DEC-15','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2015 VALUES( seqCat15ID.NextVal,
160103005,'New, updated Alpha III model in white.', NULL, TO_DATE('01-DEC-15','DD-MON-YY'));

/***** CATALOG_SKU_2016 *****************************************************/

INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160103001, 'Our low price Alpha III model in black.', 10, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160103005, 'Our low price Alpha III model in white.', 11, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160202001, 'Our new Bravo II model in black.', 18, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160202005, 'Our new Bravo II model in white.', 17, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160304001, 'Our high performance Delta IV model in black.', 22, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2016 VALUES( seqCat16ID.NextVal,
160304005, 'Our high performance Delta IV model in white.', 23, TO_DATE('01-JAN-16','DD-MON-YY'));

/*  PRODUCTION_ITEM_DATA */

INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) 
VALUES(160103001, TO_DATE('31-OCT-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) 
VALUES(160103005, TO_DATE('31-OCT-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM_DATA 
VALUES( 170104001, 'Alpha IV, Black', TO_DATE('15-NOV-16','DD-MON-YY'), NULL, 0, 200, 
	TO_DATE('15-OCT-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM_DATA 
VALUES( 170104005, 'Alpha IV, White', TO_DATE('15-NOV-16','DD-MON-YY'), NULL, 0, 200, 
	TO_DATE('15-OCT-16','DD-MON-YY'));

/***** PRODUCTION_ITEM DATA FOR Exercise E.55 (no MERGE) *************************************************/

INSERT INTO PRODUCTION_ITEM VALUES( 
170104001, 'Alpha IV, Black', TO_DATE('15-NOV-16','DD-MON-YY'), NULL, 50, 200, 
	TO_DATE('15-OCT-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170104005, 'Alpha IV, White', TO_DATE('15-NOV-16','DD-MON-YY'), NULL, 50, 200, 
	TO_DATE('15-OCT-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203001, 'Bravo III, Black', TO_DATE('15-DEC-16','DD-MON-YY'), NULL, 100, 250, 
	TO_DATE('15-NOV-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203005, 'Bravo III, White', TO_DATE('15-DEC-16','DD-MON-YY'), NULL, 100, 250, 
	TO_DATE('15-NOV-16','DD-MON-YY'));  

/***** PRODUCTION_ITEM DATA FOR Exercise E.55 (with MERGE) *************************************************/

UPDATE PRODUCTION_ITEM 
	SET QuantityOnHand = 50
	WHERE SKU = 170104001 OR SKU = 170104005; 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203001, 'Bravo III, Black', TO_DATE('15-DEC-16','DD-MON-YY'), NULL, 100, 250, 
	TO_DATE('15-NOV-16','DD-MON-YY')); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203005, 'Bravo III, White', TO_DATE('15-DEC-16','DD-MON-YY'), NULL, 100, 250, 
	TO_DATE('15-NOV-16','DD-MON-YY'));  

/***** CATALOG_SKU_2017 DATA FOR Exercise E.57 *************************************************/

INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
170104001, 'Our low price Alpha IV model in black.', 9, TO_DATE('01-JAN-17','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
170104005, 'Our low price Alpha IV model in white.', 10, TO_DATE('01-JAN-17','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
170203001, 'Our popular Bravo III model in black.', 15, TO_DATE('01-JAN-17','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
170203005, 'Our popular Bravo III model in white.', 16, TO_DATE('01-JAN-17','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
160304001, 'Our high performance Delta IV model in black.', 20, TO_DATE('01-JAN-16','DD-MON-YY')); 
INSERT INTO CATALOG_SKU_2017 VALUES( seqCat17ID.NextVal,
160304005, 'Our high performance Delta IV model in white.', 22, TO_DATE('01-JAN-16','DD-MON-YY'));

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

