Superstore

SQL

Data Analysis

Database Management

Table of Contents

Organize Products by Ascending Price: This query is designed to list all details of items, organizing them in ascending order based on their price. It facilitates a holistic view of the store's inventory, priced from the least to the most expensive.
Aggregate Price by Category: Here, the focus shifts towards understanding the financial volume each category represents. By summing up the prices of items within each category, this query provides a clear picture of the total value generated by each product category.
Maximum Priced Item in Kitchen Supplies: Zeroing in on the "Kitchen Supplies" category, this query identifies the most expensive item, offering insights into premium product offerings within this specific category.
Identify Overstocked Items: Aiming to spotlight potential inventory excesses, this query lists items with a stock quantity exceeding 80. This information is crucial for inventory management and identifying items that may require promotional efforts to reduce surplus.

This project leverages SQL for in-depth analysis of the Superstore database, focusing on product listings, pricing, and category-based financial metrics. The queries are streamlined to provide essential insights into pricing order, category value, premium items in specific categories, and inventory levels.

This project employs the following Superstore data:

sql

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

sql

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

Organize Products by Ascending Price: This query is designed to list all details of items, organizing them in ascending order based on their price. It facilitates a holistic view of the store's inventory, priced from the least to the most expensive.

sql

SELECT *
FROM superstore
ORDER BY price;

Aggregate Price by Category: Here, the focus shifts towards understanding the financial volume each category represents. By summing up the prices of items within each category, this query provides a clear picture of the total value generated by each product category.

sql

SELECT SUM(price), category
FROM superstore
GROUP BY category;

Maximum Priced Item in Kitchen Supplies: Zeroing in on the "Kitchen Supplies" category, this query identifies the most expensive item, offering insights into premium product offerings within this specific category.

sql

SELECT MAX(price), category
FROM superstore
WHERE category LIKE 'Kitchen Supplies'
GROUP BY category;

Identify Overstocked Items: Aiming to spotlight potential inventory excesses, this query lists items with a stock quantity exceeding 80. This information is crucial for inventory management and identifying items that may require promotional efforts to reduce surplus.

sql

SELECT item_name
FROM superstore
WHERE stock_quantity > 80;