La Superstore Database

#This project for querying was fueled by the following data from a superstore:

CREATE TABLE superstore (
    item_id INTEGER PRIMARY KEY,
    item_name TEXT,
    category TEXT,
    price DECIMAL(10, 2),
    stock_quantity INTEGER,
    average_rating DECIMAL(3, 2)
);

INSERT INTO superstore (item_id, item_name, category, price, stock_quantity, average_rating)
VALUES
    (1, 'Stainless Steel Cookware Set', 'Kitchen Supplies', 89.99, 50, 4.6),
    (2, 'Memory Foam Mattress', 'Furnishings', 499.99, 30, 4.8),
    (3, 'Smart LED TV', 'Electronics', 549.00, 20, 4.5),
    (4, 'Robot Vacuum Cleaner', 'Appliances', 199.50, 40, 4.3),
    (5, 'Wireless Bluetooth Speaker', 'Electronics', 39.99, 60, 4.2),
    (6, 'Non-Stick Baking Set', 'Kitchen Supplies', 29.95, 80, 4.4),
    (7, 'Cotton Bedding Set', 'Furnishings', 89.00, 25, 4.7),
    (8, 'Smart Home Security Camera', 'Electronics', 79.95, 15, 4.1),
    (9, 'Air Purifier', 'Appliances', 129.50, 35, 4.6),
    (10, 'Premium Coffee Maker', 'Kitchen Supplies', 79.99, 50, 4.9),
    (11, 'Ergonomic Office Chair', 'Furnishings', 189.00, 20, 4.5),
    (12, 'Wireless Earbuds', 'Electronics', 49.99, 75, 4.3),
    (13, 'Slow Cooker', 'Appliances', 49.95, 30, 4.7),
    (14, 'Cutlery Set', 'Kitchen Supplies', 34.50, 40, 4.4),
    (15, 'Cozy Throw Blanket', 'Furnishings', 24.99, 100, 4.2);

#For this project, I used SQL to query the Superstore database to retrieve information about the items sold and their prices

# using select statement to order by price
SELECT *
FROM superstore
ORDER BY price;

#using aggregate functions to show all statistics on item prices
SELECT AVG(price)
FROM superstore;

SELECT SUM(price)
FROM superstore;

SELECT MAX(price)
FROM superstore;

SELECT MIN(price)
FROM superstore;

SELECT COUNT(price)
FROM superstore;

#showing price stats only for everything in the Kitchen Supplies category aka catagory
SELECT SUM(price), category
FROM superstore
WHERE category='Kitchen Supplies'
GROUP BY category;

#showing the amount of stock for the memory foam cloudy dreamy mattress!
SELECT stock_quantity, item_name
FROM superstore
WHERE item_name='Memory Foam Mattress';

#showing the highest rated item to point out the best of the best
SELECT average_rating, item_name, category
FROM superstore
ORDER BY average_rating DESC
LIMIT 1;