/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) CH03, App E			*/
/*																				*/
/*  James River Jewelry [JRJ] Database Data										*/
/*																				*/
/*	These are the MySQL 5.7						SQL code solutions				*/
/*																				*/
/********************************************************************************/

USE jrj;


/*****   CUSTOMER DATA   ********************************************************/
/* MySQL allows nulls to generate the next auto_increment value */
INSERT INTO CUSTOMER VALUES(
	null,'Stanley', 'Elizabeth','555-236-7789', 'Elizabeth.Stanley@somewhere.com');
INSERT INTO CUSTOMER VALUES(
	null,'Price', 'Fred', '555-236-0091', 'Fred.Price@somewhere.com');
INSERT INTO CUSTOMER VALUES(
	null,'Becky', 'Linda', '555-236-0392', 'Linda.Becky@somewhere.com');
INSERT INTO CUSTOMER VALUES(
	null,'Birch', 'Pamela', '555-236-4493', 'Pamela.Birch@somewhere.com');
INSERT INTO CUSTOMER VALUES(
	null,'Romez', 'Ricardo', '555-236-3334', 'Ricardo.Romez@somewhere.com');
INSERT INTO CUSTOMER VALUES(
	null,'Jackson', 'Samantha', '555-236-1095', 'Samantha.Jackson@somewhere.com');

-- SELECT * FROM CUSTOMER;

/*****   ITEM DATA   ************************************************************/
/* MySQL allows nulls to generate the next auto_increment value */

INSERT INTO ITEM VALUES(null,'Gold Bracelet', 120.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Gold Necklace', 160.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 50.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Gold Bracelet', 180.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES(null,'Silver Necklace', 135.00, 'Baxter', 'Sam');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 25.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 22.50, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Gold Earrings', 50.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES(null,'Gold Necklace', 160.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 20.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 35.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 45.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Gold Necklace', 225.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES(null,'Silver Earrings', 55.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES(null,'Gold Bracelet', 200.00, 'Lintz', 'John');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 25.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Bead Earrings', 45.00, 'Josephson', 'Mary');
INSERT INTO ITEM VALUES(null,'Gold Bracelet', 210.00, 'Baker', 'Samantha');
INSERT INTO ITEM VALUES(null,'Silver Necklace', 165.00, 'Baxter', 'Sam');

-- SELECT * FROM ITEM;

/*****   PURCHASE DATA   *******************************************************/
/* MySQL allows nulls to generate the next auto_increment value */

INSERT INTO PURCHASE VALUES(null,'2017-05-05', 155.00, 1);
INSERT INTO PURCHASE VALUES(null,'2017-05-07', 203.00, 2);
INSERT INTO PURCHASE VALUES(null,'2017-05-11', 75.00, 3);
INSERT INTO PURCHASE VALUES(null,'2017-05-15', 67.00, 4);
INSERT INTO PURCHASE VALUES(null,'2017-05-15', 330.00, 5);
INSERT INTO PURCHASE VALUES(null,'2017-05-16', 25.00, 1);
INSERT INTO PURCHASE VALUES(null,'2017-05-25', 45.00, 3);
INSERT INTO PURCHASE VALUES(null,'2017-06-06', 445.00, 1);
INSERT INTO PURCHASE VALUES(null,'2017-06-07', 72.00, 6);

-- SELECT * FROM PURCHASE;

/*****   PURCHASE_ITEM DATA   ***************************************************/

INSERT INTO PURCHASE_ITEM VALUES(1001, 1, 1, 155.00);
INSERT INTO PURCHASE_ITEM VALUES(1002, 1, 2, 203.00);
INSERT INTO PURCHASE_ITEM VALUES(1003, 1, 3, 75.00);
INSERT INTO PURCHASE_ITEM VALUES(1004, 1, 6, 35.00);
INSERT INTO PURCHASE_ITEM VALUES(1004, 2, 7, 32.00);
INSERT INTO PURCHASE_ITEM VALUES(1005, 1, 4, 240.00);
INSERT INTO PURCHASE_ITEM VALUES(1005, 2, 8, 90.00);
INSERT INTO PURCHASE_ITEM VALUES(1006, 1, 10, 25.00);
INSERT INTO PURCHASE_ITEM VALUES(1007, 1, 11, 45.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 1, 5, 175.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 2, 9, 215.00);
INSERT INTO PURCHASE_ITEM VALUES(1008, 3, 12, 55.00);
INSERT INTO PURCHASE_ITEM VALUES(1009, 1, 14, 72.00);

-- SELECT * FROM PURCHASE_ITEM;

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

