
CREATE TABLE IF NOT EXISTS Customers (
    -- Fields: 
    id          INT NOT NULL AUTO_INCREMENT, 
    firstName   VARCHAR( 255 ) NOT NULL,
    lastName    VARCHAR( 255 ) NOT NULL,
    address        VARCHAR( 255 ) NOT NULL,
    city        VARCHAR( 255 ) NOT NULL,
    state  VARCHAR( 2 )   NOT NULL,
    zip         int( 11 ) UNSIGNED NOT NULL,
    email       VARCHAR(255) NOT NULL,

    -- Constraints: 
    PRIMARY KEY (id)
);
-- CHECK ! 

ALTER TABLE Customers
ADD age INT UNSIGNED NOT NULL;
-- CHECK! 

INSERT INTO Customers(firstName, lastName, addr, city, cust_state, zip, email) VALUES
('Andy','Okami', '2916 K Street', 'Bakersfield', 'CA', '93309', 'andy@gmail.com'),
('Tori','Leister', '1036 W Street', 'Bakersfield', 'CA', '93306', 'tori@gmail.com'),
('Lily','Lynn', '1234 Z Street', 'Bakersfield', 'CA', '93305', 'lily@gmail.com');
-- CHECK ! 

SELECT firstName, lastName FROM Customers;
-- CHECK ! 
-------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Products (
    -- Fields:
    id  INT NOT NULL AUTO_INCREMENT,
    title   VARCHAR( 255 ) NOT NULL,
    description VARCHAR( 255 ) NOT NULL,
    price  DECIMAL( 5, 2 ),
    -- Constraints: 
    PRIMARY KEY (id)
);
-- CHECK ! 

INSERT INTO Products(title, description, price) VALUES 
('Cup Of Noodles', 'The only thing you can afford for the month due to your bad spending habits', 0.99),
('GamingLaptop', 'Laptop you do not need for university but hey, free money and vidya game', 999.99),
('Starbucks Card', 'The caffeine fix you need to survive university', 20.00);
-- CHECK ! 

SELECT * FROM Products;
-- CHECK ! 

UPDATE Products
SET price = 49.99
WHERE title = 'Starbucks Card';
-- CHECK ! 

SELECT * FROM Products 
WHERE price < 100.00;
-- CHECK ! 

DELETE FROM Products
ORDER BY id DESC limit 1;
-- Check! 
-------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS Orders (
    -- Fields:
    id  INT NOT NULL AUTO_INCREMENT,
    cid INT NOT NULL,
    amount  DECIMAL( 5, 2) UNSIGNED NOT NULL,
    date   TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    
    -- Constraints: 
    PRIMARY KEY (id),
    FOREIGN KEY (cid) REFERENCES Customers(id)
);
-- CHECK ! 

INSERT INTO Orders(cid, amount) VALUES
('1', 123.45),
('1', 90.99),
('3', 245.99);
-- CHECK ! 


SELECT * FROM Orders
WHERE cid = 1;
-- CHECK ! 

SELECT * FROM Orders
WHERE amount > 100.00;
-- CHECK ! 

DROP TABLE Orders;
-- CHECK ! 