Connection to MySQL server successfully established.

Customers | Products | Orders

db_description.md

*customers* Table with attributes:
+ customerID (Primary Key)
+ firstName
+ lastName
+ email
+ phone
+ address
+ city
+ state
+ zip

*products* Table with attributes:
+ productID (Primary Key)
+ productName
+ qty
+ active

*productOptions* Table with attributes:
+ optionID (Primary Key)
+ optionName
+ optionValue
+ productID (Foreign Key) References table products->productID

*orders* Table with attributes:
+ ordersID (Primary Key)
+ customerID (Foreign Key) References table customers->customerID
+ productID (Foreign Key) References table products->productID
+ optionID (Foreign Key) References table productOptions->optionID
+ orderDate


Create statements

CREATE TABLE customers (customerID int AUTO_INCREMENT PRIMARY KEY, firstName varchar(50), lastName varchar(50), email varchar(50), phone varchar(10), address varchar(255), city varchar(50), state varchar(2), zip varchar(10));

CREATE TABLE products (productID int AUTO_INCREMENT PRIMARY KEY, productName varchar(50), qty int, active int);

CREATE TABLE productOptions (optionID int AUTO_INCREMENT PRIMARY KEY, optionName varchar(50), optionValue varchar(50), productID int);

CREATE TABLE orders (ordersID int AUTO_INCREMENT PRIMARY KEY, customerID int, productID int, optionID varchar(25), orderDate date);

Insert statements

customers Table

INSERT INTO customers (firstName, lastName, email, phone, address, city, state, zip) VALUES ('Peter', 'Parker', 'parkerp@spiderman.com', '3165558745', '6341 Sesame St', 'Brooklyn', 'NY', '11211');

INSERT INTO customers (firstName, lastName, email, phone, address, city, state, zip) VALUES ('Tony', 'Stark', 'tony@starkindustries.com', '3168881266', '730 Loch Ness Rd', 'Towson', 'MD', '21022');

INSERT INTO customers (firstName, lastName, email, phone, address, city, state, zip) VALUES ('Clint', 'Barton', 'hawkeye@avengers.com', '3165552348', '1225 Mistletoe Lane', 'Redding', 'CA', '96003');

INSERT INTO customers (firstName, lastName, email, phone, address, city, state, zip) VALUES ('Natasha', 'Romanoff', 'nat@avengers.com', '3168889753', '3 Stars Rd', 'Edmond', 'OK', '73034');

INSERT INTO customers (firstName, lastName, email, phone, address, city, state, zip) VALUES ('Rocket', 'Racoon', 'rocky@starlord.com', '3165551799', '372 Peacock Ave', 'Miami', 'FL', '33125');

customers Table

INSERT INTO products(productName, qty, active) VALUES ('Storm Physix',20,1);

INSERT INTO products(productName, qty, active) VALUES ('Storm Phaze III',9,1);

INSERT INTO products(productName, qty, active) VALUES ('Storm Idol Synergy',0,0);

INSERT INTO products(productName, qty, active) VALUES ('Hammer Black Widow',4,1);

INSERT INTO products(productName, qty, active) VALUES ('Columbia 300 Madness',10,1);

productOptions Table

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 9, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 10, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 11, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 12, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 13, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 14, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 15, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 16, 1);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 10, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 11, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 12, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 13, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 14, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 15, 2);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 10, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 11, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 12, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 13, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 14, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 15, 3);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 9, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 10, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 11, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 12, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 13, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 14, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 15, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 16, 4);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 10, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 11, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 12, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 13, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 14, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 15, 5);

INSERT INTO productOptions(optionName, optionValue, productID) VALUES ('weight', 16, 5);

Orders Table

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (5,2,1,'2023-02-14');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (5,5,29,'2023-02-14');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (3,1,7,'2023-04-02');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (1,5,32,'2023-01-10');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (2,1,8,'2023-03-04');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (2,5,35,'2023-03-04');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (4,4,33,'2023-03-10 ');

INSERT INTO orders(customerID, productID, optionID, orderDate) VALUES (4,4,33,'2023-04-18');

CRUD

SELECT * FROM customers;

SELECT o.ordersID, o.orderDate, o.optionID, c.firstName, c.lastName, p.productID, p.productName, po.optionName, po.optionValue FROM orders o JOIN customers c ON c.customerID = o.customerID JOIN products p ON p.productID = o.productID JOIN productOptions po ON po.optionID = o.optionID;

UPDATE products SET status = 0 WHERE productID = 5;

UPDATE products SET qty = 19 WHERE productID = 1;

DELETE FROM orders WHERE customerID = 3;

DELETE FROM productOptions WHERE optionValue = 9;