SQL SERVER Topic

Common Table Expressions in SQL
Common Table Expressions(CTE):-
  1. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
  2. A common table expression can include references to itself. This is referred to as a recursive common table expression.
  3. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table.
  4. CTE is standard ANSI SQL standard.
  5. Create a recursive query.
  6. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  7. Reference the resulting table multiple times in the same statement.
  8. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  9. Common table expression (CTE) is introduced in SQL server 2005.
Syntax:- 

  

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )


Arguments:-
  1. CTE_query_definition:-Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE.
  2. column_name:- Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
  3. expression_name:- Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.


Example:- We are using SQL Server. So first of all we create two tables named are tblStudent and tblBranch and insert some records as shown in below. 

  

-----------tblStudent--------------

CREATE TABLE tblStudent
(
  Id int Primary Key,
  Name nvarchar(30),
  Email nvarchar(200),
  Gender nvarchar(10),
  BranchId int
)


-----------tblBranch--------------

CREATE TABLE tblBranch
(
 BranchId int Primary Key,
 BranchName nvarchar(20)
)


----------- Insert records into tblStudent--------------

INSERT INTO tblStudent VALUES(1001,'Santosh','s@gmail.com','Male',1)
INSERT INTO tblStudent VALUES(1002,'Pooja kumari','p@gmail.com','Female',2)
INSERT INTO tblStudent VALUES(1003,'Arun kuamr','a1@gmail.com','Male',1)
INSERT INTO tblStudent VALUES(1004,'Pragya','p@gmail.com','Female',3)
INSERT INTO tblStudent VALUES(1005,'Manorma','m@gmail.com','Female',4)
INSERT INTO tblStudent VALUES(1006,'Ojha','o@gmail.com','Male',4)
INSERT INTO tblStudent VALUES(1007,'Kavita','k@gmail.com','Female',2)
INSERT INTO tblStudent VALUES(1008,'Dr. Prem kumar','pk@gmail.com','Male',4)
INSERT INTO tblStudent VALUES(1009,'Arti','a@gmail.com','Female',1)


----------- Insert records into tblBranch--------------

INSERT INTO tblBranch VALUES(1,'IT')
INSERT INTO tblBranch VALUES(2,'CSE')
INSERT INTO tblBranch VALUES(3,'ECE')
INSERT INTO tblBranch VALUES(4,'ME')


tblStudent


tblBranch


Write a query in SQL SERVER to get following output.


SQL Query:- 

  

With StudentCount(BranchId, TotalStudents)
as
(
 Select BranchId, COUNT(*) as TotalStudents
 from tblStudent
 group by BranchId
)

Select BranchName, TotalStudents
from tblBranch
join StudentCount
on tblBranch.BranchId = StudentCount.BranchId
order by TotalStudents