DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS transactions;

CREATE TABLE customers(
     cID MEDIUMINT NOT NULL AUTO_INCREMENT,
     cName VARCHAR(30),
     address VARCHAR(25),
     city VARCHAR(10),
     state VARCHAR(2),
     zip int(5),
     email VARCHAR(40),
     year_born int(4), 
     PRIMARY KEY (cID),
     FOREIGN KEY (state) REFERENCES states(sID)
     );

INSERT INTO customers VALUES(NULL,"Ezra Mosseri", “23 W13th Street", "New York", "NY", 10003, “janedoe@gmail.com”, 1993);
INSERT INTO customers VALUES(NULL,"Ben Ryzak", "54 E13th Street", "New York", "NY", 10003, “jrp185@nyu.edu", 1992);
INSERT INTO customers VALUES(NULL,"Jack Mosseri", 7254 East 65th Street", "Brooklyn", "NY", 11223, “fakeemail@aol.com", 1996);
INSERT INTO customers VALUES(NULL,"Michelle Smith”, “56 Shalow Cove", "Great Neck", "NY", 11024, “notreal@aol.com", 1991);
INSERT INTO customers VALUES(NULL,"Stella Grabner”, “466 E34th St.", "New York", "NY", 10016, “null@gmail.com", 1992);
INSERT INTO customers VALUES(NULL,"Judy Fisher”, "1201 W15th Street", "New York", "NY", 10003, “notlegitemail@gmail.com", 1979);


CREATE TABLE states(
     sID VARCHAR(2),
     PRIMARY KEY (sID));

INSERT INTO states VALUES ('AK');
INSERT INTO states VALUES ('AL');
INSERT INTO states VALUES ('AS');
INSERT INTO states VALUES ('AZ');
INSERT INTO states VALUES ('AR');
INSERT INTO states VALUES ('CA');
INSERT INTO states VALUES ('CO');
INSERT INTO states VALUES ('CT');
INSERT INTO states VALUES ('DE');
INSERT INTO states VALUES ('DC');
INSERT INTO states VALUES ('FL');
INSERT INTO states VALUES ('GA');
INSERT INTO states VALUES ('HI');
INSERT INTO states VALUES ('ID');
INSERT INTO states VALUES ('IL');
INSERT INTO states VALUES ('IN');
INSERT INTO states VALUES ('IA');
INSERT INTO states VALUES ('KS');
INSERT INTO states VALUES ('KY');
INSERT INTO states VALUES ('LA');
INSERT INTO states VALUES ('ME');
INSERT INTO states VALUES ('MD');
INSERT INTO states VALUES ('MA');
INSERT INTO states VALUES ('MI');
INSERT INTO states VALUES ('MN');
INSERT INTO states VALUES ('MS');
INSERT INTO states VALUES ('MO');
INSERT INTO states VALUES ('MT');
INSERT INTO states VALUES ('NE');
INSERT INTO states VALUES ('NV');
INSERT INTO states VALUES ('NH');
INSERT INTO states VALUES ('NJ');
INSERT INTO states VALUES ('NM');
INSERT INTO states VALUES ('NY');
INSERT INTO states VALUES ('NC');
INSERT INTO states VALUES ('ND');
INSERT INTO states VALUES ('OH');
INSERT INTO states VALUES ('OK');
INSERT INTO states VALUES ('OR');
INSERT INTO states VALUES ('PA');
INSERT INTO states VALUES ('RI');
INSERT INTO states VALUES ('SC');
INSERT INTO states VALUES ('SD');
INSERT INTO states VALUES ('TN');
INSERT INTO states VALUES ('TX');
INSERT INTO states VALUES ('UT');
INSERT INTO states VALUES ('VT');
INSERT INTO states VALUES ('VA');
INSERT INTO states VALUES ('WA');
INSERT INTO states VALUES ('WV');
INSERT INTO states VALUES ('WI');
INSERT INTO states VALUES ('WY');


CREATE TABLE categories(
     category VARCHAR(35),
     PRIMARY KEY (category));

INSERT INTO categories VALUES ("Grocery - Dairy");
INSERT INTO categories VALUES ("Grocery - Meat");
INSERT INTO categories VALUES ("Grocery - Poultry");
INSERT INTO categories VALUES ("Grocery - Produce");
INSERT INTO categories VALUES ("Grocery");
INSERT INTO categories VALUES ("Apparel - Mens");
INSERT INTO categories VALUES ("Apparel - Womens");
INSERT INTO categories VALUES ("Apparel - Boys");
INSERT INTO categories VALUES ("Apparel - Girls");
INSERT INTO categories VALUES ("Home");


 CREATE TABLE items(
     itemID int NOT NULL AUTO_INCREMENT,
     name VARCHAR(30),
     category VARCHAR(20), 
     price DECIMAL(4,2),
     image VARCHAR(70),
     PRIMARY KEY (itemID)
     );


ALTER TABLE items AUTO_INCREMENT=1000;

INSERT INTO items VALUES (NULL, "Stacy's Pita Chips Original", "Grocery", 4.99, "<img src='images/stacys.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Frank's Yellow Mustard", "Grocery", 3.49, "<img src='images/mustard.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Frank's Tomato Ketchup", "Grocery", 3.49, "<img src='images/ketchup.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Real Fruit Gushers", "Grocery", 5.79, "<img src='images/gushers.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Macintosh Apple", "Grocery - Produce", 0.99, "<img src='images/macapple.jpeg style='max-width: 50px;''>");
INSERT INTO items VALUES (NULL, "Asian Pear", "Grocery - Produce", 1.29, "<img src='images/asianpear.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Large Green Pepper", "Grocery - Produce", 0.49, "<img src='images/greenpepper.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Large Red Pepper", "Grocery - Produce", 0.49, "<img src='images/redpepper.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Tuscan Large Whole Milk", "Grocery - Dairy", 2.99, "<img src='images/tuscanwhole.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Tuscan Large 2% Milk", "Grocery - Dairy", 2.99, "<img src='images/tuscan2.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Tuscan Large Skim Milk", "Grocery - Dairy", 2.99, "<img src='images/tuscanskim.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Polly Fresh Mozzarella", "Grocery - Dairy", 4.79, "<img src='images/pollymoz.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hebrew National Hotdogs [4pk]", "Grocery - Meat", 7.99, "<img src='images/hotdogs.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Oscar Meyer's Bologna", "Grocery - Meat", 5.29, "<img src='images/oscarmeyer.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Boar's Head Sliced Smoked Turkey", "Grocery - Meat", 5.49, "<img src='images/smokedturkey.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Kosher Sliced Corned Beef", "Grocery - Meat", 7.79, "<img src='images/cornedbeef.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Empire Large Turkey", "Grocery - Poultry", 8.79, "<img src='images/empireturkey.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Empire Family Pack Chicken Cutlet", "Grocery - Poultry", 7.59, "<img src='images/empirefamily.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Empire Chicken Cutlet", "Grocery - Poultry", 4.89, "<img src='images/empirechicken.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "KHG Frozen Chicken Nuggets", "Grocery - Poultry", 5.69, "<img src='images/frozennuggets.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Blue T-shirt [Men's]", "Apparel - Mens", 1.99, "<img src='images/hanesblue.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Black T-shirt [Men's]", "Apparel - Mens", 1.99, "<img src='images/hanesblack.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Red T-shirt [Men's]", "Apparel - Mens", 1.99, "<img src='images/hanesred.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes White T-shirt [Men's]", "Apparel - Mens", 1.99, "<img src='images/haneswhite.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Blue T-shirt [Women's]", "Apparel - Womens", 1.99, "<img src='images/hanesbluew.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Black T-shirt [Women's]", "Apparel - Womens", 1.99, "<img src='images/hanesblackw.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Red T-shirt [Women's]", "Apparel - Womens", 1.99, "<img src='images/hanesredw.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes White T-shirt [Women's]", "Apparel - Womens", 1.99, "<img src='images/haneswhitew.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Sky Blue T-shirt [Boy's]", "Apparel - Boys", 1.99, "<img src='images/boysblue.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Green T-shirt [Boy's]", "Apparel - Boys", 1.99, "<img src='images/boysgreen.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Grey T-shirt [Boy's]", "Apparel - Boys", 1.99, "<img src='images/boysgrey.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes White T-shirt [Boy's]", "Apparel - Boys", 1.99, "<img src='images/boyswhite.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Pink T-shirt [Girl's]", "Apparel - Girls", 1.99, "<img src='images/girlspink.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Orange T-shirt [Girl's]", "Apparel - Girls", 1.99, "<img src='images/girlsorange.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Yellow T-shirt [Girl's]", "Apparel - Girls", 1.99, "<img src='images/girlsyellow.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Hanes Blue T-shirt [Girl's]", "Apparel - Girls", 1.99, "<img src='images/girlsblue.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Designer Rug 4x10", "Home", 21.99, "<img src='images/designerrug.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "CB6 Welcome Mat", "Home", 18.79, "<img src='images/welcomemat.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Command Large Hooks [4pk]", "Home", 8.29, "<img src='images/commandhooks.jpeg' style='max-width: 50px;'>");
INSERT INTO items VALUES (NULL, "Phillips Smart Light Bulb", "Home", 70.99, "<img src='images/smartbulb.jpeg' style='max-width: 50px;'>");


CREATE TABLE transactions(
     tID MEDIUMINT(5),
     customer int(5),
     tDate DATE,
     item int(5),
     quantity int(3),
     FOREIGN KEY (customer) REFERENCES customers(cID),
     FOREIGN KEY (item) REFERENCES items(itemID)
     );
     
ALTER TABLE transactions AUTO_INCREMENT=100;     

INSERT INTO transactions VALUES(200, 1, 20141014, 1000, 2);
INSERT INTO transactions VALUES(200, 1, 20141014, 1002, 1);
INSERT INTO transactions VALUES(200, 1, 20141014, 1005, 2);
INSERT INTO transactions VALUES(200, 1, 20141014, 1020, 3);
INSERT INTO transactions VALUES(200, 1, 20141014, 1015, 1);
INSERT INTO transactions VALUES(200, 1, 20141014, 1034, 2);
INSERT INTO transactions VALUES(200, 1, 20141014, 1022, 1);
INSERT INTO transactions VALUES(200, 1, 20141014, 1018, 1);
INSERT INTO transactions VALUES(200, 1, 20141014, 1012, 2);



INSERT INTO transactions VALUES(202, 1, 20141014, 1003, 4);
INSERT INTO transactions VALUES(202, 1, 20141014, 1019, 2);
INSERT INTO transactions VALUES(202, 1, 20141014, 1023, 3);
INSERT INTO transactions VALUES(202, 1, 20141014, 1018, 3);
INSERT INTO transactions VALUES(202, 1, 20141014, 1016, 1);
INSERT INTO transactions VALUES(202, 1, 20141014, 1031, 1);
INSERT INTO transactions VALUES(202, 1, 20141014, 1033, 2);
INSERT INTO transactions VALUES(202, 1, 20141014, 1039, 1);



INSERT INTO transactions VALUES(208, 1, 20141110, 1007, 1);
INSERT INTO transactions VALUES(208, 1, 20141110, 1015, 1);
INSERT INTO transactions VALUES(208, 1, 20141110, 1023, 1);
INSERT INTO transactions VALUES(208, 1, 20141110, 1014, 1);
INSERT INTO transactions VALUES(208, 1, 20141110, 1009, 1);
INSERT INTO transactions VALUES(208, 1, 20141110, 1002, 2);
INSERT INTO transactions VALUES(208, 1, 20141110, 1031, 2);
INSERT INTO transactions VALUES(208, 1, 20141110, 1001, 4);




INSERT INTO transactions VALUES(206, 2, 20141110, 1007, 1);
INSERT INTO transactions VALUES(206, 2, 20141110, 1015, 1);
INSERT INTO transactions VALUES(206, 2, 20141110, 1023, 1);
INSERT INTO transactions VALUES(206, 2, 20141110, 1014, 1);
INSERT INTO transactions VALUES(206, 2, 20141110, 1009, 1);
INSERT INTO transactions VALUES(206, 2, 20141110, 1002, 2);
INSERT INTO transactions VALUES(206, 2, 20141110, 1031, 2);
INSERT INTO transactions VALUES(206, 2, 20141110, 1001, 4);


INSERT INTO transactions VALUES(203, 2, 20141014, 1000, 2);
INSERT INTO transactions VALUES(203, 2, 20141014, 1002, 1);
INSERT INTO transactions VALUES(203, 2, 20141014, 1005, 2);
INSERT INTO transactions VALUES(203, 2, 20141014, 1020, 3);
INSERT INTO transactions VALUES(203, 2, 20141014, 1015, 1);
INSERT INTO transactions VALUES(203, 2, 20141014, 1034, 2);
INSERT INTO transactions VALUES(203, 2, 20141014, 1022, 1);
INSERT INTO transactions VALUES(203, 2, 20141014, 1018, 1);
INSERT INTO transactions VALUES(203, 2, 20141014, 1012, 2);



INSERT INTO transactions VALUES(201, 2, 20141014, 1003, 4);
INSERT INTO transactions VALUES(201, 2, 20141014, 1019, 2);
INSERT INTO transactions VALUES(201, 2, 20141014, 1023, 3);
INSERT INTO transactions VALUES(201, 2, 20141014, 1018, 3);
INSERT INTO transactions VALUES(201, 2, 20141014, 1016, 1);
INSERT INTO transactions VALUES(201, 2, 20141014, 1031, 1);
INSERT INTO transactions VALUES(201, 2, 20141014, 1033, 2);
INSERT INTO transactions VALUES(201, 2, 20141014, 1039, 1);





INSERT INTO transactions VALUES(210, 3, 20141110, 1007, 1);
INSERT INTO transactions VALUES(210, 3, 20141110, 1015, 1);
INSERT INTO transactions VALUES(210, 3, 20141110, 1023, 1);
INSERT INTO transactions VALUES(210, 3, 20141110, 1014, 1);
INSERT INTO transactions VALUES(210, 3, 20141110, 1009, 1);
INSERT INTO transactions VALUES(210, 3, 20141110, 1002, 2);
INSERT INTO transactions VALUES(210, 3, 20141110, 1031, 2);
INSERT INTO transactions VALUES(210, 3, 20141110, 1001, 4);


INSERT INTO transactions VALUES(211, 3, 20141014, 1000, 2);
INSERT INTO transactions VALUES(211, 3, 20141014, 1002, 1);
INSERT INTO transactions VALUES(211, 3, 20141014, 1005, 2);
INSERT INTO transactions VALUES(211, 3, 20141014, 1020, 3);
INSERT INTO transactions VALUES(211, 3, 20141014, 1015, 1);
INSERT INTO transactions VALUES(211, 3, 20141014, 1034, 2);
INSERT INTO transactions VALUES(211, 3, 20141014, 1022, 1);
INSERT INTO transactions VALUES(211, 3, 20141014, 1018, 1);
INSERT INTO transactions VALUES(211, 3, 20141014, 1012, 2);



INSERT INTO transactions VALUES(212, 3, 20141012, 1003, 4);
INSERT INTO transactions VALUES(212, 3, 20141012, 1019, 2);
INSERT INTO transactions VALUES(212, 3, 20141012, 1023, 3);
INSERT INTO transactions VALUES(212, 3, 20141012, 1018, 3);
INSERT INTO transactions VALUES(212, 3, 20141012, 1016, 1);
INSERT INTO transactions VALUES(212, 3, 20141012, 1031, 1);
INSERT INTO transactions VALUES(212, 3, 20141012, 1033, 2);
INSERT INTO transactions VALUES(212, 3, 20141012, 1039, 1);




INSERT INTO transactions VALUES(214, 4, 20141110, 1007, 1);
INSERT INTO transactions VALUES(214, 4, 20141110, 1015, 1);
INSERT INTO transactions VALUES(214, 4, 20141110, 1023, 1);
INSERT INTO transactions VALUES(214, 4, 20141110, 1014, 1);
INSERT INTO transactions VALUES(214, 4, 20141110, 1009, 1);
INSERT INTO transactions VALUES(214, 4, 20141110, 1002, 2);
INSERT INTO transactions VALUES(214, 4, 20141110, 1031, 2);
INSERT INTO transactions VALUES(214, 4, 20141110, 1001, 4);


INSERT INTO transactions VALUES(216, 4, 20141014, 1000, 2);
INSERT INTO transactions VALUES(216, 4, 20141014, 1002, 1);
INSERT INTO transactions VALUES(216, 4, 20141014, 1005, 2);
INSERT INTO transactions VALUES(216, 4, 20141014, 1020, 3);
INSERT INTO transactions VALUES(216, 4, 20141014, 1015, 1);
INSERT INTO transactions VALUES(216, 4, 20141014, 1034, 2);
INSERT INTO transactions VALUES(216, 4, 20141014, 1022, 1);
INSERT INTO transactions VALUES(216, 4, 20141014, 1018, 1);
INSERT INTO transactions VALUES(216, 4, 20141014, 1012, 2);



INSERT INTO transactions VALUES(215, 4, 20141012, 1003, 4);
INSERT INTO transactions VALUES(215, 4, 20141012, 1019, 2);
INSERT INTO transactions VALUES(215, 4, 20141012, 1023, 3);
INSERT INTO transactions VALUES(215, 4, 20141012, 1018, 3);
INSERT INTO transactions VALUES(215, 4, 20141012, 1016, 1);
INSERT INTO transactions VALUES(215, 4, 20141012, 1031, 1);
INSERT INTO transactions VALUES(215, 4, 20141012, 1033, 2);
INSERT INTO transactions VALUES(215, 4, 20141012, 1039, 1);





INSERT INTO transactions VALUES(222, 5, 20141110, 1007, 1);
INSERT INTO transactions VALUES(222, 5, 20141110, 1015, 1);
INSERT INTO transactions VALUES(222, 5, 20141110, 1023, 1);
INSERT INTO transactions VALUES(222, 5, 20141110, 1014, 1);
INSERT INTO transactions VALUES(222, 5, 20141110, 1009, 1);
INSERT INTO transactions VALUES(222, 5, 20141110, 1002, 2);
INSERT INTO transactions VALUES(222, 5, 20141110, 1031, 2);
INSERT INTO transactions VALUES(222, 5, 20141110, 1001, 4);


INSERT INTO transactions VALUES(223, 5, 20141014, 1000, 2);
INSERT INTO transactions VALUES(223, 5, 20141014, 1002, 1);
INSERT INTO transactions VALUES(223, 5, 20141014, 1005, 2);
INSERT INTO transactions VALUES(223, 5, 20141014, 1020, 3);
INSERT INTO transactions VALUES(223, 5, 20141014, 1015, 1);
INSERT INTO transactions VALUES(223, 5, 20141014, 1034, 2);
INSERT INTO transactions VALUES(223, 5, 20141014, 1022, 1);
INSERT INTO transactions VALUES(223, 5, 20141014, 1018, 1);
INSERT INTO transactions VALUES(223, 5, 20141014, 1012, 2);



INSERT INTO transactions VALUES(224, 5, 20141012, 1003, 4);
INSERT INTO transactions VALUES(224, 5, 20141012, 1019, 2);
INSERT INTO transactions VALUES(224, 5, 20141012, 1023, 3);
INSERT INTO transactions VALUES(224, 5, 20141012, 1018, 3);
INSERT INTO transactions VALUES(224, 5, 20141012, 1016, 1);
INSERT INTO transactions VALUES(224, 5, 20141012, 1031, 1);
INSERT INTO transactions VALUES(224, 5, 20141012, 1033, 2);
INSERT INTO transactions VALUES(224, 5, 20141012, 1039, 1);









## Here are the *sample* sql queries for the user generated tables
## (To be placed in another sql file later on)

SELECT r.tID AS "Transaction ID", a.cName, r.tDate, b.name, b.price, r.quantity, (b.price*r.quantity) AS "Total Price"
	 FROM transactions r
	 INNER JOIN customers a ON r.customer = a.cID
	 INNER JOIN items b ON r.item = b.itemID
	 WHERE r.tID=200;     
	 
SELECT SUM(b.price), SUM(b.price*r.quantity) FROM transactions r INNER JOIN items b ON r.item = b.itemID WHERE r.tID=200;