I started studying for my Computer Science degree in 2011. In 2014 I relied heavily on this channel to carry out our final year project. I have been working as a Software Engineer since 2015 and to date I still refer to this channel as either a refresher on certain concepts that I forgot or to learn new technologies. God bless Venkat!
Mostly for beginners:- I don't know if anyone has any doubt on " how he joined two tables without having same /related column name/value ?" The answer is :- We can join two tables without having Pk / FK concept as long as the columns on the condition have the same data values or it can be converted into the same data value :) This video is crystal clear !! Thanks.
It's amazing how relevant Venkat SIR is even after 10 years. I was asked a question on CTE in one of my interview today. Find the third highest salary from an employee table with CTE AS (Select distinct Salary, ROW_NUMBER() OVER (order by Salary desc) as ranking from Employees) Select salary from CTE where ranking=3
CTE's made my life simple! I used to write complex and nested queries for producing complex reports. Now I can easily simplify them using CTE's, which are more simple and maintainable.
Venkat, I started watching your videos earlier today and I must tell you that your method of instruction is really good. I am preparing for MCSE 70-461 exam and I was struggling with CTE. However, your video made it absolutely clear. I have also placed an order for all of your SQL Server videos. Can't wait to have them downloaded. I am glad to have found your videos! Keep up the good work ...
+Sidhartha Chakravarty Thank you so much for the feedback. Means a lot. Glad the videos are useful. Dot Net & SQL Server training videos for aspiring web developers ruclips.net/user/kudvenkatplaylists?view=1&sort=dd Download our training videos and slides for offline viewing www.pragimtech.com/Order.aspx Code Samples, Text Version of the videos & PPTS on my blog csharp-video-tutorials.blogspot.com Tips to best utilise our channel ruclips.net/video/y780MwhY70s/видео.html To receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below ruclips.net/user/kudvenkat Please click the THUMBS UP button below the video, if you think you liked them Thank you for sharing the link with your friends who you think would also benefit from them Best Venkat
Your work and attention to detail are FANTASTIC! Thank you so much for this series. I intend to explore your other topics, as well. THANK YOU SO MUCH!!
You can reference the first CTE in the second CTE in the CTE declaration list. That's a very useful feature as you can build on CTE results on each subsequent CTE in the list. A CTE can be referenced within the next select, insert, (...) AND another CTE declared after it under the same WiTH clause.
You explained very brilliantly topics of CTE, table variable, temp table, views. But I have one question, ie. temp table, table variable and cte are doing same things so which one is best and for what scenario?. In which situation I should use CTE and not views/table variable. I will do my research too but since you're more knowledgable it doesn't hurt to question you. Thank you
I am not sure if this answer will help you now coz its been 6 years since you asked this question but i will still answer just in case somebody else have the same query. Derived tables and CTE's do almost the same thing but derived tables scope is within that same query but with CTE u can define the table and use it in the next immediate select insert etc query so in this case ur single query does not becomes complex and big. Table variables and temporary tables you can see the differnce on internet there are a lot of difference mentioned. I will mention just the one where u can pass table variables as argument to SP's and all. Views you create when u have to use it in future as well not for just one time. Derived tables and CTE's are one time things. Hope this answers ur query.
Hi Venkat, It was very informative and worth watching video .It would be great if you can share group by with having clause video or else send the link.
Dear Venkat I just want to correct you (temporary table will destroyed from the database when the connection which created it exist or we can explicitly drop it) thanks
HI Venkat One question here.. If we can only use CTE just immidately followed with select statement then what is the use of CTE instead of that we can use Temp table.
A CTE on its own would replace a derived table (subselect in from clause) not a temp table and you could reference it multiple times in the same select. It makes the code a lot cleaner. You can also reference a CTE in the next CTE listed under the same WITH statement which is very useful as you can build on calculations from one CTE to the next. THE most useful CTEs are the recursive ones. You can replace a lot of loops and cursors with recursive CTEs if you know what you're doing and get enormous increases (100x, 1000x) in performance.
Can anyone explain.. I''m confused in the increment part.. why we are incrementing the Level by using +1... the value can be derived from the second table.
why am i getting errors....WITH Employeecount (Departmentid,TotalEmployees) as (SELECT Departmentid, COUNT (*) AS TotalEmployees From [dbo].[EMPLOYEE] group by Departmentid) SELECT DeptName, TotalEmployees from [dbo].[DEPARTMENT] join Employeecount on [dbo].[DEPARTMENT].deptid = [dbo].[EMPLOYEE].Departmentid ORDER BY TotalEmployees
In Join Employeecount on [dbo].[DEPARTMENT].deptid = [dbo].[EMPLOYEE].Departmentid, after = you used [dbo].[EMPLOYEE].Departmentid where you should use Employeecount.Departmentid
Why do you need a cte here..? Why not just do this? Select dept.name, count* From employees emp Join department dept On emp.deptid = dept.empid Group by dept.name
I started studying for my Computer Science degree in 2011. In 2014 I relied heavily on this channel to carry out our final year project. I have been working as a Software Engineer since 2015 and to date I still refer to this channel as either a refresher on certain concepts that I forgot or to learn new technologies. God bless Venkat!
Mostly for beginners:- I don't know if anyone has any doubt on " how he joined two tables without having same /related column name/value ?"
The answer is :- We can join two tables without having Pk / FK concept as long as the columns on the condition have the same data values or it can be converted into the same data value :)
This video is crystal clear !! Thanks.
It's amazing how relevant Venkat SIR is even after 10 years. I was asked a question on CTE in one of my interview today. Find the third highest salary from an employee table with CTE AS
(Select distinct Salary, ROW_NUMBER() OVER (order by Salary desc) as ranking from Employees)
Select salary from CTE where ranking=3
Thank you very much. You should help Microsoft build their courses because they are a mess!You are a bless for us the newbies!!!
kudvenkat reallyappreciate
you
It was true 2 years ago, and it is still true now :-) Venkat for Microsoft Virtual Academy!
@@Trzbne AND STILL RELEVANT AFTER 4 YEARS NO DOUBT
CTE's made my life simple! I used to write complex and nested queries for producing complex reports. Now I can easily simplify them using CTE's, which are more simple and maintainable.
Venkat, I started watching your videos earlier today and I must tell you that your method of instruction is really good. I am preparing for MCSE 70-461 exam and I was struggling with CTE. However, your video made it absolutely clear. I have also placed an order for all of your SQL Server videos. Can't wait to have them downloaded. I am glad to have found your videos!
Keep up the good work ...
+Sidhartha Chakravarty Thank you so much for the feedback. Means a lot. Glad the videos are useful.
Dot Net & SQL Server training videos for aspiring web developers
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
Download our training videos and slides for offline viewing
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to best utilise our channel
ruclips.net/video/y780MwhY70s/видео.html
To receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
ruclips.net/user/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing the link with your friends who you think would also benefit from them
Best
Venkat
Hello I am also preparing to take MCSE 70-461 exam and if you have taken the exam do you have any advice how to study or any tips .
thanks
You are the greatest teacher i have ever known. Thank for all you are doin'.
God bless you.
Your work and attention to detail are FANTASTIC! Thank you so much for this series. I intend to explore your other topics, as well. THANK YOU SO MUCH!!
Your videos are saving me tons of hours that I would otherwise be spending reading books to learn the same!
Mr Venkat You are genius man...
You can reference the first CTE in the second CTE in the CTE declaration list. That's a very useful feature as you can build on CTE results on each subsequent CTE in the list. A CTE can be referenced within the next select, insert, (...) AND another CTE declared after it under the same WiTH clause.
Wow. Excellent Explanation.Makes it crystal clear. Very confident about my 70-461 exam
It is really amazing and it helps lot to know about the SQL in very short period. Thanks a lot. Keep it up Mr. Venkat. Hats off.
best CTE explanation on youtube!
Explaination and terminology is simple superb. Clear and easy to remember explaination
My pranams to you venkat sir!!!
Thank you, very enjoy it and easy to understand. Very good works from you.
this guy is really good at explaining cte
Clear and concise explanation. Well done.
Thank you, great explanation in a clear way.
Perfect. Thanks a lot for your time and the best tutorial.
Agreed, really clear and concise info. Thanks very much for sharing
very detailed explanation ! thanks a lot!
You the G.O.A.T !!
Sir you have a beautiful voice!
Tks my teacher, from Viet Nam
I learned ASP.net, asp.net MVC, SQL server, jquery from you.
That was very helpful, thanks. I recommend practicing on MS SQL Server Management Studio. CTEs don't really work well on MS mySQL Workbench
This is a tutorial for MS SQL Server not MySQL.
cool and clear tutorial. definetly subscription
Excellent content 👍
You explained very brilliantly topics of CTE, table variable, temp table, views. But I have one question, ie. temp table, table variable and cte are doing same things so which one is best and for what scenario?. In which situation I should use CTE and not views/table variable. I will do my research too but since you're more knowledgable it doesn't hurt to question you.
Thank you
I am not sure if this answer will help you now coz its been 6 years since you asked this question but i will still answer just in case somebody else have the same query.
Derived tables and CTE's do almost the same thing but derived tables scope is within that same query but with CTE u can define the table and use it in the next immediate select insert etc query so in this case ur single query does not becomes complex and big.
Table variables and temporary tables you can see the differnce on internet there are a lot of difference mentioned. I will mention just the one where u can pass table variables as argument to SP's and all.
Views you create when u have to use it in future as well not for just one time. Derived tables and CTE's are one time things.
Hope this answers ur query.
Hi Venkat,
It was very informative and worth watching video .It would be great if you can share group by with having clause video or else send the link.
+Akansha Soni its already there. Check his sql playlist. I think its in between the beginning 20 videos.
Thank you very much for this video!
Good stuff mate, good stuff...
you are great !!
Thanks bro ✌✌
Great Explanation - Thank you
nice explanation sir thanq you very much
Good video. Thanks for posting.
i love you voice bro
very helpful
Thank you so much Sir.
Thank you sir.
thank you, very helpful
beautiful explanation
Thanks 🥰
Dear Venkat I just want to correct you (temporary table will destroyed from the database when the connection which created it exist or we can explicitly drop it) thanks
Thanks a lot :)
HI Venkat One question here.. If we can only use CTE just immidately followed with select statement then what is the use of CTE instead of that we can use Temp table.
A CTE on its own would replace a derived table (subselect in from clause) not a temp table and you could reference it multiple times in the same select. It makes the code a lot cleaner. You can also reference a CTE in the next CTE listed under the same WITH statement which is very useful as you can build on calculations from one CTE to the next. THE most useful CTEs are the recursive ones. You can replace a lot of loops and cursors with recursive CTEs if you know what you're doing and get enormous increases (100x, 1000x) in performance.
Thanks
Just wow
Thank you sir..
Ur welcome
Great
good tutorial
very nice
Thank you for this wonderful tutorial
Can anyone explain.. I''m confused in the increment part.. why we are incrementing the Level by using +1... the value can be derived from the second table.
Hi
Hope you are fine, I want to ask what is the importance of CTE .we can achieve a result without CTE then why use??
Hello Sir
Can you share video regarding Tabled Value Variables
yes.
hi, can we use insert/update/delete in SQL Functions,
Thanks, very useful
can CTE be used for more than 2 tables?
Dears, how do you make Result and Message tabs disappear?
Give video on how consume web api in wcf
where cte are stored like in database or in tables
Why we use,and where we use...
And benefits ...
Is it possible to pass the list(array) as parameter to SQl?
muthu kumar use user defined table type
but totalEMployees isn't a field of tblDepartment, someone please explain
We are taking as alias for total count
why am i getting errors....WITH Employeecount (Departmentid,TotalEmployees) as (SELECT Departmentid, COUNT (*) AS TotalEmployees
From [dbo].[EMPLOYEE]
group by Departmentid)
SELECT DeptName, TotalEmployees
from [dbo].[DEPARTMENT]
join Employeecount
on [dbo].[DEPARTMENT].deptid = [dbo].[EMPLOYEE].Departmentid
ORDER BY TotalEmployees
In Join Employeecount on [dbo].[DEPARTMENT].deptid = [dbo].[EMPLOYEE].Departmentid,
after = you used [dbo].[EMPLOYEE].Departmentid where you should use Employeecount.Departmentid
I am so unlucky 😔 because it's too late to display in mobile RUclips channel .
, Now I'm probably Lucky Man because it pop-up on my screen..now ..
Why do you need a cte here..? Why not just do this?
Select dept.name, count*
From employees emp
Join department dept
On emp.deptid = dept.empid
Group by dept.name
I guess you mean emp.deptid= dept.deptid? The purpose of the tutor ihere is to explain basics of CTE which you can use for more complex queries.