/********************************************************************************/
/*																				*/
/*	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 Microsoft SQL Server 2014/2016 SQL code solutions				*/
/*																				*/
/********************************************************************************/

USE WP3
GO

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

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

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

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

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

/*  PRODUCTION_ITEM_DATA */

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

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

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

/***** 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', '15-DEC-16', NULL, 100, 250, '15-NOV-16'); 
INSERT INTO PRODUCTION_ITEM VALUES( 
170203005, 'Bravo III, White', '15-DEC-16', NULL, 100, 250, '15-NOV-16');

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

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

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

