Cartesian Product and Rename Operations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

// Relational algebra with relation renaming
 
// Simple relation rename
E1 ← ρ_E1(Employee)
E2 ← ρ_E2(Employee)
 
// Self-join using renamed relations
ManagerPairs ← σ_{E1.ManagerID = E2.EmpID}(E1 × E2)
 
// Named intermediate results
HighEarners ← σ_{Salary > 100000}(Employee)
DeptHighEarners ← HighEarners ⋈ Department
Result ← π_{DeptName, Name, Salary}(DeptHighEarners)
 
// Compare to single expression (harder to read):
Result ← π_{DeptName, Name, Salary}(
    σ_{Salary > 100000}(Employee) ⋈ Department
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Basic table alias (implicit)
SELECT e.Name, e.Salary
FROM Employee e;              -- e is alias for Employee
 
-- Explicit alias with AS keyword (preferred for clarity)
SELECT e.Name, e.Salary
FROM Employee AS e;           -- AS makes aliasing explicit
 
-- Multiple aliases in a query
SELECT 
    e.Name AS EmployeeName,
    d.Name AS DepartmentName
FROM Employee AS e
JOIN Department AS d ON e.DeptID = d.ID;
 
-- Aliases are required for self-joins
SELECT 
    e1.Name AS Employee,
    e2.Name AS Manager
FROM Employee AS e1            -- First reference to Employee
JOIN Employee AS e2            -- Second reference (same table)
    ON e1.ManagerID = e2.EmpID;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- Basic derived table
SELECT dept_summary.DeptName, dept_summary.TotalSalary
FROM (
    SELECT 
        d.Name AS DeptName,
        SUM(e.Salary) AS TotalSalary
    FROM Department d
    JOIN Employee e ON d.ID = e.DeptID
    GROUP BY d.ID, d.Name
) AS dept_summary                -- Alias is REQUIRED for derived tables
WHERE dept_summary.TotalSalary > 500000;
 
-- Multiple derived tables
SELECT 
    sales.Region,
    sales.TotalSales,
    costs.TotalCosts,
    sales.TotalSales - costs.TotalCosts AS Profit
FROM (
    SELECT Region, SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY Region
) AS sales
JOIN (
    SELECT Region, SUM(Amount) AS TotalCosts
    FROM Expenses
    GROUP BY Region
) AS costs ON sales.Region = costs.Region;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Basic CTE
WITH high_earners AS (
    SELECT EmployeeID, Name, Salary, DeptID
    FROM Employee
    WHERE Salary > 100000
)
SELECT * FROM high_earners;
 
-- Multiple CTEs (comma-separated)
WITH 
    high_earners AS (
        SELECT EmployeeID, Name, Salary, DeptID
        FROM Employee
        WHERE Salary > 100000
    ),
    dept_high_earner_count AS (
        SELECT DeptID, COUNT(*) AS HighEarnerCount
        FROM high_earners              -- References previous CTE
        GROUP BY DeptID
    )
SELECT 
    d.Name AS DepartmentName,
    COALESCE(dhec.HighEarnerCount, 0) AS HighEarners
FROM Department d
LEFT JOIN dept_high_earner_count dhec ON d.ID = dhec.DeptID;
 
-- CTE with column renaming
WITH ranked_employees (emp_id, emp_name, dept, rank) AS (
    SELECT 
        EmployeeID,
        Name,
        DeptID,
        ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC)
    FROM Employee
)
SELECT * FROM ranked_employees WHERE rank <= 3;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Recursive CTE: Employee hierarchy
WITH RECURSIVE org_chart AS (
    -- Anchor member: top-level employees (no manager)
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member: employees with managers in current level
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1
    FROM Employee e
    JOIN org_chart oc ON e.ManagerID = oc.EmployeeID
)
SELECT 
    Level,
    REPEAT('  ', Level - 1) || Name AS OrgChart  -- Indented display
FROM org_chart
ORDER BY Level, Name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Basic view creation
CREATE VIEW active_employees AS
SELECT 
    EmployeeID,
    Name,
    Email,
    DeptID
FROM Employee
WHERE Status = 'ACTIVE';
 
-- Using the view (like a table)
SELECT * FROM active_employees WHERE DeptID = 10;
 
-- View with joins and computation
CREATE VIEW employee_details AS
SELECT 
    e.EmployeeID,
    e.Name AS EmployeeName,
    d.Name AS DepartmentName,
    e.Salary,
    e.Salary * 12 AS AnnualSalary,
    m.Name AS ManagerName
FROM Employee e
JOIN Department d ON e.DeptID = d.ID
LEFT JOIN Employee m ON e.ManagerID = m.EmployeeID;
 
-- View with column renaming
CREATE VIEW dept_statistics (dept_id, dept_name, employee_count, avg_salary) AS
SELECT 
    d.ID,
    d.Name,
    COUNT(e.EmployeeID),
    AVG(e.Salary)
FROM Department d
LEFT JOIN Employee e ON d.ID = e.DeptID
GROUP BY d.ID, d.Name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- Multi-stage data processing pipeline
WITH 
    -- Stage 1: Filter and clean raw data
    clean_orders AS (
        SELECT *
        FROM orders
        WHERE order_date >= '2024-01-01'
          AND status != 'CANCELLED'
          AND amount > 0
    ),
    
    -- Stage 2: Enrich with customer data
    enriched_orders AS (
        SELECT 
            o.*,
            c.segment AS customer_segment,
            c.region
        FROM clean_orders o
        JOIN customers c ON o.customer_id = c.id
    ),
    
    -- Stage 3: Aggregate by dimensions
    regional_metrics AS (
        SELECT 
            region,
            customer_segment,
            COUNT(*) AS order_count,
            SUM(amount) AS total_revenue,
            AVG(amount) AS avg_order_value
        FROM enriched_orders
        GROUP BY region, customer_segment
    ),
    
    -- Stage 4: Rank and identify top performers
    ranked_segments AS (
        SELECT 
            *,
            RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS segment_rank
        FROM regional_metrics
    )
 
-- Final output
SELECT *
FROM ranked_segments
WHERE segment_rank <= 3
ORDER BY region, segment_rank;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- Parallel computation with merge
WITH 
    -- Branch A: Sales metrics
    sales_metrics AS (
        SELECT 
            product_id,
            SUM(quantity) AS total_units,
            SUM(revenue) AS total_revenue
        FROM sales
        WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE)
        GROUP BY product_id
    ),
    
    -- Branch B: Inventory metrics
    inventory_metrics AS (
        SELECT 
            product_id,
            SUM(stock_quantity) AS current_stock,
            AVG(reorder_point) AS avg_reorder_point
        FROM inventory
        GROUP BY product_id
    ),
    
    -- Branch C: Product metadata
    product_info AS (
        SELECT id, name, category, unit_cost
        FROM products
        WHERE is_active = true
    )
 
-- Merge all branches
SELECT 
    p.name AS product_name,
    p.category,
    COALESCE(s.total_units, 0) AS monthly_sales,
    COALESCE(s.total_revenue, 0) AS monthly_revenue,
    COALESCE(i.current_stock, 0) AS stock_on_hand,
    CASE 
        WHEN COALESCE(i.current_stock, 0) < COALESCE(i.avg_reorder_point, 0)
        THEN 'REORDER'
        ELSE 'OK'
    END AS stock_status
FROM product_info p
LEFT JOIN sales_metrics s ON p.id = s.product_id
LEFT JOIN inventory_metrics i ON p.id = i.product_id
ORDER BY monthly_revenue DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- GOOD: Descriptive CTE names telling what data represents
WITH 
    active_customers AS (...),
    monthly_order_totals AS (...),
    customer_lifetime_value AS (...)
SELECT ...;
 
-- BAD: Generic, meaningless names
WITH 
    temp AS (...),
    data AS (...),
    result AS (...)
SELECT ...;
 
-- GOOD: Short but clear table aliases
SELECT e.name, d.name, o.total
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN orders o ON e.id = o.employee_id;
 
-- BAD: Cryptic single letters with no context
SELECT x.a, y.b, z.c
FROM table1 x
JOIN table2 y ON x.id = y.fk
JOIN table3 z ON y.id = z.fk;