-- creating a store "Look gorgeous"
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  brand TEXT NOT NULL,
  product_type_id INTEGER,
  store_id INTEGER,
  price INTEGER,
  quantity INTEGER
  
);

CREATE TABLE product_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    description TEXT
    );
    
CREATE TABLE stores (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    location TEXT
    );
    
-- inserting values
INSERT INTO products (product_id, name, brand, product_type_id, store_id, price, quantity)
VALUES
	(1, "Hyalu B5 Hyaluronic Acid Serum", "LA ROCHE-POSAY", 1, 1, 35, 69),
	(2, "Squalane + Probiotic Gel Moisturizer", "BIOSSANCE", 2, 3, 40, 71),
	(3, "F-Balm Electrolyte Waterfacial", "DRUNK ELEPHANT", 4, 1, 37, 2),
	(4, "Vitamin Enriched Face Base","BOBBI BROWN", 3, 2, 40, 11),
	(5, "Umbra Tinte Physical Daily Defence SPF 30", "DRUNK ELEPHANT", 5, 2, 30, 3),
	(6, "CeraVe Cream to Foam Cleanser","CERAVE", 6, 1, 10, 93),
	(7, "Blemish + Age Defense", "SKINCEUTICALS", 1, 2, 90, 4),
	(8, "Soy Face Cleanser", "FRESH", 6, 2, 12, 34),
	(9, "Glow Daily Vitamin C Gel Cream", "REN CLEAN SKINCARE", 2, 1, 25, 45),
	(10, "Regenerist 3 Point Face Cream with Hyaluronic Acid", "OLAY", 3, 1, 15, 11),
	(11, "Murad Vita-C Glycolic Brightening Serum", "MURAD", 1, 2, 72, 5),
	(12, "The Ordinary Hyaluronic Acid 2% + B5", "The Ordinary", 2, 1, 6, 68),
	(13, "Transform Plus PHAT Glow Facial", "OLE HENRIKSEN", 7, 1, 36, 21),
	(14, "Skin Regimen Hydra Fluid ", "SKIN REGIMEN", 1, 2, 50, 11),
	(15, "CeraVe Smoothing Cleanser", "CERAVE", 6, 3, 12,47);
	
INSERT INTO product_types (description)
VALUES
    ("Serum"),
    ("Gel"),
    ("Cream"),
    ("Mask"),
    ("Sun protection"),
    ("Cleanser"),
    ("Scrub");
    
INSERT INTO stores (location)
VALUES
    ("CB7 4DV"),
    ("LE11 6YT"),
    ("KL4 5RV");
    
-- fetching values
SELECT * FROM products;

-- what products we are low of stock at?
SELECT product_id FROM products WHERE quantity < 20;

-- what the average prices for products of different types?---------------

-- SELECT product_type_id, AVG(price) 'Average Price' FROM products GROUP BY product_type_id;
-- let's make last query's output table more readable - showing product_types description instead of their id:
SELECT description, AVG(price) AS Average_Price FROM products 
JOIN product_types 
ON products.product_type_id = product_types.id 
GROUP BY product_type_id 
ORDER BY Average_Price;

-- how many products each store has? let's compare:
SELECT location, SUM(quantity) FROM products JOIN stores ON products.store_id = stores.id
GROUP BY store_id;