1. What is a relation database management system? 
    - A relational database management system is a system where data is stored on tables. 
    Using these tables we can make queries from the simple all the way to complex queries by 
    adding and filtering data from multiple tables. 

2. What is SQL ? 
    - SQL stands for Structured Query Language. This langauge is mostly used to communicate 
    with a database to collect, filter and present queried information that the user wants to 
    extract from the databse based on a specific query. It is also used to create, edit and
    update tables and the data in those tables of the database. 

3. What is an ER model ? 
    - The ER model stands for an Entity Relationship Model. This model is used to get started 
    on defining the characteristics/attributes of a database and how they connect to one another 
    using relationships. It is meant as a starting point to easily see how the database interconnects 
    its entities and relationships. 

4. Define the following terms: 
    One-To-One Relationship: A 1-1 relationship is where each record for one table can connect to only one record
    of another table. The same applies for the later table, each record in the later table can connect to only
    one record in the previous table with which it has a one-to-one relationship.
    
    One-To-Many Relationship: A 1-M relationship is where a record in a table can connect to many records 
    in another table. However, each record in the later table can only be connected to one record from 
    the previous table with which it has a one-to-many relationship.
    
    Many-To-Many Relationship: A M-M relationship involves a table where the record of one table can have
    many connections to another table and vice versa with the other table with which it has a many-to-many
    relationship. 

5. What is the purpose of a primary key? Why should it be unique ? 
    - The purpose of a primary key is to give each table a unique way to identify the records within
    it. It should be unique because it lets there be many copies of the same general data while letting 
    each set of data retain a uniqueness to help identify them. 

-----------------------------------------------------------------------

6. Create a table called Customers that has eight properties with these exact names.

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)
);

7. Alter the Customers table by adding a new column for a property called age.

ALTER TABLE Customers
ADD age INT UNSIGNED NOT NULL;

8. Add at least 3 records to the Customers table.

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');

9. Select all of your customers.

SELECT firstName, lastName FROM Customers;

10. Create a table called Products that has four properties with these exact names.

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)
);

11. Add at least 3 records to the Products table.
 
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);

12. Select all of your products.

SELECT * FROM Products;

13. Update one of your products' price to $49.99

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

14. Select all of your products that are less that $100

SELECT * FROM Products 
WHERE price < 100.00;

15. Delete you last product record

DELETE FROM Products
ORDER BY id DESC limit 1;

16. Create a tabled called Orders that has four properties with these exact names.

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)
);

17. Using the data that was added to the Customers and Products tables, add atleast 3 records to the Orders table.

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

18. Select all orders where the customerID is that of your first customer.

SELECT * FROM Orders
WHERE cid = 1;

19. Select all orders above $100. 

SELECT * FROM Orders
WHERE amount > 100.00;

20. Drop your Orders table. 

DROP TABLE Orders;