/*  Create a table called Customers that has eight properties with these exact names.
id : A non-null, auto-incrementing integer that is the primary key.
firstName : A string that cannot be null.
lastName : A string that cannot be null.
address : A string that cannot be null.
city : A string that cannot be null.
state : A two character string that cannot be null.
zip : A five digit, unsigned integer that cannot be null.
email : A string that cannot be null */

CREATE TABLE Customers (
    id INTEGER NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    age INT UNSIGNED,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(255) NOT NULL,
    zip INT UNSIGNED,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
);
INSERT INTO Orders(firstName, lastName, age, address, city, state, zip, email) VALUES('Name1', 'lName1', 20, '7777 streetname', 'Bakersfield', 'CA', 90000, 'name1@email.com');
INSERT INTO Orders(firstName, lastName, age, address, city, state, zip, email) VALUES('Name2', 'lName2', 55, '1234 streetname', 'Bakersfield', 'CA', 91234, 'name2@email.com');
INSERT INTO Orders(firstName, lastName, age, address, city, state, zip, email) VALUES('Name1', 'lName1', 20, '5678 streetname', 'Bakersfield', 'CA', 93987, 'name3@email.com');

/* Create a table called Products that has four properties with these exact names.
id : A non-null, auto-incrementing integer that is the primary key.
title : A string that cannot be null.
description : A string that cannot be null.
price : A non-null, unsigned decimal that can store up to five digits total with two decimal places.
*/
CREATE TABLE Product (
    id INTEGER NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL,
    price DECIMAL(5,2) NOT NULL,
    PRIMARY KEY(id)
);
/** 6. Add at least 3 records to the Products table.
7. Select all of your products.
8. Update one of your products' price to $49.99.
9. Select all of your products that are less than $100.
10. Delete your last product record */

INSERT INTO Product(title,description,price) VALUES('Product #1', 'First product sold', 2.99);
INSERT INTO Product(title,description,price) VALUES('Product #2', 'Second product sold', 499.00);
INSERT INTO Product(title,description,price) VALUES('Product #3', 'Third product sold', 60.00);

UPDATE Product SET price = 49.99 WHERE id = 3;
SELECT * FROM Product WHERE price < 100;
DELETE FROM Product WHERE id=1;
/**
Create a table called Orders that has four properties with these exact names.
id : A non-null, auto-incrementing integer that is the primary key.
cid : A non-null integer that references id from the Customers table.
amount : A non-null, unsigned decimal.
date : A timestamp that defaults to the current timestamp
*/
CREATE TABLE Orders(
    id INTEGER NOT NULL AUTO_INCREMENT,
    cid INTEGER NOT NULL,
    amount DECIMAL UNSIGNED,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
);

/** 12. Using the data that was added to the Customers and Products tables, add at least 3 records to the Orders table.
13. Select all orders where the customer ID is that of your first customer.
14. Select all orders above $100.
15. Drop your Orders table. */
INSERT INTO Orders(cid, amount) VALUES(1, 2.99);
INSERT INTO Orders(cid, amount) VALUES(2, 499.99);
INSERT INTO Orders(cid, amount) VALUES(3, 60.00);
SELECT * FROM Orders WHERE amount > 100;
SELECT * FROM Orders;