DAY 20: SQL - Views, Stored Procedures, Optimization + WEEK 4 TEST

Exercise 1: Create a VIEW for Reusable Queries

Goal: Learn to create views that simplify complex queries and provide consistent data access.

Step-by-Step Instructions:

1. Understand views: A view is a saved SELECT query that acts like a virtual table
2. Create a sales summary view:

   CREATE VIEW vw_SalesSummary AS
   SELECT CustomerID, CustomerName, COUNT(*) AS OrderCount,
          SUM(Sales) AS TotalSales, AVG(Sales) AS AvgOrderValue
   FROM Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID
   GROUP BY CustomerID, CustomerName;

3. Use the view like a table:

   SELECT * FROM vw_SalesSummary WHERE TotalSales > 10000;

4. Drop and recreate view:

   DROP VIEW IF EXISTS vw_SalesSummary;
   CREATE VIEW vw_SalesSummary AS
   -- Updated version with new column
   SELECT CustomerID, CustomerName, COUNT(*) AS OrderCount,
          SUM(Sales) AS TotalSales, MAX(OrderDate) AS LastOrderDate
   FROM Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID
   GROUP BY CustomerID, CustomerName;

💡 Tips:

• Views don't store data: They execute the underlying query each time
• Naming convention: Use vw_ prefix to identify views
• Security benefit: Grant access to view instead of underlying tables
• Simplification: Hide complex JOINs from end users
• Performance: Views don't improve performance by themselves

✅ Expected Result: Views created and queryable like regular tables

Exercise 2: Create a Stored Procedure with Parameters

Goal: Write reusable SQL logic with parameters for dynamic execution.

Step-by-Step Instructions:

1. Create basic stored procedure:

   CREATE PROCEDURE sp_GetCustomerOrders @CustomerID INT
   AS BEGIN
       SELECT OrderID, OrderDate, Sales, ProductName
       FROM Orders O INNER JOIN Products P ON O.ProductID = P.ProductID
       WHERE O.CustomerID = @CustomerID
       ORDER BY OrderDate DESC;
   END;

2. Execute the stored procedure:

   EXEC sp_GetCustomerOrders @CustomerID = 123;

3. Create procedure with multiple parameters:

   CREATE PROCEDURE sp_GetSalesByDateRange
       @StartDate DATE, @EndDate DATE, @MinSales DECIMAL(10,2) = 0
   AS BEGIN
       SELECT OrderDate, SUM(Sales) AS DailySales
       FROM Orders
       WHERE OrderDate BETWEEN @StartDate AND @EndDate
       GROUP BY OrderDate
       HAVING SUM(Sales) >= @MinSales;
   END;

💡 Tips:

• Parameters: Can have default values (e.g., @MinSales = 0)
• Benefits: Reusable, parameterized, can include logic
• Security: Prevents SQL injection when used properly
• Performance: Execution plans cached for faster runs
• Debugging: Use PRINT statements to output variable values

✅ Expected Result: Stored procedures callable with parameters

Exercise 3: Create and Use Indexes for Performance

Goal: Understand indexing and create indexes to speed up queries.

Step-by-Step Instructions:

1. Create a non-clustered index:

   CREATE INDEX idx_Orders_CustomerID ON Orders(CustomerID);

2. Create composite index:

   CREATE INDEX idx_Orders_Date_Customer
   ON Orders(OrderDate, CustomerID);

3. Test performance difference:

   -- Check execution time before/after
   SELECT * FROM Orders WHERE CustomerID = 123;

4. Drop index if needed:

   DROP INDEX idx_Orders_CustomerID ON Orders;

💡 Tips:

• When to index: Columns in WHERE, JOIN, ORDER BY clauses
• Trade-off: Indexes speed reads but slow writes
• Don't over-index: Each index uses storage
• Column order matters: Most selective column first in composite indexes
• Unique index: Use CREATE UNIQUE INDEX to enforce uniqueness

✅ Expected Result: Indexes created, queries run faster

Exercise 4: Analyze Query Execution Plans

Goal: Learn to read execution plans to identify bottlenecks.

Step-by-Step Instructions:

1. Use EXPLAIN (PostgreSQL/MySQL):

   EXPLAIN SELECT * FROM Orders WHERE OrderDate > '2024-01-01';

2. Analyze a complex query:

   EXPLAIN ANALYZE
   SELECT C.CustomerName, SUM(O.Sales) AS TotalSales
   FROM Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID
   WHERE O.OrderDate >= '2024-01-01'
   GROUP BY C.CustomerName HAVING SUM(O.Sales) > 10000;

3. Look for problems: Table scans, high cost operations, missing indexes

💡 Tips:

• Table scan vs Index seek: Scan is bad (reads all), Seek is good (uses index)
• Focus on high cost: Operations with >10% cost
• Actual vs Estimated: Use EXPLAIN ANALYZE for actual stats
• Common fixes: Add indexes, rewrite query, update statistics
• Compare before/after: Check plan after adding indexes

✅ Expected Result: Ability to read plans and identify issues

Exercise 5: Query Optimization Best Practices

Goal: Apply best practices to write efficient SQL queries.

Step-by-Step Instructions:

1. Use EXISTS instead of IN:

   -- Faster for large datasets
   SELECT * FROM Customers C
   WHERE EXISTS (SELECT 1 FROM Orders O WHERE O.CustomerID = C.CustomerID);

2. Avoid SELECT *:

   -- Only select needed columns
   SELECT OrderID, OrderDate, Sales FROM Orders;

3. Use WHERE instead of HAVING:

   -- Filter before aggregation when possible
   SELECT Category, COUNT(*)
   FROM Products WHERE Category = 'Electronics'
   GROUP BY Category;

4. Avoid functions on indexed columns:

   -- Use range instead of function
   SELECT * FROM Orders
   WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

💡 Tips:

• Limit result sets: Use TOP/LIMIT during testing
• Batch operations: INSERT/UPDATE many rows at once
• Use appropriate data types: Smaller types improve performance
• Update statistics: Keep table stats current
• Monitor slow queries: Use query logs to find optimization candidates

✅ Expected Result: Understanding of optimization techniques

🔄 Connection to Excel: Like optimizing Excel formulas - avoid volatile functions, use efficient calculations!