Subqueries, pivoting and ranking functions

Subqueries, pivoting and ranking functions

Subqueries:

A subquery is a query within a query that is used to retrieve data that will be used in the main query. The subquery can be nested within another query or used as a standalone query. The result of the subquery is used to filter or manipulate the result set of the main query. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements.

Pivoting:

Pivoting is a technique used to transform rows into columns in a query result set. It is used to summarize data and create more readable reports. The PIVOT operator in SQL Server 2008 allows you to rotate rows into columns based on the values in a specified column. Pivoting can be used with the GROUP BY clause to group the data and display it in a more meaningful way.

Ranking Functions:

Ranking functions are used to assign a rank to each row in a result set based on the values in one or more columns. There are four ranking functions in SQL Server 2008: RANK, DENSE_RANK, ROW_NUMBER, and NTILE. The RANK function assigns the same rank to rows with the same values, while the DENSE_RANK function assigns a unique rank to each distinct value. The ROW_NUMBER function assigns a unique number to each row, and the NTILE function divides the result set into a specified number of groups and assigns a group number to each row.

CTE:

CTE stands for Common Table Expression. A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a subquery, but it can be defined and referenced multiple times within the same query. A CTE is defined using the WITH keyword and can include one or more SELECT statements. It is useful for breaking down complex queries into more manageable pieces and improving query readability.

Apply for SQL Server 2008 Certification!!

https://www.vskills.in/certification/certified-sql-server-2008-programmer

Back to Tutorials