Skip to content

9.views_materialized_views.md

Latest commit

 

History

History
243 lines (207 loc) · 6.3 KB

9.views_materialized_views.md

File metadata and controls

243 lines (207 loc) · 6.3 KB

SQL Views and Materialized Views

This lesson covers SQL Views and Materialized Views - powerful database objects that help abstract complex logic and improve query performance.

Views

A view is a virtual table based on the result set of a SQL statement. Views can simplify complex queries and provide an additional security layer.

Basic View Creation

-- Basic view creation syntax
CREATE VIEW BookDetails AS
SELECT 
    b.BookID,
    b.Title,
    c.CategoryName,
    b.CurrentStock,
    COUNT(l.LoanID) as TimesLoaned
FROM Books b
LEFT JOIN Categories c ON b.CategoryID = c.CategoryID
LEFT JOIN Loans l ON b.BookID = l.BookID
GROUP BY b.BookID, b.Title, c.CategoryName, b.CurrentStock;

-- Using the view
SELECT * FROM BookDetails
WHERE CurrentStock < 5;

Views for Data Security

-- View that limits sensitive data
CREATE VIEW PublicMemberInfo AS
SELECT 
    MemberID,
    FirstName,
    LastName,
    City
FROM Members;

-- View with computed columns
CREATE VIEW LoanStatistics AS
SELECT 
    m.MemberID,
    m.FirstName + ' ' + m.LastName as FullName,
    COUNT(l.LoanID) as TotalLoans,
    AVG(DATEDIFF(day, l.LoanDate, l.ReturnDate)) as AvgLoanDuration
FROM Members m
LEFT JOIN Loans l ON m.MemberID = l.MemberID
GROUP BY m.MemberID, m.FirstName, m.LastName;

Updating Views

-- Updatable view (simple view on a single table)
CREATE VIEW ActiveBooks AS
SELECT BookID, Title, CurrentStock
FROM Books
WHERE IsActive = 1;

-- You can update through this view
UPDATE ActiveBooks
SET CurrentStock = CurrentStock + 1
WHERE BookID = 1;

Materialized Views

Materialized views store the result set physically, improving query performance for complex calculations. Note: Syntax varies by database system.

PostgreSQL Materialized Views

-- Create a materialized view
CREATE MATERIALIZED VIEW MonthlyLoanStats AS
SELECT 
    DATE_TRUNC('month', LoanDate) as Month,
    COUNT(*) as TotalLoans,
    COUNT(DISTINCT MemberID) as UniqueMembers,
    COUNT(DISTINCT BookID) as UniqueBooks
FROM Loans
GROUP BY DATE_TRUNC('month', LoanDate)
WITH DATA;

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW MonthlyLoanStats;

SQL Server Indexed Views

-- Create an indexed view in SQL Server
CREATE VIEW BookLoanSummary
WITH SCHEMABINDING
AS
SELECT 
    b.BookID,
    b.Title,
    COUNT_BIG(*) as LoanCount
FROM dbo.Books b
INNER JOIN dbo.Loans l ON b.BookID = l.BookID
GROUP BY b.BookID, b.Title;

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IX_BookLoanSummary
ON BookLoanSummary(BookID);

Practical Examples

Dashboard Views

-- Library Overview Dashboard
CREATE VIEW LibraryDashboard AS
SELECT
    (SELECT COUNT(*) FROM Books) as TotalBooks,
    (SELECT COUNT(*) FROM Members) as TotalMembers,
    (SELECT COUNT(*) FROM Loans WHERE ReturnDate IS NULL) as ActiveLoans,
    (SELECT COUNT(*) FROM Books WHERE CurrentStock = 0) as OutOfStock;

-- Category Performance
CREATE VIEW CategoryPerformance AS
SELECT 
    c.CategoryName,
    COUNT(DISTINCT b.BookID) as TotalBooks,
    SUM(b.CurrentStock) as TotalStock,
    COUNT(l.LoanID) as TotalLoans,
    ROUND(COUNT(l.LoanID) * 1.0 / COUNT(DISTINCT b.BookID), 2) as LoansPerBook
FROM Categories c
LEFT JOIN Books b ON c.CategoryID = b.CategoryID
LEFT JOIN Loans l ON b.BookID = l.BookID
GROUP BY c.CategoryName;

Analytics Views

-- Member Reading Patterns
CREATE VIEW MemberReadingPatterns AS
SELECT 
    m.MemberID,
    m.FirstName + ' ' + m.LastName as FullName,
    c.CategoryName,
    COUNT(*) as LoansInCategory,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY m.MemberID), 2) as CategoryPercentage
FROM Members m
JOIN Loans l ON m.MemberID = l.MemberID
JOIN Books b ON l.BookID = b.BookID
JOIN Categories c ON b.CategoryID = c.CategoryID
GROUP BY m.MemberID, m.FirstName, m.LastName, c.CategoryName;

-- Book Popularity Trends
CREATE VIEW BookPopularityTrends AS
SELECT 
    b.Title,
    DATEPART(month, l.LoanDate) as Month,
    DATEPART(year, l.LoanDate) as Year,
    COUNT(*) as MonthlyLoans,
    AVG(DATEDIFF(day, l.LoanDate, l.ReturnDate)) as AvgLoanDuration
FROM Books b
JOIN Loans l ON b.BookID = l.BookID
GROUP BY b.Title, DATEPART(month, l.LoanDate), DATEPART(year, l.LoanDate);

Exercise 🔴

-- Create tables for a bookstore database
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10,2),
    CategoryID INT,
    Stock INT
);

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    SaleDate DATE,
    CustomerID INT
);

-- Tasks:
-- 1. Create a view that shows daily sales summary
-- 2. Create a materialized view for monthly revenue by category
-- 3. Create a view for low stock alerts with complex conditions

-- Solutions:

-- 1. Daily Sales Summary View
CREATE VIEW DailySalesSummary AS
SELECT 
    s.SaleDate,
    COUNT(DISTINCT s.SaleID) as TotalTransactions,
    SUM(s.Quantity) as TotalItemsSold,
    SUM(s.Quantity * p.Price) as TotalRevenue
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY s.SaleDate;

-- 2. Monthly Revenue by Category (PostgreSQL Materialized View)
CREATE MATERIALIZED VIEW MonthlyRevenueByCategoryMV AS
SELECT 
    c.CategoryName,
    DATE_TRUNC('month', s.SaleDate) as Month,
    SUM(s.Quantity * p.Price) as Revenue,
    COUNT(DISTINCT s.SaleID) as NumberOfSales
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName, DATE_TRUNC('month', s.SaleDate)
WITH DATA;

-- 3. Low Stock Alert View
CREATE VIEW LowStockAlert AS
SELECT 
    p.ProductID,
    p.Name,
    p.Stock,
    AVG(s.Quantity) as AvgDailySales,
    p.Stock / NULLIF(AVG(s.Quantity), 0) as DaysUntilStockout,
    CASE 
        WHEN p.Stock = 0 THEN 'OUT OF STOCK'
        WHEN p.Stock < AVG(s.Quantity) * 7 THEN 'CRITICAL'
        WHEN p.Stock < AVG(s.Quantity) * 14 THEN 'LOW'
        ELSE 'OK'
    END as StockStatus
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name, p.Stock
HAVING p.Stock < AVG(s.Quantity) * 14 OR p.Stock = 0;

This exercise demonstrates:

  • Creating views for daily operational reporting
  • Using materialized views for performance-intensive calculations
  • Complex business logic in views with multiple conditions and calculated fields
  • Practical application of views in inventory management