SmartShop Inventory System SQL Project

Comprehensive submission of SQL queries generated, debugged, and optimized using Microsoft Copilot.

1. Basic SQL Queries

These queries were generated with Copilot to retrieve and filter data from the SmartShop Inventory System.

Example Queries:

-- Retrieve all products from the Products table
SELECT * FROM Products;

-- Retrieve products priced above $100
SELECT * FROM Products WHERE Price > 100;

-- Retrieve employees in Marketing department earning above 75,000, ordered by last name descending
SELECT * FROM Employees 
WHERE Department = 'Marketing' AND Salary > 75000 
ORDER BY LastName DESC;
        

2. Complex SQL Queries

Copilot assisted in generating JOINs, nested queries, and aggregations for advanced reporting.

Example Queries:

-- Join Products and Categories to list products with their categories
SELECT Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

-- Count total orders per customer
SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID;

-- Calculate average order value for the last 6 months
SELECT AVG(OrderAmount) AS AverageOrder
FROM Orders
WHERE OrderDate >= DATEADD(MONTH, -6, GETDATE());

-- Combine first and last names of employees
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;
        

3. Debugged and Optimized Queries

Copilot helped identify errors, optimize query structure, and suggested indexes for performance improvement.

Example Queries:

-- Delete customers inactive for over a year
DELETE FROM Customers
WHERE LastPurchaseDate < '2023-01-01';

-- Insert a new product into Products table
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (701, 'Smart Watch', 199);

-- Update product name
UPDATE Products
SET ProductName = 'Smartphone Pro'
WHERE ProductID = 101;

-- Use CTE to calculate total sales per region for filtering
WITH RegionalSales AS (
  SELECT Region, SUM(SalesAmount) AS TotalSales
  FROM Sales
  GROUP BY Region
)
SELECT * FROM RegionalSales
WHERE TotalSales > 10000;
        

4. Summary of Copilot Assistance

Basic Queries: Copilot generated foundational queries for retrieving and filtering data efficiently.
Complex Queries: Assisted in creating JOINs, nested queries, and aggregation statements to meet reporting requirements.
Debugging: Copilot identified syntax errors and logical issues in queries, suggesting corrections.
Optimization: Suggested query restructuring and indexing to improve performance for large datasets.
Learning Aid: Provided examples and guided proper SQL syntax while saving development time.