/*
1.What is a relational database management system (RDBMS)?
    A relational database management system is designed to manage relational databases.
    A number of examples inlcude MySQL and Oracle Database.

2.What is SQL?
    SQL is a programming language used to maniplate data in a number of RDBMS.

3.What is an ER model?
    An ER model is used to demonstrate relations between different tables in a database.

4.Define the following terms: One-to-one relationship, one-to-many relationship, and many-to-many relationship.
    The term one to one means that one attribute in one talbe refers to only one attribute in another.
    The term one to many means that one attribute in a table refers to many attributes in another table.
    The term many to many refers to many attributes in a table refering to many attributes in another table.

5.What is the purpose of a primary key? Why should it be unique?
    In most cases the purpose of a primary key is to create a relation between two or more tables.
    The reason why it should be unique is in order to prevent any data is not duplicated.
*/

--6.
CREATE TABLE IF NOT EXISTS Customers (
    id INTEGER NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip int(5) NOT NULL,
    email VARCHAR(255) NOT NULL,

    PRIMARY KEY (id)
);

--7
ALTER TABLE Customers ADD age INTEGER UNSIGNED NOT NULL;

--8
INSERT INTO Customers ( firstName, lastName, address, city, state, zip, email, age)
VALUES
( 'Orsen', 'Cart', '1234 Main Street', 'Bakersfield', 'CA', 98765, "orsen@gmail.com", 47), 
( 'Rick', 'Sanchez', '2342 1st Street', 'Springfeild', 'WA', 13452, "rick@gmail.com", 56),
( 'Jack', 'Black', '2nd Street', 'Los Angles', 'CA', 90210, "jack@gmail.com", 51);

--9
SELECT * FROM Customers;

--10
CREATE TABLE IF NOT EXISTS Products (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    description VARCHAR(50) NOT NULL,
    price DECIMAL(5,2) UNSIGNED NOT NULL
);

-- 11
    INSERT INTO Products (title, description, price) 
    VALUES
    ( 'Tolet Paper', 'Its all gone!', 12.50),
    ( 'Laptop', 'Its a Chromebook', 199.99),
    ( 'Cell Phone', 'Its an iPhone', 1000.99);
--12
    SELECT * FROM Products;
--13
    UPDATE Products SET price = 49.99 WHERE id = 1;
--14
    SELECT * FROM Products WHERE price < 100;
--15
    --DELETE FROM Products
    --ORDER BY id
    --LIMIT 1;
    --DELETE Products FROM table ORDER BY id DESC LIMIT 1;
    --DELETE FROM Products WHERE id=(SELECT MAX(id) );
    DELETE FROM Products WHERE id = LAST_INSERT_ID();
--16
    CREATE TABLE IF NOT EXISTS Orders (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cid INTEGER NOT NULL,
    amount DECIMAL(5,2) UNSIGNED NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

--17
    INSERT INTO Orders (cid, amount, date) VALUES
    ((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "199.99" , DEFAULT),
    ((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "399.98" , DEFAULT),
    ((SELECT id FROM Customers ORDER BY RAND() LIMIT 1) , "49.99" , DEFAULT);
--18
    SELECT * FROM Orders WHERE cid = (
        SELECT MIN(id) FROM Customers
    );

--19
    SELECT * FROM Orders WHERE amount > 100.00;

--20
    DROP TABLE IF EXISTS Orders;