-- CSE 154
-- Week 9 Section: Bricker and Morter (PHP and SQL)
-- Starter dataset for Inventory of Bricker and Morter store.
USE bmstore;

DROP TABLE IF EXISTS Inventory;
CREATE TABLE Inventory(
  id           INTEGER  NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(50) NOT NULL,
  category     VARCHAR(35),
  subcategory  VARCHAR(35),
  price        DECIMAL(5, 2) DEFAULT 0.0, -- DECIMAL(5, 2) specifies values can store up to 5 sig. digits and 2 digits following the decimal point.
  cost         DECIMAL(5, 2) DEFAULT 0.0,
  description  VARCHAR(200)
);

INSERT INTO Inventory(name, subcategory, category, price, cost, description) VALUES 
  ('Pickled Walnuts', 'Nuts', 'Food', 4.95, 8.95, 'For all your pickled walnut needs...'),
  ('Peanuts', 'Nuts', 'Food', 3.99, 5.99, 'What''s game day without hot peanuts being lobbed at your head?'),
  ('Walnuts', 'Nuts', 'Food', 3.99, 5.99, 'Stephen doesn''t like these.'),
  ('Almonds', 'Nuts', 'Food', 3.99, 6.99, 'Makes a mighty fine meal if you''re low carb.'),
  ('Trail Mix', 'Nuts', 'Food', 4.99, 6.99, 'When you''re trying to justify M&M''s as a healthy snack.'),
  ('Canned Tuna', 'Canned Foods', 'Food', 3.00, 6.00, 'You can''t see the mercury on our canned tuna!'),
  ('Anchovies', 'Seafood', 'Food', 1.93, 2.10, 'Autopsy in a can.'),
  ('Cod', 'Seafood', 'Food', 2.50, 3.50, NULL),
  ('Eel', 'Seafood', 'Food', 4.40, 8.24, 'Unagi at its finest.'),
  ('Sea Urchin ', 'Seafood', 'Food', 11.45, 25.00, NULL),
  ('Shrimp ', 'Seafood', 'Food', 15.40, 16.00, NULL),
  ('Raw Tuna ', 'Seafood', 'Food', 10.00, 15.00, 'Now that''s what we call Ahi.'),
  ('Salt mackerel', 'Seafood', 'Food', 10.00, 25.00, 'Recommended to serve with saltines'),
  ('Scallops ', 'Seafood', 'Food', 8.00, 14.00, NULL), 
  ('Rainbow Trout', 'Seafood', 'Food', 5.50, 10.00, NULL),
  ('Brown Trout', 'Seafood', 'Food', 5.50, 10.00, NULL),
  ('Bluefish ', 'Seafood', 'Food', 4.75, 9.00, NULL),    
  ('Kippered Herring ', 'Seafood', 'Food', 5.00, 10.00, NULL), 
  ('Crab ', 'Seafood', 'Food',  6.00, 12.00, NULL), 
  ('Beluga Caviar', 'Seafood', 'Food', 18.00, 40.00, NULL),
  ('Black Caviar ', 'Seafood', 'Food', 20.00, 44.00, NULL), 
  ('Red Caviar ', 'Seafood', 'Food', 19.00, 42.00, NULL), 
  ('Clams', 'Seafood', 'Food', 12.00, 20.00, 'Not too clammy'),    
  ('Little Neck clams', 'Seafood', 'Food', 13.50, 21.00, 'These remind me of Little Foot.'), 
  ('Lobster', 'Seafood', 'Food', 30.00, 60.00, 'Maybe not for the college budget.'),
  ('Oysters', 'Seafood', 'Food', 14.00, 28.00, NULL), 
  ('Sardines ', 'Seafood', 'Food', 3.00, 6.00, 'These pair nicely with Anchovies.'), 
  ('Pinto Beans', 'Legumes', 'Food', 1.00, 2.25, 'These were inspiration to the Ford counterpart.'), 
  ('Black-eyed Peas', 'Legumes', 'Food', 1.00, 2.25, NULL), 
  ('Lentils', 'Legumes', 'Food', 1.20, 2.50, 'Great for interesting vegan recipes!'),
  ('Edamame', 'Legumes', 'Food', 4, 6, 'Add soymilk and enjoy these as an alternative to breakfast cereal'), 
  ('Plain Omelette', 'Proteins', 'Food', 3, 6, NULL), 
  ('Firm Tofu', 'Proteins', 'Food', 3, 6, NULL),
  ('Soft Tofu', 'Proteins', 'Food', 3, 6, NULL), 
  ('Spanish Omelette', 'Proteins', 'Food', 3, 6, NULL),    
  ('Escargot', 'Proteins', 'Food', 9.45, 10.50, 'Moving slowly.'), 
  ('Chicken (8oz Raw)', 'Proteins', 'Food', 8, 15, NULL), 
  ('English Pheasant (8oz Raw)', 'Proteins', 'Food', 10, 18, NULL),    
  ('Turkey (8oz Raw)', 'Proteins', 'Food', 10, 18, NULL), 
  ('Squab', 'Proteins', 'Food', 6, 12, 'No squabbling here.'),
  ('Beef (8oz ground)', 'Proteins', 'Food', 10, 18, NULL),    
  ('Bacon (8oz raw)', 'Proteins', 'Food', 12, 20, NULL), 
  ('Ham (4oz deli sliced)', 'Proteins', 'Food', 4, 6, NULL),
  ('Corned Beef', 'Proteins', 'Food', 5, 8, NULL), 
  ('Roast Beef ', 'Proteins', 'Food', 6, 9.50, NULL), 
  ('Smoked Beef', 'Proteins', 'Food', 6, 9.50, NULL), 
  ('Sirloin Steak', 'Proteins', 'Food', 10, 18, NULL),
  ('Lamb Chops ', 'Proteins', 'Food', 10, 18, NULL),
  ('Mince Pie', 'Proteins', 'Food', 9, 16, NULL), 
  ('Tenderloin Steak ', 'Proteins', 'Food', 10, 18, NULL),
  ('Virginia Ham', 'Proteins', 'Food', 10, 18, NULL), 
  ('Filet Mignon', 'Proteins', 'Food', 12, 20, NULL),    
  ('Boiled Ham', 'Proteins', 'Food', 8, 14, NULL), 
  ('Original Cheerios', 'Breakfast', 'Food', 1.25, 3, 'One of the most fun cereals to play with'), 
  ('Frosted Flakes', 'Breakfast', 'Food', 2.5, 5, 'The tiger mascot is scarier than clowns. But we promise it isn''t poisonous'),
  ('Honey Nut Cheerios', 'Breakfast', 'Food', 2, 4, 'A sweeter version of the classic.'), 
  ('Instant Oatmeal (Apples and Cinnamon)', 'Breakfast', 'Food', 2, 4, NULL), 
  ('Instant Oatmeal (Maple and Brown Sugar)', 'Breakfast', 'Food', 2, 4, NULL), 
  ('Granola', 'Grains', 'Food', 3.75, 6.5, NULL), 
  ('Rolled Oats', 'Grains', 'Food', 2.25, 4.25, NULL), 
  ('Froot Loops', 'Breakfast', 'Food', 2.25, 4.5, 'My kids can only eat this on vacation.'), 
  ('Original Special K', 'Breakfast', 'Food', 1.50, 4, NULL),
  ('Special K with Strawberries', 'Breakfast', 'Food', 2, 5, 'Buy by the bushel at Costco for the best price. Mmm dehydrated strawberries!'),
  ('Kix', 'Breakfast', 'Food', 1.15, 3.25, NULL),
  ('Berry Kix', 'Breakfast', 'Food', 1.25, 3.5, NULL),
  ('Honeycombs cereal', 'Breakfast', 'Food', 1.5, 3.5, 'Bees not included.'), 
  ('Fruity Pebbles', 'Breakfast', 'Food', 2.00, 4.5, NULL),
  ('Cocoa Pebbles', 'Breakfast', 'Food', 2.00, 4.5, NULL),
  ('Grapenuts', 'Breakfast', 'Food', 2.25, 5.75, 'Best eaten heated up with soy milk'),
  ('Capn Crunch', 'Breakfast', 'Food', 2.25, 4.5, 'The best part of Capn crunch is how it chews up the roof of your mouth.'), 
  ('White Monster Energy Drinks (4 pack)', 'Energy Drinks', 'Food', 2.00, 5.00, NULL), 
  ('French Roast Ground Coffee', 'Coffee', 'Food', 6.00, 12.00, NULL),
  ('Italian Roast Ground Coffee', 'Coffee', 'Food', 6.00, 12.00, NULL),
  ('Cold Brew Coffee', 'Coffee', 'Food', 2.00, 4.00, NULL),
  ('Coffee Mug (8oz)', 'Dishware', 'Home', 5.00, 8.00, NULL),
  ('Coffee Mug (12oz)', 'Dishware', 'Home', 5.50, 10.00, NULL),
  ('Coffee Mug (18oz)', 'Dishware', 'Home', 6.00, 12.00, NULL),
  ('Throw Blanket', 'Bed', 'Home', 8.00, 16.00, NULL),
  ('Twinsize Blanket', 'Bed', 'Home', 12.00, 24.00, NULL),
  ('Queensize Blanket', 'Bed', 'Home', 15.00, 30.00, NULL),
  ('Wooden Pencils', 'Office and School Supplies', 'Office Products', 0.50, 2.00, NULL),
  ('Mechanical Pencils', 'Office and School Supplies', 'Office Products', 0.80, 3.00, NULL),
  ('Backpack', 'Luggage', 'Apparel', 8.00, 20.00, NULL),
  ('Messenger Bag', 'Luggage', 'Apparel', 9.00, 28.00, NULL),
  ('Spiral Notebook', 'Office and School Supplies', 'Office Products', 0.50, 1.00, NULL),
  ('Dishsoap', 'Cleaning', 'Kitchen Supplies', 1.00, 3.00, NULL),
  ('Wireless Computer Mouse', 'Computer Devices', 'Technology', 8.00, 18.00, NULL),
  ('Wired Computer Mouse', 'Computer Devices', 'Technology', 6.00, 12.00, NULL),
  ('Tamagachi', 'Games', 'Technology', 8.00, 14.00, NULL),
  ('Tetris Handheld', 'Games', 'Technology', 6.00, 12.00, NULL),
  ('1000 pc Jigsaw Puzzle', 'Entertainment', 'Puzzles', 7.50, 12.00, NULL),
  ('2000 pc Jigsaw Puzzle', 'Entertainment', 'Puzzles', 8.00, 16.00, NULL),
  ('3000 pc Jigsaw Puzzle', 'Entertainment', 'Puzzles', 12.00, 22.00, NULL),
  ('Crossword Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Kakuro Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Ken-Ken Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Nonograms Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Logic Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Variety Puzzle Book', 'Puzzles', 'Books', 4.00, 8.00, NULL),
  ('Wireless Computer Keyboard', 'Computer Devices', 'Technology', 10.00, 20.00, NULL),
  ('Fitbit', 'Personal devices', 'Technology', 49.99, 119.99, NULL),
  ('Coffee Maker', 'Electronics', 'Kitchen Supplies', 25.00, 50.00, NULL),
  ('Super Fancy Espresso Maker', 'Electronics', 'Kitchen Supplies', 200.00, 359.99, 'Finicky but makes a mighty fine cup o'' joe'),
  ('Less Fancy Espresso Maker', 'Electronics', 'Kitchen Supplies', 50.00, 129.99, NULL),
  ('Stapler', 'Office and School Supplies', 'Office Products', 5.00, 10.00, NULL),
  ('Staples', 'Office and School Supplies', 'Office Products', 1.50, 3.00, NULL),
  ('3-Ring 1-inch Binder' , 'Office and School Supplies', 'Office Products', 1.50, 3.00, NULL),
  ('Super Fluffy Pillow (Blue)' , 'Housewares', 'Bedroom', 6.50, 15.00, 'Almost as fluffy as a cloud dog!'),
  ('Super Fluffy Pillow (White)', 'Housewares', 'Bedroom', 5.00, 12.50, 'Almost as fluffy as a cloud dog!'),
  ('Super Fluffy Pillow (Black)', 'Housewares', 'Bedroom', 6.50, 15.00, 'Almost as fluffy as a cloud dog!'),
  ('Super Fluffy Pillow (Purple)' , 'Housewares', 'Bedroom', 6.50, 15.00, 'Almost as fluffy as a cloud dog!'),
  ('Super Fluffy Pillow (Grey)' , 'Housewares', 'Bedroom', 6.50, 15.00, 'Almost as fluffy as a cloud dog!'),
  ('Basic Pillow (Blue)', 'Housewares', 'Bedroom', 4.75, 10.00, 'You should get the super fluffy pillow.'),
  ('Basic Pillow (White)' , 'Housewares', 'Bedroom', 4.00, 9.00, 'You should get the super fluffy pillow.'),
  ('Basic Pillow (Black)' , 'Housewares', 'Bedroom', 4.75, 10.00, 'You should get the super fluffy pillow.'),
  ('Basic Pillow (Purple)', 'Housewares', 'Bedroom', 4.75, 10.00, 'You should get the super fluffy pillow.'),
  ('Basic Pillow (Grey)', 'Housewares', 'Bedroom', 4.75, 10.00, 'You should get the super fluffy pillow.'),
  ('Pikachu pillow', 'Housewares', 'Bedroom', 12.00, 20.00, NULL),
  ('Paperclips' , 'Office and School Supplies', 'Office Products', 1.00, 2.50, 'For all your world domination needs.');
