HW 7
Conceptual Questions
1) This is a database system that stores data in tables, using their relationships to find other information in other tables.
2) SQL is a database language that is used to retrieve and modify data within a database via sending it queries.
3) An Entity Relationship model is a high level way to cenceptualize a database by showing things in the model (entities), what their components are, and how they relate to each other.
4) These are all variations of relationship cardinality.  Which shows how many entities can participate in a relationship. EG 1:1 means each can only have one instance (like social security # to a person), 1:n is one to many (a teacher can have many students, but a student can only have 1 teacher), and many to many (a library can lend out many books to many people).
5) Primary keys are like a stake that binds all the tables together, their uniqueness makes it possible to find data on 1 specific entry.  (eg you can use a ssn to find out most info on someone, and only them)

Exercises
1) CREATE TABLE customers(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(5) UNSIGNED NOT NULL,
email varchar(255) NOT NULL,
PRIMARY KEY(id)
);

2) ALTER TABLE customers ADD age int UNSIGNED;

3) INSERT INTO customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('a','a','a','a','CA',99999,'a',6);
INSERT INTO customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('b','b','b','b','CA',99999,'b',8);
INSERT INTO customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('c','c','c','c','CA',99999,'c',8);

4) SELECT * FROM customers;

5) CREATE TABLE products(id int NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
description varchar(255) NOT NULL,
price decimal(5,2) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);

6) INSERT INTO products(title,description,price) VALUES('taco','oh yeah',4.20);
INSERT INTO products(title,description,price) VALUES('depleted plutonium','it green',499.99);
INSERT INTO products(title,description,price) VALUES('super taco','WOAH',420.00);

7) SELECT * FROM products;

8) UPDATE products SET price = 49.99 WHERE id = 1;

9) SELECT * FROM products WHERE price < 100;

10) DELETE FROM products WHERE id = (SELECT COUNT(id) FROM products);

11) CREATE TABLE orders(id int NOT NULL AUTO_INCREMENT,
cid int NOT NULL,
amount decimal(5,2) UNSIGNED NOT NULL,
date TIMESTAMP NOT NULL,
FOREIGN KEY (cid) REFERENCES customers(id),
PRIMARY KEY(id)
);

12) INSERT INTO orders(cid,amount) VALUES(1,4.20);
INSERT INTO orders(cid,amount) VALUES(1,504.19);
INSERT INTO orders(cid,amount) VALUES(2,520.00);

13) SELECT * FROM orders WHERE cid = 1;

14) SELECT * FROM orders WHERE amount > 100;

15) DROP TABLE orders;