SQL Server Training: Column Aliases, Basic Expressions, and Hands-on Lab

 

Introduction

This training module is designed for SQL Server professionals, focusing on column aliases, basic expressions, and a hands-on lab using a product catalog table. The content is tailored to real-world scenarios in Enterprise Resource Planning (ERP) systems, covering modules like Sales, Human Resources (HR), Production, Supply Chain Management (SCM), and Accounts. We’ll explore practical examples, best practices, exception handling (especially for NULL values), do’s and don’ts, pros and cons, and alternative approaches.

By the end of this module, participants will:

• Understand how to use column aliases to improve query readability and usability.

• Master basic expressions for calculations and data manipulation.

• Apply these concepts in a hands-on lab using a product catalog table.

• Handle NULL values effectively in ERP-related queries.

• Learn best practices and avoid common pitfalls in SQL Server.

---

1. Column Aliases in SQL Server

1.1 What Are Column Aliases?

Column aliases are temporary names assigned to columns or expressions in a SQL query to make the output more readable or to simplify referencing in subsequent parts of the query. Aliases are especially useful in:

• Improving readability for end-users or reports.

• Simplifying complex expressions in queries.

• Enabling compatibility with applications that expect specific column names.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

-- For expressions
SELECT expression AS alias_name
FROM table_name;

1.2 Why Use Column Aliases?

• Readability: Makes query results easier to understand (e.g., renaming UnitPrice to PricePerUnit).

• Application Compatibility: Aligns column names with front-end applications or reports.

• Simplifying Complex Queries: Shortens long expressions for easier reference in subqueries or joins.

• ERP Context: In ERP systems, aliases help present data in a business-friendly format for modules like Sales or Accounts.

1.3 Real-Life Examples in ERP Modules

Sales Module: Renaming Columns for a Sales Report

In a Sales ERP module, you might need to generate a report showing total sales by product with a user-friendly format.

Scenario: A sales manager wants a report showing product names, total quantity sold, and revenue, with column names that align with the company’s reporting standards.

Query:

SELECT 
    p.ProductName AS Item_Name,
    SUM(od.Quantity) AS Total_Units_Sold,
    SUM(od.Quantity * od.UnitPrice) AS Total_Revenue
FROM Sales.OrderDetails od
JOIN Production.Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName;

Explanation:

• ProductName AS Item_Name: Renames the product name for clarity in the report.

• SUM(od.Quantity) AS Total_Units_Sold: Summarizes the total quantity sold under a descriptive name.

• SUM(od.Quantity * od.UnitPrice) AS Total_Revenue: Calculates revenue and assigns a meaningful alias.

HR Module: Employee Salary Report

In an HR module, you might need to display employee details with calculated fields like annual salary.

Scenario: HR needs a report showing employee names and their annual salaries, with clear column names for a payroll dashboard.

Query:

SELECT 
    FirstName + ' ' + LastName AS Full_Name,
    MonthlySalary * 12 AS Annual_Salary
FROM HR.Employees;

Explanation:

• FirstName + ' ' + LastName AS Full_Name: Concatenates first and last names into a single column.

• MonthlySalary * 12 AS Annual_Salary: Calculates the annual salary and assigns a clear alias.

Production Module: Manufacturing Cost Analysis

In a Production module, you might calculate the total cost of producing items, including labor and material costs.

Scenario: A production manager wants a report showing the total cost of manufacturing each product.

Query:

SELECT 
    p.ProductName AS Product,
    (m.MaterialCost + l.LaborCost) AS Total_Production_Cost
FROM Production.Products p
JOIN Production.MaterialCosts m ON p.ProductID = m.ProductID
JOIN Production.LaborCosts l ON p.ProductID = l.ProductID;

Explanation:

• (m.MaterialCost + l.LaborCost) AS Total_Production_Cost: Combines material and labor costs into a single column with a descriptive alias.

SCM Module: Inventory Valuation

In a Supply Chain Management module, you might calculate the total value of inventory for each product.

Scenario: The SCM team needs a report showing the stock value for each product.

Query:

SELECT 
    ProductName AS Item,
    QuantityInStock * UnitPrice AS Inventory_Value
FROM Production.Products;

Explanation:

• QuantityInStock * UnitPrice AS Inventory_Value: Calculates the total value of inventory and assigns a meaningful alias.

Accounts Module: Accounts Receivable Aging

In an Accounts module, you might categorize invoices by how long they’ve been overdue.

Scenario: The finance team needs a report showing overdue invoices with clear column names.

Query:

SELECT 
    InvoiceID AS Invoice_Number,
    DATEDIFF(DAY, InvoiceDate, GETDATE()) AS Days_Overdue,
    InvoiceAmount AS Amount_Due
FROM Accounts.Invoices
WHERE InvoiceStatus = 'Unpaid';

Explanation:

• InvoiceID AS Invoice_Number: Renames for clarity in financial reports.

• DATEDIFF(DAY, InvoiceDate, GETDATE()) AS Days_Overdue: Calculates days overdue with a descriptive alias.

1.4 Best Practices for Column Aliases

• Use Meaningful Names: Choose aliases that clearly describe the data (e.g., Total_Revenue instead of Sum1).

• Follow Naming Conventions: Use consistent naming (e.g., CamelCase or underscores) based on your organization’s standards.

• Avoid Reserved Words: Don’t use SQL keywords like SELECT or FROM as aliases.

• Use Aliases in Complex Queries: Especially in joins or subqueries to simplify referencing.

1.5 Do’s and Don’ts

• Do:

  • Use aliases to make reports user-friendly.

  • Test aliases with front-end applications to ensure compatibility.

  • Use spaces in aliases (enclose in square brackets, e.g., [Total Revenue]).

• Don’t:

  • Use overly long aliases that make queries hard to read.

  • Overuse aliases when column names are already clear.

  • Use aliases that conflict with existing column names in the table.

1.6 Pros and Cons

• Pros:

  • Enhances readability for end-users and developers.

  • Simplifies integration with reporting tools like Power BI or Crystal Reports.

  • Reduces the need to modify front-end code to match database column names.

• Cons:

  • Overuse can make queries verbose.

  • Incorrect aliases can confuse users or break application logic.

1.7 Alternatives to Column Aliases

• Views: Create a view with predefined column names instead of using aliases in every query.

  CREATE VIEW Sales.ProductSales AS
  SELECT 
      p.ProductName AS Item_Name,
      SUM(od.Quantity) AS Total_Units_Sold,
      SUM(od.Quantity * od.UnitPrice) AS Total_Revenue
  FROM Sales.OrderDetails od
  JOIN Production.Products p ON od.ProductID = p.ProductID
  GROUP BY p.ProductName;

• Computed Columns: Add calculated columns directly to the table schema.

  ALTER TABLE Production.Products
  ADD Inventory_Value AS (QuantityInStock * UnitPrice);

---

2. Basic Expressions in SQL Server

2.1 What Are Basic Expressions?

Basic expressions in SQL Server are combinations of columns, literals, operators, and functions used to compute values. They are used in SELECT, WHERE, GROUP BY, and other clauses to manipulate data.

Types of Expressions:

• Arithmetic: +, -, *, /, % (e.g., calculate total cost).

• String: Concatenation (+ or CONCAT), substring, etc.

• Date/Time: DATEDIFF, DATEADD, etc.

• Logical: AND, OR, NOT, etc.

2.2 Real-Life Examples in ERP Modules

Sales Module: Discounted Price Calculation

Scenario: Calculate the discounted price for products during a sales promotion.

Query:

SELECT 
    ProductName AS Item,
    UnitPrice AS Original_Price,
    UnitPrice * 0.9 AS Discounted_Price
FROM Production.Products
WHERE DiscountAvailable = 1;

Explanation:

• UnitPrice * 0.9 AS Discounted_Price: Applies a 10% discount to the unit price.

HR Module: Employee Tenure

Scenario: Calculate how long employees have been with the company.

Query:

SELECT 
    FirstName + ' ' + LastName AS Full_Name,
    DATEDIFF(YEAR, HireDate, GETDATE()) AS Years_of_Service
FROM HR.Employees;

Explanation:

• DATEDIFF(YEAR, HireDate, GETDATE()): Calculates years of service.

Production Module: Cost Per Unit

Scenario: Calculate the cost per unit for manufactured products.

Query:

SELECT 
    ProductName AS Product,
    (MaterialCost + LaborCost) / QuantityProduced AS Cost_Per_Unit
FROM Production.Products;

Explanation:

• (MaterialCost + LaborCost) / QuantityProduced: Computes cost per unit.

SCM Module: Reorder Level Check

Scenario: Identify products that need reordering based on stock levels.

Query:

SELECT 
    ProductName AS Item,
    QuantityInStock - ReorderLevel AS Stock_Shortage
FROM Production.Products
WHERE QuantityInStock < ReorderLevel;

Explanation:

• QuantityInStock - ReorderLevel: Calculates how many units are below the reorder level.

Accounts Module: Invoice Interest Calculation

Scenario: Calculate interest on overdue invoices.

Query:

SELECT 
    InvoiceID AS Invoice_Number,
    InvoiceAmount AS Original_Amount,
    InvoiceAmount * 0.015 * DATEDIFF(DAY, DueDate, GETDATE()) AS Interest_Due
FROM Accounts.Invoices
WHERE InvoiceStatus = 'Unpaid' AND DueDate < GETDATE();

Explanation:

• InvoiceAmount * 0.015 * DATEDIFF(DAY, DueDate, GETDATE()): Calculates 1.5% interest per day overdue.

2.3 Handling NULL Values in Expressions

NULL values can cause issues in calculations, as any operation involving NULL typically results in NULL. In ERP systems, NULLs are common (e.g., missing salary data, unrecorded costs).

Techniques for Handling NULLs

• COALESCE: Returns the first non-NULL value.

• ISNULL: Replaces NULL with a specified value (SQL Server-specific).

• NULLIF: Converts a value to NULL if it matches a condition.

Example: Sales Module with NULL Discounts

Scenario: Calculate discounted prices, handling cases where the discount is NULL.

Query:

SELECT 
    ProductName AS Item,
    UnitPrice AS Original_Price,
    UnitPrice * (1 - COALESCE(DiscountPercentage, 0)) AS Discounted_Price
FROM Production.Products;

Explanation:

• COALESCE(DiscountPercentage, 0): Uses 0 if DiscountPercentage is NULL, ensuring the calculation doesn’t return NULL.

Example: HR Module with NULL Bonuses

Scenario: Calculate total compensation, including bonuses that may be NULL.

Query:

SELECT 
    FirstName + ' ' + LastName AS Full_Name,
    MonthlySalary + ISNULL(Bonus, 0) AS Total_Compensation
FROM HR.Employees;

Explanation:

• ISNULL(Bonus, 0): Replaces NULL bonuses with 0.

2.4 Best Practices for Basic Expressions

• Handle NULLs Explicitly: Always account for NULLs in calculations to avoid unexpected results.

• Use Descriptive Aliases: Pair expressions with meaningful aliases.

• Optimize Performance: Avoid complex expressions in WHERE clauses; consider computed columns or indexes.

• Test Edge Cases: Test expressions with NULLs, zero values, or extreme values.

2.5 Do’s and Don’ts

• Do:

  • Use COALESCE or ISNULL for NULL handling.

  • Break down complex expressions into smaller parts for readability.

  • Use parentheses to control operator precedence.

• Don’t:

  • Assume all data is non-NULL.

  • Use complex expressions in large datasets without indexing.

  • Mix data types in expressions without proper conversion (e.g., CAST or CONVERT).

2.6 Pros and Cons

• Pros:

  • Enables dynamic calculations without altering table data.

  • Supports complex business logic in queries.

  • Flexible for reporting and analytics.

• Cons:

  • Can impact performance on large datasets.

  • Complex expressions may reduce query readability.

  • NULL handling adds complexity.

2.7 Alternatives to Basic Expressions

• Computed Columns: Store calculated values in the table.

• Stored Procedures: Encapsulate complex logic for reuse.

• Views: Predefine calculations for frequent use.

---

3. Hands-on Lab: Querying a Product Catalog Table

3.1 Lab Setup

This lab uses a Product Catalog table to simulate real-world ERP scenarios. Participants will write queries using column aliases and basic expressions, handling NULL values, and applying best practices.

Table Schema: ProductCatalog

CREATE TABLE ProductCatalog (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    UnitPrice DECIMAL(10, 2),
    QuantityInStock INT,
    ReorderLevel INT,
    DiscountPercentage DECIMAL(5, 2) NULL,
    LastRestocked DATE
);

Sample Data

INSERT INTO ProductCatalog (ProductID, ProductName, Category, UnitPrice, QuantityInStock, ReorderLevel, DiscountPercentage, LastRestocked)
VALUES
(1, 'Laptop', 'Electronics', 999.99, 50, 20, 0.10, '2025-07-01'),
(2, 'Desk Chair', 'Furniture', 199.99, 30, 15, NULL, '2025-06-15'),
(3, 'Smartphone', 'Electronics', 699.99, 10, 25, 0.05, '2025-08-01'),
(4, 'Printer', 'Electronics', 299.99, 5, 10, NULL, '2025-05-20'),
(5, 'Office Desk', 'Furniture', 499.99, 20, 10, 0.15, '2025-07-10');

3.2 Lab Exercises

Exercise 1: Sales Report with Discounts

Objective: Create a sales report showing product names, original prices, and discounted prices, handling NULL discounts.

Query:

SELECT 
    ProductName AS Item,
    UnitPrice AS Original_Price,
    UnitPrice * (1 - COALESCE(DiscountPercentage, 0)) AS Discounted_Price
FROM ProductCatalog;

Expected Output:

Item	Original_Price	Discounted_Price
Laptop	999.99	899.99
Desk Chair	199.99	199.99
Smartphone	699.99	664.99
Printer	299.99	299.99
Office Desk	499.99	424.99

Exercise 2: Inventory Valuation

Objective: Calculate the total inventory value for each product.

Query:

SELECT 
    ProductName AS Item,
    QuantityInStock AS Stock,
    UnitPrice * QuantityInStock AS Inventory_Value
FROM ProductCatalog;

Expected Output:

Item	Stock	Inventory_Value
Laptop	50	49999.50
Desk Chair	30	5999.70
Smartphone	10	6999.90
Printer	5	1499.95
Office Desk	20	9999.80

Exercise 3: Reorder Alerts

Objective: Identify products below their reorder level and calculate the shortage.

Query:

SELECT 
    ProductName AS Item,
    QuantityInStock AS Current_Stock,
    ReorderLevel AS Minimum_Stock,
    ReorderLevel - QuantityInStock AS Units_Needed
FROM ProductCatalog
WHERE QuantityInStock < ReorderLevel;

Expected Output:

Item	Current_Stock	Minimum_Stock	Units_Needed
Smartphone	10	25	15
Printer	5	10	5

Exercise 4: Days Since Last Restock

Objective: Calculate how many days have passed since each product was last restocked.

Query:

SELECT 
    ProductName AS Item,
    LastRestocked AS Last_Stocked,
    DATEDIFF(DAY, LastRestocked, GETDATE()) AS Days_Since_Restock
FROM ProductCatalog;

Expected Output (assuming today is August 27, 2025):

Item	Last_Stocked	Days_Since_Restock
Laptop	2025-07-01	57
Desk Chair	2025-06-15	73
Smartphone	2025-08-01	26
Printer	2025-05-20	99
Office Desk	2025-07-10	48

3.3 Best Practices for the Lab

• Use meaningful aliases for all calculated columns.

• Handle NULL values in DiscountPercentage using COALESCE or ISNULL.

• Test queries with edge cases (e.g., zero stock, NULL discounts).

• Optimize queries by indexing frequently used columns like ProductID or QuantityInStock.

3.4 Do’s and Don’ts

• Do:

  • Validate query results against expected business logic.

  • Use comments to explain complex expressions.

  • Test for NULLs in all calculations.

• Don’t:

  • Ignore performance implications of expressions on large datasets.

  • Use ambiguous aliases like Col1 or Result.

3.5 Pros and Cons

• Pros:

  • Hands-on practice reinforces learning.

  • Real-world scenarios prepare participants for ERP challenges.

• Cons:

  • Limited dataset may not reflect real-world complexity.

  • Requires setup of sample data and schema.

3.6 Alternatives to Lab Setup

• Use Real ERP Data: If available, use sanitized ERP data for more realistic scenarios.

• Simulate Larger Datasets: Generate thousands of rows using scripts to test performance.

• Use Cloud-Based SQL Server: Use Azure SQL Database for accessibility.

---

4. Exception Handling: NULL Values in Calculations

4.1 Why NULLs Matter

NULLs represent missing or unknown data and can cause calculations to return NULL. In ERP systems, NULLs are common in fields like discounts, bonuses, or stock levels.

4.2 Techniques for Handling NULLs

• COALESCE: Returns the first non-NULL value.

  SELECT COALESCE(DiscountPercentage, 0) AS Discount
  FROM ProductCatalog;

• ISNULL: SQL Server-specific, replaces NULL with a specified value.

  SELECT ISNULL(DiscountPercentage, 0) AS Discount
  FROM ProductCatalog;

• NULLIF: Converts a value to NULL if it matches a condition.

  SELECT NULLIF(QuantityInStock, 0) AS Stock
  FROM ProductCatalog;

4.3 ERP Examples with NULL Handling

Sales: Discounted Price

SELECT 
    ProductName AS Item,
    UnitPrice * (1 - COALESCE(DiscountPercentage, 0)) AS Discounted_Price
FROM ProductCatalog;

HR: Total Compensation

SELECT 
    FirstName + ' ' + LastName AS Full_Name,
    MonthlySalary + ISNULL(Bonus, 0) AS Total_Compensation
FROM HR.Employees;

Production: Cost Calculation

SELECT 
    ProductName AS Product,
    ISNULL(MaterialCost, 0) + ISNULL(LaborCost, 0) AS Total_Cost
FROM Production.Products;

4.4 Best Practices for NULL Handling

• Always test for NULLs in critical calculations.

• Use COALESCE for portability across databases.

• Document NULL handling in queries for clarity.

4.5 Do’s and Don’ts

• Do:

  • Use COALESCE or ISNULL consistently.

  • Check for NULLs in WHERE clauses to avoid missing rows.

• Don’t:

  • Assume NULLs won’t occur in production data.

  • Use complex logic without testing NULL scenarios.

4.6 Pros and Cons

• Pros:

  • Prevents calculation errors.

  • Ensures accurate reporting.

• Cons:

  • Adds complexity to queries.

  • May require additional testing.

---

5. Conclusion

This training module has covered column aliases, basic expressions, and a hands-on lab using a product catalog table, with a focus on real-world ERP scenarios. Participants should now be able to:

• Use column aliases to improve query readability.

• Write basic expressions for calculations in ERP modules.

• Handle NULL values effectively.

• Apply best practices in SQL Server queries.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone