Cartesian Product and Rename Operations

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

-- SQL Attribute Renaming Patterns
 
-- 1. Simple column alias
SELECT 
    employee_id AS id,
    first_name AS name,
    annual_salary / 12 AS monthly_pay
FROM employees;
 
-- 2. Preserving qualified names through joins
SELECT 
    e.employee_id AS emp_id,
    d.department_id AS dept_id,
    d.name AS department_name,   -- Disambiguate 'name'
    e.name AS employee_name       -- from two tables
FROM employees e
JOIN departments d ON e.dept_id = d.department_id;
 
-- 3. Renaming in subqueries (derived tables)
SELECT 
    dept_summary.dept_name,
    dept_summary.emp_count
FROM (
    SELECT 
        d.name AS dept_name,
        COUNT(e.id) AS emp_count
    FROM departments d
    LEFT JOIN employees e ON e.dept_id = d.id
    GROUP BY d.id, d.name
) AS dept_summary
WHERE dept_summary.emp_count > 5;
 
-- 4. CTE with renamed columns (cleanest for complex queries)
WITH employee_details (emp_id, full_name, dept, salary) AS (
    SELECT id, name, department_id, annual_salary
    FROM employees
)
SELECT * FROM employee_details WHERE salary > 50000;

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

-- Consistent naming pattern examples
 
-- Entity_attribute pattern (recommended)
SELECT 
    c.customer_id,
    c.customer_name,
    c.customer_email,
    o.order_id,
    o.order_date,
    o.order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
 
-- Boolean naming with is_ prefix
SELECT 
    product_id,
    product_name,
    is_active,           -- Boolean flag
    is_featured,
    has_variants         -- Indicates related data exists
FROM products;
 
-- Aggregate naming with operation prefix
SELECT 
    customer_id,
    COUNT(*)          AS count_orders,
    SUM(order_total)  AS sum_revenue,
    AVG(order_total)  AS avg_order_value,
    MAX(order_date)   AS date_last_order
FROM orders
GROUP BY customer_id;

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

-- Strategy: Prefix every column with source table abbreviation
SELECT 
    -- Customer columns
    c.id        AS cust_id,
    c.name      AS cust_name,
    c.email     AS cust_email,
    
    -- Order columns
    o.id        AS order_id,
    o.date      AS order_date,
    o.status    AS order_status,
    
    -- Product columns
    p.id        AS prod_id,
    p.name      AS prod_name,
    p.price     AS prod_price,
    
    -- Computed columns
    (oi.quantity * p.price) AS line_total
    
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

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

-- Strategy: Name by semantic meaning, not just source
SELECT 
    c.name      AS buying_customer,
    o.date      AS purchase_date,
    p.name      AS purchased_product,
    oi.quantity AS units_bought,
    p.price     AS unit_price,
    (oi.quantity * p.price) AS purchase_amount
    
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
 
-- Result is self-explanatory without knowing source tables

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

-- Without aliases: confusing column names
SELECT 
    customer_id,
    COUNT(*),           -- Named 'count(*)' or 'count_all' or '?column?'
    SUM(amount),        -- Named 'sum' or 'sum(amount)' or similar
    SUM(amount) / COUNT(*),  -- Often just '?column?' or 'expr1'
    CASE 
        WHEN status = 'A' THEN 'Active'
        ELSE 'Inactive'
    END                 -- 'case' or unnamed
FROM orders
GROUP BY customer_id;
 
-- With proper aliases: clear, usable output
SELECT 
    customer_id,
    COUNT(*)                AS order_count,
    SUM(amount)             AS total_revenue,
    SUM(amount) / COUNT(*)  AS avg_order_value,
    CASE 
        WHEN status = 'A' THEN 'Active'
        ELSE 'Inactive'
    END                     AS customer_status
FROM orders
GROUP BY customer_id;

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

-- PostgreSQL: ALTER TABLE with RENAME COLUMN
ALTER TABLE employees 
    RENAME COLUMN emp_name TO employee_name;
 
-- MySQL: ALTER TABLE with CHANGE or RENAME COLUMN
ALTER TABLE employees 
    RENAME COLUMN emp_name TO employee_name;
-- Or with CHANGE (requires restating the type):
ALTER TABLE employees 
    CHANGE emp_name employee_name VARCHAR(100);
 
-- SQL Server: sp_rename stored procedure
EXEC sp_rename 'employees.emp_name', 'employee_name', 'COLUMN';
 
-- Oracle: ALTER TABLE RENAME COLUMN
ALTER TABLE employees 
    RENAME COLUMN emp_name TO employee_name;

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

-- Schema uses new name
ALTER TABLE employees RENAME COLUMN emp_name TO employee_name;
 
-- View provides backward compatibility
CREATE VIEW employees_legacy AS
SELECT 
    id,
    employee_name AS emp_name,  -- Old name for old code
    department_id,
    hire_date
FROM employees;
 
-- New code uses: SELECT employee_name FROM employees
-- Old code uses: SELECT emp_name FROM employees_legacy
 
-- Eventually deprecate the view after all code migrated

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

// TypeScript: ORM with name mapping (Prisma example)
model Employee {
  employeeId    Int      @id @map("emp_id")       // DB: emp_id
  firstName     String   @map("first_name")       // DB: first_name
  lastName      String   @map("last_name")        // DB: last_name
  departmentId  Int      @map("dept_id")          // DB: dept_id
  hireDate      DateTime @map("created_at")       // DB: created_at
  
  @@map("employees")                              // DB table: employees
}
 
// Application uses camelCase: employee.firstName
// Database uses snake_case: first_name
 
// API Response transformation
function toApiResponse(dbEmployee: DbEmployee): ApiEmployee {
  return {
    id: dbEmployee.emp_id,
    fullName: `${dbEmployee.first_name} ${dbEmployee.last_name}`,
    department: dbEmployee.dept_id,
    startDate: dbEmployee.created_at.toISOString()
  };
}