SQL TUTORIAL - CTEs Part 1

A tutorial on how to write and use CTEs
How to calculate aggregations and then join to underlying base table
How to remove duplicates using CTEs
How to created nested CTEs
How to create stored procedures that contain CTEs

[@johnconnor9787]

your videos definitely should be in top of the results when people search for SQL Server tutorials

[@nasircallum411]

I guess I'm quite off topic but do anyone know a good site to watch new series online ?

[@bhavikpatel576]

These videos are fantastic. The only thing I would suggest is changing the zoom on your window. It would be so much nicer if the font size was increased by a factor of 2

[@BeardedDevData]

Thanks for the feedback, it is something I have implemented in my newer videos.

[@petergodzina5296]

Your videos are amazing. I'm currently working towards moving into a BI Developer role. I already have pretty decent SQL skills but your tutorials really fill in the gaps in some areas and your explanations are very clear.

[@BeardedDevData]

Thanks for the feedback and good look with the new role.

[@brittanyjat]

These videos are awesome! Thank you!

[@BeardedDevData]

Thanks for the feedback, much appreciated 👍

[@blackrahk2037]

Thanks brother. Like WOW. You the man.

[@isiomaikechukwu2173]

Thank you!

[@johnbreslin4521]

This is really useful explanation of CTEs. The only problem with removing data from my table is that I don't have a Id column so cannot use MIN(Id)

[@BeardedDevData]

If you cannot use another column because rows are exact duplicates then you can use ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT NULL) AS n within the CTE, this will give each duplicate a different number, you can then DELETE FROM CTE WHERE n > 1 to remove duplicates, give it a try and let me know how you get on

[@johnbreslin4521]

@@BeardedDevData Yes that does exactly what I want it to do to remove my Duplicates. Many thanks for your help.

[@superfreiheit1]

Its weird that you cant use the same cte muliple times.

[@pabeader1941]

My only complaint is this; ( is NOT a bracket. At best is might be called a Round Bracket but it is an Open Parenthesis. { is a Curly Bracket or possibly a Brace. [ is a Bracket or Square Bracket. It's just too confusing to hear ( being called a Bracket.

[@CaribouDataScience]

What is the difference between a CTE and a View?

[@BeardedDevData]

The first difference is scope, you can commonly refer to views in all queries whereas with a CTE you have to declare to it and refer to it in the same statement. If you find yourself writing the same query over and over then creating a view would save you time, the other benefit of views is that they can be used to separate the database from applications, prevent users accessing tables directly or simply rename columns to something more friendly. A typical use case for a CTE is where you need to perform multiple operations on a set of data such as if you want to find out the average of total customer spend, where first you need to calculate total spend per customer then find the average or rank data then apply a top n. Views can also be materialised, which can help speed up queries and CTEs can be used recursively to flatten hierarchies.

[@CaribouDataScience]

@@BeardedDevData thanks that helped.

[@steveschiff8640]

is it possible to get a copy of the databases you use in your videos?

[@BeardedDevData]

Unfortunately not, I don’t have them anymore but I am adding scripts to create the objects required in my latest videos. For ease I may consider using AdventureWorks in the future.

[@steveschiff8640]

I managed to install Adventure Works so it would be great if you use that one. In future, could you at least say which database each video is using, please?

[@steveschiff8640]

Also, it would be helpful if you could make clear the order we should watch all the videos say from start to finish. Would that be possible?

[@BeardedDevData]

Sure, no problem

[@sndrstpnv8419]

can you share code pls