CTE in sql server Part 49 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to regime technologies I
am venket this is part 49 of sequel
server in this session we'll learn about
common table expressions before
continuing with the session I strongly
recommend to watch part 48 of this video
series common table expressions are
introduced in sequence over 2005 so what
is SE te s CDE is a temporary diesel set
that can be referenced within a select
insert update or delete statement that
immediately follows the CTE let's
understand what we mean by this
definition with an example so here you
can find the syntax for creating a CTE
so to create a CTE we use the width
keyword for example to create a stored
procedure we use create procedure
command similarly to create a table we
use create table command but to create a
CTE we use the width keyword so with
keyword followed by the name of the CD
and then the columns that would make up
your CTE so what is this EDS CTE is a
temporary result set so obviously a
result set will have columns so what are
the columns that would make up your
result set that is what you specify here
within a pair of parentheses then you
use the ask keyword then another set of
pay you know parentheses and then your
CTE query there let's look at this with
an example
we have two tables here TBL employee and
TBL department tables TBL employee has
got ID name gender and department ID
columns and TBL department has got
department ID and the department name
columns now let's say I want you to
write a query which gives me an output
that you can see here I want the total
number of employees by Department
obviously to achieve this output I don't
have to use a CTA I can simply achieve
this by using joins and group by but
since we are talking about CDEs here
let's see how to achieve this using a
CTE so I'm creating a CTE here called
employee count I'm using the width
keyword with employee count
so employee count is the name of the CDE
and department ID and total employees
are the columns that form you know the
city as and then this is your query so
if you look at this query it's pretty
straightforward
what is this query doing it's selecting
two columns Department ID and total
employees okay count of star as total
employees from TBL employee group by
department ID so this query will return
me the total number of employees by
department ID but if you look in the
output I want total number of employees
by department name okay so this select
query is nothing but your CTE query and
if you look at the select where it's
returning these two columns and these
two columns will map to these two
columns that you can see here you know
this CDE has got department ID and total
employees your select query has got
department ID and total employees so
these columns map to this CTE columns
okay and then look at the way we are
using the CTE this CTE is now being used
in the Select query so you're joining
the employee count CTE with TBL
department table and why are we doing
that because the employee count CTE has
got department ID and total employees
department table has got department ID
and Department name so we join the
employee count with TBL department on
the department ID column and we trade
three department name from TBL
department and total employees from
employee count CT and finally we order
by total employees so if you execute
this entire query we get the output that
you see here let's look at the CTE in
action so we are creating the CTE with
employee count these are the columns
that would make this CTE as and this is
the CTE query the Select query that
would actually serve the data for the
CTE so now when I actually execute this
select query alone look at this I get
this this is nothing but a temporary
results at a result set and for this
results
that you are giving a name called
employee count and this column
Department ID and total employees
returned by your select query is mapping
up to the columns you know in the city
okay and then what you are doing you are
joining this employee count with TBL
department table and the rest of the
stuff is pretty much a simple join here
and finally you are ordering by total
employees so if we execute this entire
staff we should get the output that we
are expecting all right now the columns
that you specify for the CTE here are
actually optional if you don't specify I
mean look at this
obviously when you execute this query
you get these two columns now if you
don't specify the column names for your
CTE here what is going to happen the
same column names are retained and then
used as the columns for this CTE so
let's execute this and we get the same
amount output still okay so it is also
possible that you can just say okay
instead of saying department ID
I can just say DEP ID and instead of
saying total employees I can say total
but then the moment you do that the
columns you know though this query is
returning them as department ID and
total employees you know for the CTE
they will be mapped to dep ID and total
employees will be mapped to total so
when you refer to that CTE in the select
statement you will have to change those
column names accordingly okay because if
I try to execute this now we get an
error invalid column name Department ID
because on employee counts CTE I don't
find a column called Department ID
because you have redefined you have
renamed that to be DEP DEP ID so you
should use DEP ID and similarly here you
have to specify that as total
so now when we execute this we should
succeed and get the same output all
right you know if you don't specify I
mean if you do specify the columns the
number of columns that you specify here
should match with the number of the
columns that your Select query is
returning if you say for example let's
say I have another column X Y Z for
example okay look at this your Select
query is actually returning two columns
Department ID and total employees
Department ID will be mapped to dep ID
total employees will be mapped to total
but there is no you know third column in
the Select query to map to XYZ so
obviously when I try to execute this it
throws an error stating that employee
count has fewer columns then were
specified in the column list obviously
that makes sense okay so the number of
columns that you specify here should
match the number of the columns returned
by the CDE query all right now this is
important to keep in mind you know if
you look at the definition of the CTA as
CTSA temperatures offset we know what we
mean by what we mean by that and then a
city is a temporary result set that can
be referenced within a select insert
update or delete statement that
immediately follows the city so if you
look at the way we are using the CTE we
are using the CTA within this select
statement if you look at the Select
statement it follows the CTE there is
nothing in between the CTE and the
Select statement that's why we are able
to use that but on the other hand it's
the same city except that you know I
have select hello here okay we are
saying we are using another select
statement and within the Select
statement I am not referring to the city
but then there is another select
statement here where I am referring to
this city
so obviously what's going to happen by
the time you know we execute this query
gets executed the CTE is gone because
this ete will be available to a select
in
that update or delete that immediately
follows the CTE if you try to use it
anywhere else then you know if you try
to do something else in between we get
an error let's look at this in action so
here we have the employee count CTE you
know it's the same thing and then we
have the Select hollow in between now
look at this if I comment that and then
if I try to execute this entire thing
you know we succeed we get the output
that we were getting before but on the
other hand look at this the moment i
uncomment this we are not using the CTE
in the select insert update or delete
statement that immediately follows the
CTE so obviously you cannot access this
employee account in this select
statement because this is not the
immediate select statement for this CTE
so let's see what happens if I try to
execute this we get an error set stating
common table expression defined but not
used okay so that's why in the
definition it's very clear you know a
CTE is a temporary result set that can
be referenced from within a select
insert update or delete statement that
immediately follows the CTE and I hope
that makes sense now all right it's also
possible to create multiple CTS
using a single wit class now we know
that to create a CTE we use the width
clause so you specify with keyword and
then the name of the CTE and then the
columns that would make up that CD as
your CTE query so this entire thing is
one CT but if you want an additional CD
using the same width keyword what you
can basically do is put a comma another
name of the CD another CD and then the
columns that would make up that CTE as
you know select query that can you know
that makes up this CD so it's possible
to create more than one CTE using the
width keyword just by separating them
with
so what we are doing here it's pretty
simple now if you look at this select
query all we are doing is we are getting
the total you know the total number of
employees by department but we're
filtering you know we are only doing it
for IT and payroll and here we are doing
it for HR and admin departments so
obviously when I execute this query you
should see we get the total number of
employees for IT and payroll departments
and when we execute this query you
should see that we get the total number
of employees for admin and HR department
and we have named our CTS
accordingly so this gives us employee
count by HR and admin department this
gives us the employee count by payroll
and IT department and the way we are
using that there is a select statement
look at this now you might be wondering
okay I am using the city within this
select statement this is another select
statement where I am using you know
maybe this city so I am not using them
within a select statement that
immediately follows city actually this
entire thing is treated as one statement
because you are using the Union keyword
and we are spoken about Union keyword in
the previous sessions of this video
series which basically unions the result
of this query with this query so this
entire thing is treated as one statement
so obviously now if I execute this
entire thing you should see total
employees by Department for all the four
departments okay so this example
illustrates that it's possible to create
multiple cities using a single with
clause on this slide you can find
resources for asp.net and C shop and
sequel server interview questions that's
it for today thank you for listening
have a great day