Transform data by using Transact-SQL (T-SQL)

Transact-SQL (T-SQL) is a powerful programming language used to interact with and manipulate data in Microsoft SQL Server. As a Data Engineer working with Microsoft Azure, it is essential to have a thorough understanding of T-SQL for transforming data to meet your business requirements. In this article, we will explore some key T-SQL techniques for data transformation.

1. SELECT Statement

The SELECT statement is the most basic form of retrieving data from a database. It allows you to specify the data you want to retrieve and how it should be presented. Here’s an example:

SELECT column1, column2
FROM table
WHERE condition;

You can use the SELECT statement to extract specific columns, apply aggregate functions (like SUM, AVG, COUNT), and filter data based on conditions.

2. JOINs

JOINs are used to combine rows from two or more tables based on related columns. They enable you to retrieve data from multiple tables with a single query. Here are some commonly used JOIN types:

• INNER JOIN: Returns only the matched records from both tables.
• LEFT JOIN: Returns all records from the left (first) table and the matched records from the right (second) table.
• RIGHT JOIN: Returns all records from the right (second) table and the matched records from the left (first) table.
• FULL JOIN: Returns all records when there is a match in either the left or right table records.

Here’s an example of an INNER JOIN:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

3. Aggregation Functions

T-SQL provides a variety of aggregate functions to perform calculations on a set of values. Some commonly used aggregate functions include COUNT, SUM, AVG, MAX, and MIN. Here’s an example:

SELECT COUNT(OrderID) as TotalOrders, SUM(Amount) as TotalAmount
FROM Orders;

This query will return the total number of orders and the sum of the order amounts from the Orders table.

4. CASE Statement

The CASE statement allows you to perform conditional logic in your T-SQL queries. It is useful for transforming data based on certain conditions. Here’s an example:

SELECT OrderID, Quantity,
   CASE
        WHEN Quantity > 10 THEN 'High'
        WHEN Quantity <= 10 THEN 'Low'
        ELSE 'N/A'
   END AS QuantityCategory
FROM OrderDetails;

This query assigns a category (‘High’ or ‘Low’) to each order based on the quantity.

5. Common Table Expressions (CTEs)

CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They provide a way to simplify complex queries and improve query readability. Here’s an example:

WITH CTE_TotalAmount AS (
   SELECT CustomerID, SUM(Amount) AS TotalAmount
   FROM Orders
   GROUP BY CustomerID
)
SELECT Customers.CustomerName, CTE_TotalAmount.TotalAmount
FROM Customers
JOIN CTE_TotalAmount ON Customers.CustomerID = CTE_TotalAmount.CustomerID;

In this query, the CTE_TotalAmount calculates the total order amount for each customer, which is then joined with the Customers table.

These are just a few examples of how you can use T-SQL to transform data in Microsoft Azure for the Data Engineering exam. By mastering T-SQL and its various features, you’ll be equipped to handle complex data transformation tasks efficiently.

Remember, practice is key to becoming proficient in T-SQL. The more you explore different scenarios and experiment with the language, the better you’ll become at transforming data to meet your specific requirements.