Content

• CTE
• Ranking Functions
• Transactions

CTE (Common Table Expression)

A CTE is a temporary result set, that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.

Example :

With EmployeeCount(DepartmentId, TotalEmployees)
            as
            (
             Select DepartmentId, COUNT(*) as TotalEmployees
             from tblEmployee
             group by DepartmentId
            )
            
            Select DeptName, TotalEmployees
            from tblDepartment
            join EmployeeCount
            on tblDepartment.DeptId = EmployeeCount.DepartmentId
            order by TotalEmployees

We define a CTE, using WITH keyword, followed by the name of the CTE. In our example, EmployeeCount is the name of the CTE. Within parentheses, we specify the columns that make up the CTE. DepartmentId and TotalEmployees are the columns of EmployeeCount CTE. These 2 columns map to the columns returned by the SELECT CTE query.

EmployeeCount CTE is being joined with tblDepartment table, in the SELECT query, that immediately follows the CTE. Remember, a CTE can only be referenced by a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.

---

Ranking Functions

• Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause
• ORDER BY clause is required
• PARTITION BY clause is optional
• When the data is partitioned, rank is reset to 1 when the partition changes

Difference between Rank and Dense_Rank functions

Rank function skips ranking(s) if there is a tie where as Dense_Rank will not.

Example

SELECT Name, Salary, Gender,
            RANK() OVER (ORDER BY Salary DESC) AS [Rank],
            DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
            FROM Employees

Id	Name	Gender	Salary
1	Ashish	Male	70000
2	Ashutosh	Male	70000
3	Shree	Male	90000
4	Swapnil	Male	50000
5	Shweta	Female	45000
6	Jenny	Female	35000

Result

Name	Gender	Salary	Rank	DenseRank
Shree	Male	90000	1	1
Ashish	Male	70000	2	2
Ashutosh	Male	70000	2	2
Swapnil	Male	50000	4	3
Shweta	Female	45000	5	4
Jenny	Female	35000	6	5

RANK() and DENSE_RANK() functions with PARTITION BY clause : Notice when the partition changes from Female to Male Rank is reset to 1

SELECT Name, Salary, Gender,
                RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS [Rank],
                DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC)
                AS DenseRank
                FROM Employees

Name	Gender	Salary	Rank	DenseRank
Shree	Male	90000	1	1
Ashish	Male	70000	2	2
Ashutosh	Male	70000	2	2
Swapnil	Male	50000	4	3
Shweta	Female	45000	1	1
Jenny	Female	35000	2	2

---

Transcations

A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.

Transaction processing follows these steps:

• Begin a transaction.
• Process database commands.
• Check for errors. If errors occurred, rollback the transaction, else, commit the transaction

Example

Begin Transaction
update products set Qty=300 where id=1
Rollback Transaction

Begin Transaction
update products set Qty=300 where id=3
Commit Transaction