Member-only story

SQL Techniques

3 min readApr 25, 2025

Here, We will see some advanced techniques used in SQL.

There are advanced options in SQL like window function, common table expression , triggered, store procedure and cursor used in SQL.

when I was working as a data analyst, I was given a task where I used pivot and unpivot tables while doing analysis. you shold also know this.

You may convert a table-valued expression into another table by using the relational operators PIVOT and UNPIVOT. A table-valued expression can be rotated using PIVOT by dividing the expression’s unique values into numerous columns in the output. Additionally, PIVOT performs aggregations on any leftover column values that are needed for the final output as necessary. Rotating columns of a table-valued expression into column values is what UNPIVOT does in contrast to PIVOT.

-- Window Functions
SELECT FirstName, LastName, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
       RANK() OVER (ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

-- Pivot and Unpivot Operations
CREATE TABLE Sales (
    ProductID INT,
    SalesDate DATE,
    Amount DECIMAL(10, 2)
);INSERT INTO Sales (ProductID, SalesDate, Amount)
VALUES (1, '2024-03-01', 100.00),
       (2, '2024-03-01', 150.00),
       (1…