DROP TABLE IF EXISTS Invoice;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS LineItem;
DROP TABLE IF EXISTS Product;

CREATE TABLE Invoice (Invoice_Number INTEGER, Customer_Number INTEGER, Payment DECIMAL(10, 2));
CREATE TABLE Customer (Customer_Number INTEGER, Name VARCHAR(40), Address VARCHAR(40), City VARCHAR(30), State CHAR(2), Zip CHAR(5));
CREATE TABLE LineItem (Invoice_Number INTEGER, Product_Code CHAR(7), Quantity INTEGER);
CREATE TABLE Product (Product_Code CHAR(7), Description VARCHAR(40), Price DECIMAL(10, 2));

  INSERT INTO Invoice VALUES (11731, 3175, 0);
  INSERT INTO Invoice VALUES (11732, 3176, 249.50);
  INSERT INTO Invoice VALUES (11733, 3175, 0);

  INSERT INTO Customer VALUES (3175, 'Sam''s Small Appliances', '100 Main Street', 'Anytown', 'CA', 98765);
  INSERT INTO Customer VALUES (3176, 'Electronics Unlimited', '1175 Liberty Ave', 'Pleasantville', 'MI', 45066);

  INSERT INTO LineItem VALUES (11731, '116-064', 3);
  INSERT INTO LineItem VALUES (11731, '257-535', 1);
  INSERT INTO LineItem VALUES (11731, '643-119', 2);
  INSERT INTO LineItem VALUES (11732, '116-064', 10);
  INSERT INTO LineItem VALUES (11733, '116-064', 2);
  INSERT INTO LineItem VALUES (11733, '643-119', 1);

  INSERT INTO Product VALUES ('116-064', 'Toaster', 24.95);
  INSERT INTO Product VALUES ('257-535', 'Hair Dryer', 29.95);
  INSERT INTO Product VALUES ('643-119', 'Car Vacuum', 19.99);

/* SELECT * FROM Invoice;
SELECT * FROM Customer;
SELECT * FROM LineItem;
SELECT * FROM Product; */
SELECT * FROM Product;

/* SELECT Name FROM Customer WHERE State IN ('CA', 'NV'); */
/* SELECT Product.Product_Code, Product.Description, Product.Price FROM Product, LineItem, Customer, Invoice
WHERE Customer.State = 'CA'
AND Customer.Customer_Number = Invoice.Customer_Number AND Invoice.Invoice_Number = Lineitem.Invoice_Number
AND LineItem.Product_Code = Product.Product_Code; */