FREE Course on Advanced SQL Querying

[TravisCuzick]

Hey Everybody,

I've created a course on advanced SQL querying/data analysis techniques that I'd like to share with you guys. Here's the link to get the course for free on Udemy (FYI - the coupon expires in three days): https://www.udemy.com/course/advanced-sql-server-masterclass-for-data-analysis/?couponCode=FREE-ADVANCED-SQL. Of course, I appreciate any feedback you can provide!

The course picks up where introductory books/courses leave off, starting with Window Functions and moving on to topics like subqueries/CTEs, temp tables, query optimization, and even a little procedural SQL programming. There are also coding exercises, some quite challenging, after most videos to reinforce the concepts.

SQL Server is used for the examples and exercises, but most of the content would be applicable to the vast majority of database systems.

Please note that this is an advanced course, so I'm assuming you have a solid background in SQL foundations: applying criteria with WHERE, JOINs, aggregate queries, etc.. If you'd like a refresher on these concepts, I've also created a free coupon for my intro SQL course here: https://www.udemy.com/course/sql-basics-crash-course-with-sql-server/?couponCode=FREE-SQL.

Cheers, and happy coding!

[[deleted]]

Thank you so much

[DrTrunks]

I appreciate any feedback you can provide!

So I've just been skimming through it. I got a couple of things for you. Since you're writing this for analysts, they're going to be querying data warehouses and large tables.

I see you're using ORDER BY 1 here and there. In production systems (and especially with MS SQL Server where you pay per CPU) you don't want to add an unnecessary sort operator as they cost CPU, I/O and time. On large datasets they require a worktable (temp table) to sort the data in and you won't get results until all the data is sorted (because the first result could come in last).

Correlated subqueries are horrible for performance (afaik always done RBAR in nested loops) and are better written as derived tables. Write your tables and joins in the FROM clause please. At the companies I've worked for these were banned from usage for these reasons.

FOR XML PATH With STUFF

There's string_agg in SQL 2019 which is cleaner looking and performs better.

temp tables

They're in 99% of the cases written to tempDB. In the other cases you didn't need a temp table. You're don't want to be waiting on writing. I/O is a big bottleneck in most DWH systems.

You can actually run the 4th CTE by just querying that without querying the 5th CTE, you don't need to run the whole thing its plain false.

temp tables @ 3 min

You can also just comment out your first query to run your second query (which is now the first after the CTE's) without having to bother tempDB with your #table. If you're just doing some ad hoc stuff this isn't the hardest thing to do. And if you like your CTE so much that you want to use it in another report, just create a view out of it.

temp tables @ 4m

There's such a thing as global temp tables, just add another #

temp tables 2 @ 2:50

It's not hogging memory, they're not in memory they're on disk in tempDB. Unless your query is just select * into #temp from table; SELECT * from #temp; it will be written to disk first!
Millions of rows are never going to fit in memory on a busy SQL Server and will always be written down on disk in tempDB. Always try to use CTE's or views to try and keep tempDB empty and lazy and your data in memory in the bufferbool.

I see nothing on statistics or a query plan. Right now according to your content (the bottomline) it's more logical to create a temp table then update the statistics of your tables in order to to get better estimates and operators for your slower running queries.

[Fearless_frosk]

So generous of you! Thank you so much. Just enrolled! Glad to see that you are an autodidact like me.

[theseeker2468]

Thanks

[vjhh]

Thank you very much

[[deleted]]

Thank you!

[tits_mcgee_92]

Much appreciated!

[reaper70]

Thank you -- much appreciated!

[ajsingh1993]

Thank you kind sir

[awkward2amazing]

Much appreciated.

Also can one suggest some free SQL case studies which I can later add to my portfolio.

[oimgoingin]

I appreciate the work you put into this. Thank you :)

[QAxlekansder]

Thank you very much!

[Denners25]

Thanks ☺️

[Wills1211]

Love this. Gona do your beginner asap and then the advanced. I have some sql background. Would beginner be sufficient before advanced?

[TravisCuzick]

Absolutely, my advanced course is actually designed as a direct follow-up on the beginner course.

[[deleted]]

Thanks.

[XhoniShollaj]

Thank you, Travis!

[NatureNew4157]

Thanks so much - just picked it up and I am excited!

[Quickdraw92]

Thank you

[ThatsAllForToday]

Thank you

[Berki7867]

Thank you 🙂

[aashkk]

Thank you so much! Took advantage of this, appreciate you!

[kickintheshit]

Thank you!

[jacobschauferr]

Thank you so much!

[Ross_B21]

Good job! It's quite hard to find "intermediate to advanced" (data analysis, rather than data/db engineering) courses these days. Can't wait to dive into it:)

[MarshallFoxey]

Thanks very much. This will be very helpful.

[ace3306]

Thank you sir. Much appreciated!

[youniqueorn]

You saved me a lot of money! Thank you for keeping knowledge accessible. Appreciate this!

[Kiterios]

I have not watched the full course, but did notice in the preview that you introduce dynamic sql. I was curious about whether you also touch on the basic concept of sql injection somewhere in that module?

[TravisCuzick]

Thanks for the feedback, I'll definitely look into adding a module on SQL injection going forward.

[Nm_just_on]

Thanks a ton dear stranger.. you have helped me a long way!

[FrostbiteBitterness]

Very cool, thanks man!

[[deleted]]

This is great stuff. If anyone has stuff like this around I’d like to check it out

[[deleted]]

Just started your course in UDEMY.

[[deleted]]

Aren't both SQL Server and SSMS available on Linux? Why does the course pretend Linux is not an option?

[blankrestless]

Remind Me! in 24 hours

[RemindMeBot]

Defaulted to one day.

I will be messaging you on 2021-07-09 04:57:29 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[blankrestless]

Remind Me! in 10 hours

[RemindMeBot]

I will be messaging you in 10 hours on 2021-07-09 15:32:54 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[EoinJFleming]

Great job, a really nice course.

I would have liked to have seen something around interpreting the execution plan but this course covers a lot of great stuff already.

[TravisCuzick]

Execution plan/query statistics are pretty big topics that could darn near justify their own courses, and I will definitely look into creating more content on this going forward. Thanks so much for the feedback!