/*
 * The code starting from question 6 can be found in
 * tables.sql within this directory as well.
 */

========================
  Conceptual Questions
========================
1) A RDBMS is a relational database management system which allows the ability to modify, access and process data stored within a relational database

2) SQL is a Structured Query Language that is used to interact and access a database through commands like Create, Select etc.

3) The ER model is an Entity Relationship model, which is a diagram showing unique entities that would reside within a database and their relationships towards other entities through cardinality.

4) One-to-one:  There exist an entity X and entity Y where in their relation there exists only one of each.
   One-to-many: There exists an entity X and entity Y where in their relation there exists only one of X and more than one Y.
   Many-to-many: There exists an entity X and entity Y where in their relation there exists more than one of each.

5) A primary key is a unique identifier of a subset of information. It needs to be unique as to refrain from having two of the same keys being used to identify two tables which will cause obvious errors.

========================
    SQL Exercises
========================

6)
CREATE TABLE IF NOT EXISTS customers (
    id INTEGER unsigned NOT NULL AUTO_INCREMENT,
    fname VARCHAR(30) NOT NULL,
    lname VARCHAR(30) NOT NULL,
    address VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    zip INTEGER(5) UNSIGNED NOT NULL,
    email VARCHAR(30) NOT NULL,

    PRIMARY KEY(id)
);

7)
ALTER TABLE customers 
ADD age INTEGER UNSIGNED NOT NULL;

8)
INSERT INTO customers(id, fname,lname,address,city,zip,email,age)
VALUES(1,'John','Doe', '1234 Street', 'city1',12345, 'email1@email.com',28);
INSERT INTO customers(id, fname,lname,address,city,zip,email,age)
VALUES(2,'Jane','Doe', '5678 Street', 'city2', 67891, 'email2@email.com',36);
INSERT INTO customers(id, fname,lname,address,city,zip,email,age)
VALUES(3,'Jane','Dove', '9123 Street', 'city3', 23456, 'email3@email.com',25);

9)
select * from customers;

10)
CREATE TABLE IF NOT EXISTS products (
    id INTEGER NOT NULL AUTO_INCREMENT,
    title VARCHAR(30) NOT NULL,
    description VARCHAR(199) NOT NULL,
    price decimal(5,2) UNSIGNED NOT NULL,

    PRIMARY KEY(id)
);

11)
INSERT INTO products(id, title, description, price)
VALUES(1,'movie1','Action',59.99);
INSERT INTO products(id, title, description, price)
VALUES(2,'movie2','Adventure',29.31);
INSERT INTO products(id, title, description, price)
VALUES(3,'movie3','Romance',119.29);

12)
select * from products;

13)
update products
SET price = 49.99
WHERE id = 2;

14)
select * from products
where price < 100;

15)
DELETE FROM products
order by id desc limit 1;

16)
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER NOT NULL AUTO_INCREMENT,
    cid INTEGER UNSIGNED NOT NULL,
    amount decimal UNSIGNED NOT NULL,
    date datetime default CURRENT_TIMESTAMP,

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

17)
/* Not sure what value is assumed by amount due to the following questions, these 3 inserts
insert a price but under question 19 I show an example inserting a quantity. */
INSERT INTO orders(id,cid,amount,date)
VALUES(1,1,125.99,default);
INSERT INTO orders(id,cid,amount,date)
VALUES(2,2,79.23,default);
INSERT INTO orders(id,cid,amount,date)
VALUES(3,3,205.99,default);

18)
select * from orders
where cid = 1;

19)
/* A little confused as to how this question is supposed to be answered so I gave a couple ways */
/* First example where amount is a price */
select * from orders
where amount > 100;

/* Lets say I set amount to a quantity of an item instead of a price, because there is no reference
to products I would just hardcode the price and multiply by quantity. */
INSERT INTO ORDER(id,cid,amount,date)
VALUES(1,1,2,default);
select * from orders
where cid = 1 AND
amount * 59.99 > 100;

20)
DROP TABLE orders;