T-SQL Implementation

👋

Single Post

T-SQL Implementation

Update November 21, 2024

Transact-SQL (T-SQL) is an extension of SQL (Structured Query Language) used by Microsoft SQL Server and Azure SQL Database. It adds procedural programming capabilities, making it a powerful tool for querying and manipulating data. With T-SQL, you can perform advanced operations such as conditional logic, loops, and error handling.

In this post, we’ll explore a practical example of implementing T-SQL to solve a real-world problem.

Scenario: Tracking Product Sales

Imagine you have a database for an e-commerce store, and you need to calculate total sales for each product. Additionally, you want to identify products with sales exceeding $10,000.

We’ll implement a T-SQL solution using:

Temporary Tables
Common Table Expressions (CTEs)
Conditional Logic

Step 1: Sample Database Setup

Let’s start by creating a sample database and inserting some data.

				
					-- Create a database
CREATE DATABASE SalesDB;
GO

-- Use the new database
USE SalesDB;
GO

-- Create a Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2)
);

-- Create a Sales table
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
    Quantity INT,
    SaleDate DATE
);

-- Insert sample data into Products
INSERT INTO Products (ProductID, ProductName, Price)
VALUES
(1, 'Laptop', 800),
(2, 'Smartphone', 500),
(3, 'Tablet', 300);

-- Insert sample data into Sales
INSERT INTO Sales (SaleID, ProductID, Quantity, SaleDate)
VALUES
(1, 1, 5, '2024-11-01'),
(2, 2, 20, '2024-11-01'),
(3, 3, 10, '2024-11-02'),
(4, 1, 7, '2024-11-03');

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Step 2: Calculate Total Sales

Using T-SQL, we calculate the total revenue for each product
sql:

				
					-- Calculate total sales using a CTE
WITH TotalSales AS (
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(s.Quantity * p.Price) AS TotalRevenue
    FROM Products p
    JOIN Sales s ON p.ProductID = s.ProductID
    GROUP BY p.ProductID, p.ProductName
)
SELECT * 
FROM TotalSales;

Step 3: Identify Top-Selling Products

We can add conditional logic to filter out products with total revenue exceeding $10,000.

				
					-- Filter products with revenue above $10,000
WITH TotalSales AS (
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(s.Quantity * p.Price) AS TotalRevenue
    FROM Products p
    JOIN Sales s ON p.ProductID = s.ProductID
    GROUP BY p.ProductID, p.ProductName
)
SELECT ProductID, ProductName, TotalRevenue
FROM TotalSales
WHERE TotalRevenue > 10000;

Step 4: Create a Temporary Table

For temporary analysis, we can store the results in a temporary table.

				
					-- Create a temporary table
CREATE TABLE #TopSellingProducts (
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalRevenue DECIMAL(15, 2)
);

-- Insert filtered results into the temporary table
INSERT INTO #TopSellingProducts
SELECT ProductID, ProductName, TotalRevenue
FROM (
    WITH TotalSales AS (
        SELECT 
            p.ProductID,
            p.ProductName,
            SUM(s.Quantity * p.Price) AS TotalRevenue
        FROM Products p
        JOIN Sales s ON p.ProductID = s.ProductID
        GROUP BY p.ProductID, p.ProductName
    )
    SELECT ProductID, ProductName, TotalRevenue
    FROM TotalSales
    WHERE TotalRevenue > 10000
) FilteredSales;

-- View the temporary table
SELECT * FROM #TopSellingProducts;

-- Drop the temporary table
DROP TABLE #TopSellingProducts;

Conclusion

This example demonstrates how T-SQL enables powerful data manipulation and querying capabilities. We utilized CTEs, conditional logic, and temporary tables to analyze sales data and identify top-performing products.

T-SQL’s flexibility makes it invaluable for scenarios requiring dynamic queries, advanced calculations, and data processing workflows.

Further Exploration

Learn about T-SQL functions like ROW_NUMBER(), RANK(), and OVER().
Dive into error handling with TRY...CATCH.
Explore stored procedures and triggers for automation.

With T-SQL, you can unlock advanced database functionalities and take your SQL skills to the next level!

Written by

Fahad Hossain

CEO

Single Post