-- 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 IF NOT EXISTS Customers (
    id INTEGER NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    userAddress VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    zip MEDIUMINT UNSIGNED NOT NULL,
    email VARCHAR(30) NOT NULL,

    PRIMARY KEY (id)
);

-- Alter the Customers table by adding a new column for a property called age, which is an unsigned integer that cannot be null.
ALTER TABLE Customers ADD age INTEGER NOT NULL;

-- Add at least 3 records to the Customers table.
INSERT INTO Customers(firstName, lastName, userAddress, city, state, zip, email, age) VALUES
("Larry", "Marquez", "2321 Brite Street", "Bakersfield", "CA", "93304", "lmarquez12@csub.edu", "22"),
("Gianna", "Yadach", "2385 Sugar Camp Road", "Owatonna", "MN", "55060", "someone@gmail.com", "23"),
("Frederik", "Slatt", "123 Infinite Loop Drive", "Cupertino", "CA", "30121", "whosGLM@gmail.com", "40");

-- Select all of your customers.

SELECT * from Customers;


-- 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 IF NOT EXISTS Products (
    id INTEGER NOT NULL AUTO_INCREMENT,
    title VARCHAR(20) NOT NULL,
    description VARCHAR(60) NOT NULL,
    price DECIMAL(5,2) NOT NULL,

    PRIMARY KEY (id)
);
-- Add at least 3 records to the Products table.
INSERT INTO Products(title, description, price) VALUES
("Call of Duty Modern Warfare", "FPS Shooter of the Year!", "59.99"),
("1 Pound of Medium Roast Coffee", "Delicious coffee roasted from the finest coffee beans in South America.", "199.99"),
("1 Pound of C4 Pre Workout", "Aids weight loss and higher focus when exerciing!", "24.99");

-- Select all of your products.
SELECT * from Products;

-- Update one of your products' price to $49.99

UPDATE Products SET price = "49.99" WHERE id = 33;

-- Select all of your products that are less than $100.

SELECT * from Products WHERE price > 100.00;

-- Delete your last product record

-- DELETE FROM Products ORDER BY id DESC LIMIT 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, unsigned 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 IF NOT EXISTS Orders (
    id INTEGER NOT NULL AUTO_INCREMENT,
    cid INTEGER NOT NULL,
    amount DECIMAL(5,2) NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    FOREIGN KEY (cid) REFERENCES Customers(id)
);

-- Using the data that was added to the Customers and Products tables, add at least 3 records to the Orders table.
INSERT INTO Orders (cid, amount, date) VALUES
((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "109.23" ,"2020-03-11 23:59:59"),
((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "72.12" , DEFAULT),
((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "612.57" ,"2020-03-11 03:22:42");

-- Select all orders where the customer ID is that of your first customer.
SELECT * FROM Orders WHERE cid = (
    SELECT MIN(id) From Customers
);

-- Select all orders above $100.
SELECT * FROM Orders WHERE amount > 100.00;

-- Drop your Orders table.
DROP TABLE IF EXISTS Orders;

