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

ALTER TABLE customers 
ADD age INTEGER UNSIGNED NOT NULL;

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

select * from customers;

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

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

select * from products;

update products
SET price = 49.99
WHERE id = 2;

select * from products
where price < 100;

DELETE FROM products
order by id desc limit 1;

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

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

select * from orders
where cid = 1;

select * from orders
where amount > 100;

DROP TABLE orders;
DROP TABLE products;
DROP TABLE customers;


