💻 Computer Store Inventory Analysis
A data-driven SQL project focused on inventory management and supply chain optimization for a multi-category computer hardware store. This project covers everything from basic price filtering to complex cross-category performance analysis.
📊 Database Architecture
The store’s data is normalized across four primary tables to ensure data integrity:
Product: The master list of manufacturers (maker), unique models, and categories.
PC: Desktop-specific specs (Speed, RAM, HD, CD, Price).
Laptop: Portable-specific specs (Speed, RAM, HD, Screen, Price).
Printer: Imaging hardware (Color, Printer Type, Price).
🚀 Analytical Stages
Stage 1: Premium Inventory (Expensive Printers)
Objective: Identify high-value printers to optimize inventory capital.
Technical Focus: Basic data filtering and column selection.
Query Logic: Filter the Printer table for items with a price point ≥$200.
Stage 2: High-Performance Portables (Marketable Laptops)
Objective: Track manufacturers producing high-capacity laptops (≥1000 GB HD).
Technical Focus: INNER JOIN operations and multi-level sorting (ORDER BY).
Query Logic: Joined Product and Laptop tables to link manufacturers to specific hardware specs, sorted by capacity and speed.
Stage 3: Supply Chain Risk (The "Uniques")
Objective: Identify "Niche" manufacturers that only supply a single model.
Technical Focus: Nested Subqueries and GROUP BY aggregation.
Query Logic: Created a sub-total of models per manufacturer and filtered for those with a count of exactly 1 to evaluate supplier dependency.
Stage 4: Quality Assurance (Trust No One)
Objective: Identify manufacturers producing the slowest hardware across both PC and Laptop categories.
Technical Focus: CTEs (Common Table Expressions), UNION operators, and Scalar Subqueries.
Key Logic:
Used a WITH clause to create a unified table of all computers (stacking PCs and Laptops via UNION).
Identified the absolute minimum speed in the entire inventory.
Joined the result back to the Product table to find the responsible manufacturers.
🛠️ Technical Skills Demonstrated
Vertical Data Stacking: Using UNION to treat separate tables (PC & Laptop) as a single entity.
Relational Mapping: Navigating Foreign Key relationships between product categories and manufacturers.
Performance Filtering: Utilizing MIN() and MAX() within subqueries to find outliers.
Data Organization: Implementing ORDER BY with mixed directions (ASC/DESC) for nuanced reporting.
📂 Project Structure
Plaintext ├── Computer_Store.db # SQLite Database file ├── db_setup/ │ └── setup.sql # Database schema and initial data seeds └── stages/ ├── stage1_printers.sql ├── stage2_laptops.sql ├── stage3_uniques.sql └── stage4_qa_check.sql
🚦 How to Use
Initialize the database:
Bash sqlite3 Computer_Store.db < db_setup/setup.sql Run the quality assessment report (Stage 4):
Bash sqlite3 Computer_Store.db < stages/stage4_qa_check.sql ⚓ Closing Thoughts
This project transitions from simple data retrieval to identifying business risks and quality issues within a supply chain. It highlights the power of SQL Joins and Unions in creating a "Single Source of Truth" from fragmented hardware tables.