HW 7 

Conceptual Questions
1. A relational database management system (RDBMS or just RDB) is a common type of database that stores data in tables, so it can be used in relation to other stored datasets.
2. Structures Query Language (SQL) is a programming language used to communicate with stored data in a RBDMS. It allows the access to access, query, and reorganize the data and structure of the database.
3. An Entity Relationship model (ER) is a way of showing the relationships between entities in the database.
4. One-to-one: One data in a table has a connection with only one other data in another table. One-to-many: One data in a table has a connection with one or more data members in another. Many-to-many: Multiple sets of data has mutiple connections with multiple other sets of data in another table.
5. Primary keys are used to uniquely identify a variable or array in a database.

SQL 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 NOT NULL;
3.
INSERT INTO Customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('Wilson','Bedford','806 Cherokee','Bakersflied','CA',93309,'princeofbsixtwelve@gmail.com',33);
INSERT INTO Customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('Bedford','Wilson','806 Cherokee','Bakersflied','CA',93309,'notprinceofbsixtwelve@gmail.com',333);
INSERT INTO Customers(firstName, lastName, address, city, state, zip, email,age) VALUES ('Wilson','Wade','806 Cherokee','Bakersflied','CA',93309,'totallyprinceofbsixtwelve@gmail.com',3333);
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) NOT NULL );
6.
INSERT INTO Products(title, description, price) VALUES ('Apples", "red", 0.99);
INSERT INTO Products(title, description, price) VALUES ('Melon", "green", 3.33);
INSERT INTO Products(title, description, price) VALUES ('Banana", "yellow", 0.69);
7.
SELECT * FROM Products
8.
UPDATE Products SET price = 49.99 WHERE Products.id = 1;
9.
SELECT * FROM Products WHERE price<'100';
10. 
DELETE FROM Products order by id ?
11. 
CREATE TABLE Orders(
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    cid INT NOT NULL REFERENCES Products(id),
    price decimal(5,2) NOT NULL,
    date timestamp default CURRENT_TIMESTAMP
);
13.
SELECT * FROM Orders WHERE cid = 1;
14.
SELECT * FROM Orders WHERE price > 100;
15.
DROP TABLE Orders;