/********************************************************************************/
/*																				*/
/*	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 MySQL 5.7 SQL code solutions									*/
/*																				*/
/********************************************************************************/


/* NOTE, DO NOT RUN THESE ALL AT ONCE, WE INSERT SOME , ALTER A TABLE THEN INSERT MORE */

/* Run in correct order, See App E for details. */
/***** PRODUCTION_ITEM DATA *************************************************/
INSERT INTO PRODUCTION_ITEM VALUES( 
150102001, 'Alpha II, Black', '2014-10-15', '2015-11-30', 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150102005, 'Alpha II, White', '2014-11-15', '2015-10-31', 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150201001, 'Bravo I, Black', '2014-12-15', '2015-11-30', 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150201005, 'Bravo I, White', '2014-12-15', '2015-11-30', 0, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150303001, 'Delta III, Black', '2015-1-15', '2016-1-02', 5, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
150303005, 'Delta III, White', '2015-1-15', '2016-1-02', 15, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160103001, 'Alpha III, Black', '2015-11-15', NULL, 100, 100); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160103005, 'Alpha III, White', '2015-11-15', NULL, 100, 0); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160202001, 'Bravo II, Black', '2015-12-15', NULL, 200, 100); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160202005, 'Bravo II, White', '2015-12-15', NULL, 150, 50); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160304001, 'Delta IV, Black', '2016-1-15', NULL, 300, 200); 
INSERT INTO PRODUCTION_ITEM VALUES( 
160304005, 'Delta IV, White', '2016-1-15', NULL, 200, 100);

/***** CATALOG_SKU_2015 *****************************************************/
/* insert of null gives next auto_increment value in MySQL */

INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150102001, 'Our low price Alpha II model in black.', 10, '2015-1-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150102005, 'Our low price Alpha II model in white.', 12, '2015-1-01');
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150201001, 'Our new Bravo I model in black.', 18, '2015-1-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150201005, 'Our new Bravo I model in white.', 20, '2015-1-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150303001, 'Our high performance Delta III model in black.', 24, '2015-1-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,150303005, 'Our high performance Delta III model in white.', 26, '2015-1-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,160103001, 'New, updated Alpha III model in black.', NULL, '2015-12-01'); 
INSERT INTO CATALOG_SKU_2015 VALUES( 
null,160103005, 'New, updated Alpha III model in white.', NULL, '2015-12-01');

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

INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160103001, 'Our low price Alpha III model in black.', 10, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160103005, 'Our low price Alpha III model in white.', 11, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160202001, 'Our new Bravo II model in black.', 18, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160202005, 'Our new Bravo II model in white.', 17, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160304001, 'Our high performance Delta IV model in black.', 22, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2016 VALUES( 
null,160304005, 'Our high performance Delta IV model in white.', 23, '2016-1-01');

/*  PRODUCTION_ITEM_DATA */
/* Run at appropriate time, see App E for details. */

INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) 
VALUES(160103001, '2016-10-31'); 
INSERT INTO PRODUCTION_ITEM_DATA(SKU, ProductionEndDate) 
VALUES(160103005, '2016-10-31'); 
INSERT INTO PRODUCTION_ITEM_DATA 
VALUES( 170104001, 'Alpha IV, Black', '2016-11-15', NULL, 0, 200, '2016-10-15'); 
INSERT INTO PRODUCTION_ITEM_DATA 
VALUES( 170104005, 'Alpha IV, White', '2016-11-15', NULL, 0, 200, '2016-10-15');

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

INSERT INTO PRODUCTION_ITEM VALUES( 
170104001, 'Alpha IV, Black', '2016-11-15', NULL, 50, 200, '2016-10-15'); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170104005, 'Alpha IV, White', '2016-11-15', NULL, 50, 200, '2016-10-15'); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203001, 'Bravo III, Black', '2016-12-15', NULL, 100, 250, '2016-11-15'); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203005, 'Bravo III, White', '2016-12-15', NULL, 100, 250, '2016-11-15');  

/***** PRODUCTION_ITEM DATA FOR Exercise E.55 (with MERGE) *************************************************/
/* run these before the next inserts set */
UPDATE PRODUCTION_ITEM 
	SET QuantityOnHand = 50
	WHERE SKU = 170104001 OR SKU = 170104005; 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203001, 'Bravo III, Black', '2016-12-15', NULL, 100, 250, '2016-11-15'); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203005, 'Bravo III, White', '2016-12-15', NULL, 100, 250, '2016-11-15');

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

INSERT INTO CATALOG_SKU_2017 VALUES( 
null,170104001, 'Our low price Alpha IV model in black.', 9, '2017-1-01'); 
INSERT INTO CATALOG_SKU_2017 VALUES( 
null,170104005, 'Our low price Alpha IV model in white.', 10, '2017-1-01'); 
INSERT INTO CATALOG_SKU_2017 VALUES( 
null,170203001, 'Our popular Bravo III model in black.', 15, '2017-1-01'); 
INSERT INTO CATALOG_SKU_2017 VALUES( 
null,170203005, 'Our popular Bravo III model in white.', 16, '2017-1-01'); 
INSERT INTO CATALOG_SKU_2017 VALUES( 
null,160304001, 'Our high performance Delta IV model in black.', 20, '2016-1-01'); 
INSERT INTO CATALOG_SKU_2017 VALUES( 
null,160304005, 'Our high performance Delta IV model in white.', 22, '2016-1-01');

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

