CTE in sql server Part 49
HTML-код
- Опубликовано: 24 сен 2012
- In this video we will learn about creating and referencing CTE's in SQL server. We will also learn how to create multiple CTE's using a single WITH clause.
Text version of the video
csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RUclips channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
ruclips.net/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
![Finesse2Tymes - Pretty Ricky [Official Music Video]](http://i.ytimg.com/vi/xIoP_mhYRT0/mqdefault.jpg)
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!
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
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!!
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.
You are the greatest teacher i have ever known. Thank for all you are doin'.
God bless you.
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
Your videos are saving me tons of hours that I would otherwise be spending reading books to learn the same!
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.
Agreed, really clear and concise info. Thanks very much for sharing
Perfect. Thanks a lot for your time and the best tutorial.
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.
Thank you very much for this video!
Mr Venkat You are genius man...
Clear and concise explanation. Well done.
Explaination and terminology is simple superb. Clear and easy to remember explaination
My pranams to you venkat sir!!!
Great Explanation - Thank you
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
Wow. Excellent Explanation.Makes it crystal clear. Very confident about my 70-461 exam
Thank you, very enjoy it and easy to understand. Very good works from you.
Thank you, great explanation in a clear way.
Tks my teacher, from Viet Nam
Good stuff mate, good stuff...
cool and clear tutorial. definetly subscription
this guy is really good at explaining cte
best CTE explanation on youtube!
very detailed explanation ! thanks a lot!
thank you, very helpful
Sir you have a beautiful voice!
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.
you are great !!
Good video. Thanks for posting.
Thanks a lot :)
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 so much Sir.
You the G.O.A.T !!
beautiful explanation
Excellent content 👍
nice explanation sir thanq you very much
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
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.
Thank you sir.
Thanks bro ✌✌
I learned ASP.net, asp.net MVC, SQL server, jquery from you.
Thanks 🥰
Thank you for this wonderful tutorial
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.
Hello Sir
Can you share video regarding Tabled Value Variables
very helpful
good tutorial
i love you voice bro
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.
Thank you sir..
Ur welcome
very nice
Just wow
Dears, how do you make Result and Message tabs disappear?
Thanks, very useful
Great
Thanks
can CTE be used for more than 2 tables?
hi, can we use insert/update/delete in SQL Functions,
Give video on how consume web api in wcf
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??
Why we use,and where we use...
And benefits ...
where cte are stored like in database or in tables
yes.
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.