Carter Womack
Homework 7

SQL Exercises

--------#1---------- 

CREATE TABLE Customers{
     id int NOT NULL AUTO_INCREMENT,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255) NOT NULL,
     address varchar(255) NOT NULL,
     city varchar(255) NOT NULL,
     state varchar (2) NOT NULL,
     zip int NOT NULL,
     email varchar(255) NOT NULL,
}

---------#2----------

ALTER Customers
ADD age int NOT NULL,

----------#3----------

INSERT INTO Customers (id, LastName, FirstName, address, city, state, zip, email, age)
VALUES(11567, Womack, Carter, 12345 Random Road, Bakersfield, CA, 99999, cwomack2@csub.edu, 20)

INSERT INTO Customers (id, LastName, FirstName, address, city, state, zip, email, age)
VALUES(11568, Diesel, Vin, 54321 Hollywood Road, Hollywood, CA, 90028, muscleman@gmail.com, 53)


INSERT INTO Customers (id, LastName, FirstName, address, city, state, zip, email, age)
VALUES(11569, The Dog, Jake, Big Ass Tree, Ooo, Somewhere, 1234, jakethedog@ooomail.com, 100)

----------#4----------

SELECT * FROM Customers

---------#5----------

CREATE TABLE Products{
    id NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    description varchar(255) NOT NULL,
    price decimal(5,2) NOT NULL,
}

---------#6----------

INSERT INTO Products(id, title, description, price)
VALUES(1, Toaster Strudel, Breakfast Pastry that is placed into the toaster for maximum gooeyness, 2.09) 

INSERT INTO Products(id, title, description, price)
VALUES(2, Garden Hose, Strong rubber garden hose used to squirt squirrels in the face, 16.99) 

INSERT INTO Products(id, title, description, price)
VALUES(3, Gaming Chair, Chair used by gaming gods to carry their teams to victory, 999.99)

----------#7----------

SELECT * FROM Products

---------#8----------

UPDATE Products
SET price 49.99
WHERE ID = 3;

----------#9----------

SELECT price
FROM Products
WHERE price < 100

----------#10----------

DELETE FROM Products
ORDER BY id DESC limit 1

----------#11----------

CREATE TABLE Orders{
    id NOT NULL AUTO_INCREMENT,
    cid int NOT NULL,
    amount decimal NOT NULL,
    date TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
    FOREIGN KEY (cid) REFERENCE Customers(id)
}

---------#12---------

INSERT INTO Orders(id, cid, amount, date)
VALUES(7, 11567, 34.76, 3-22-2021)

INSERT INTO Orders(id, cid, amount, date)
VALUES(8, 11568, 107.54, 3-22-2021)

INSERT INTO Orders(id, cid, amount, date)
VALUES(9, 11569, 4544.12, 3-22-2021)

---------#13----------

SELECT * FROM Orders
WHERE cid = 1

----------#14----------

SELECT amount
FROM Orders
WHERE amount > 100

----------#15----------

DROP TABLE Orders

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