• Home
  • PODCAST CODE
  • TSQL PODCASTS
  • GRUMPY OLD UNIVERSITY
  • BLOG
  • instructors
  • Beginning SQL Programming
  • Building a DW - FREE
  • TSQL - Stored Procedures
  • TSQL - Stock Simulation
  • TSQL Triggers - French
  • More
    • Home
    • PODCAST CODE
    • TSQL PODCASTS
    • GRUMPY OLD UNIVERSITY
    • BLOG
    • instructors
    • Beginning SQL Programming
    • Building a DW - FREE
    • TSQL - Stored Procedures
    • TSQL - Stock Simulation
    • TSQL Triggers - French
  • Home
  • PODCAST CODE
  • TSQL PODCASTS
  • GRUMPY OLD UNIVERSITY
  • BLOG
  • instructors
  • Beginning SQL Programming
  • Building a DW - FREE
  • TSQL - Stored Procedures
  • TSQL - Stock Simulation
  • TSQL Triggers - French

Learning the SQL Server BI stack the Grumpy Way!

Learning the SQL Server BI stack the Grumpy Way!Learning the SQL Server BI stack the Grumpy Way!Learning the SQL Server BI stack the Grumpy Way!

Grumpy Spring 2025 Curriculum

Beginning TSQL

Everything you need to get your career started as a Microsoft SQL Server Developer!

Module 01 - INTRODUCTION

FREE

Check out this module first. It will describe all the modules in this course. This way you will know what your getting into before you lay out your cash!

MODULE 02 - SSMS

This module will give you a very detailed tour of SQL Server Management Studio, much more in depth than my podcast.

MODULE 03 - DML

DML stands for data manipulation language. You will learn what a TSQL query and all the clauses and predicates that make up a query. Lots of demos, lots of sample code.

MODULE 04 - DDL

DDL stands for data declaration language, another sub language of TSQL. This will show you how to create and manage data objects like databases, tables, views and files and file groups. Check out my podcast on creating these objects to take a look at what's in store. Again, this module goes into more detail than the podcast. 

MODULE 05 - DCL

DCL stands for data control language. The third pillar of TSQL. (DML and DDL being the other two). Here we will learn all about users at the server and database level and also how to manage passwords and roles.

MODULE 06 - JOINS

If you looked at my podcast on TSQL DML then you have an idea on what JOINS are. This module really dives into these topics with a lot of complex examples and scenarios for generating large volumes of test data. I will show you how you can join to subqueries that act like tables including table variables.

MODULE 07 - ORDER BY

As I said in my podcast on the ORDER BY clause there is more to this than meets the eye. As with the other modules, this will go into more detail than my podcast and teach you reporting tricks that will empower your users. Like using Reporting Services reports that allow users to specify how many rows they want a report to return.

MODULE 08 - GROUP BY

This module will discuss not only the GROUP BY clause but the HAVING clause that will lay the ground work for using advanced aggregate and statistical functions that come with SQL SERVER.

MODULE 09 - AGGREGATE FUNCTIONS

This module covers more than the standard functions like sum, count, etc. We will discuss how to use the GROUP BY clause with CUBE and ROLLUP functions in some rather complex queries. Again, lot's of free code will b available so you can test these out.

MODULE 10 - UNION

This module will discuss the UNION operator which is used to join the results of multiple queries into a single result set. Although the concept is simple we will use complex queries and examples to show you the power of this operator.

MODULE 11 - STRING FUNCTIONS

I only touched the on the most common string functions in my podcast. This module covers the other string functions that come with SQL Server. Lot's of examples and codes to build up your data manipulation skills and toolkit.

MODULE 12 - DATE FUNCTIONS

This module covers other data functions not covered in my free podcast. Lot's of examples in a data warehouse setting. I will also show how to create a sophisticated calendar table that can be used on data warehouses, data marts and even transactional databases.

MODULE 13 - TSQL DEEP DIVE

This module ties in all that we learned in the other modules in complex examples. I will introduce two databases, one for a POWER PLANT scenario and one for a financial scenario where we simulate buying and selling of stocks! As with the other modules, lot's of demo code you can download.

MODULE 14 - WRAP UP

FREE

This is also a free module. It sums up what was learned in all the course modules. It introduces the next course.


INTERMEDIATE TSQL - Summer 2025

Advanced programming topics like stored procedures, functions, triggers and much more.

MODULE 01 - INTRODUCTION

FREE

 Check out this module first. It will describe all the modules in this intermediate course. This way you will know what your getting into before you lay out your cash! 

MODULE 02 - STORED PROCEDURES

TBA

This module introduces stored procedures. Stored procedures are like small sections of TSQL code that have a name and can be called over and over by a script or even other stored procedures. I will show you simple stored procedures that behave like calculators to complex procedures that allow you to pass parameters. The topics discussed in this module will take you to the next level of programming skills.

MODULE 03 - FUNCTIONS

TBA

Functions are almost like stored procedures but they return a value. Stored procedures do not although you can pass parameters that can be changed in the stored procedures and then the change can be seen by the calling script.  This module will be very demo intensive so you can learn the power of functions.

MODULE 04 - TRIGGERS

TBA

Next on the hit parade are triggers. These look like stored procedures but they are execute automatically on events, like when a table row is inserted, updated or deleted. A good example of a trigger is to use it to log changes to a table before you actually modify the table. We will learn all about before and after triggers. Needless to say, we need to understand the performance impact of triggers when we implement them.

MODULE 05 - CTE - COMMON TABLE EXPRESSIONS

TBA

This module covers Common Table Expressions affectionately known as CTEs. These operators allow you to work on sets of data much like the old fashioned cursor constructs. This module will also cover cursors and does a comparison of the two. Again, lot's of interesting demos and code you can download.

MODULE 06 - INTERSECT, EXCEPT & MERGE

TBA

Remember high school algebra? Well, TSQL provides a set of operators that let you perform operations on sets of data such as finding common or difference between values into two sets. The MERGE function is particularly interesting as it allows you to merge two sets of data and specify hat to do if new data is present, if existing data in one set has been changed in the other set or if data was deleted in the new set of data. These functions are useful and powerful when applied to data warehouse 

MODULE 07 - QUERY PLANS

TBA

Now this is an interesting and important topic. This is a must for anyone wanting to learn how to do serious performance monitoring and tuning of queries. SQL Server provides us with tools that show us the steps the query engine will take to process a query. Detailed information is provided that can help you identify bottlenecks and steps that eat up CPU and IO resources. This module will show you how to become an expert in performance tuning.

MODULE 08 - TRANSACTIONS

TBA

This module covers transactions, a powerful mechanism that is used to ensure that changes are either committed all at once, or rolled back if a piece of code that implements the transaction fails. You will learn all about the ACID properties of a transaction and learn how to write TQL code that is protected from other processes running on your server. Lot's of demo's and code. 

MODULE 09 - ISOLATION LEVELS

TBA

This module introduces isolation levels and shows you how to implement them. Isolation levels do just what the word implies, it allows you to isolate transactions from other transactions so that there is no negative effect on the data the transactions are manipulating. The classic banking example is used in demos to illustrate the concept.

MODULE 10 - TABLE LOCKS

TBA

Table locks are another way of protecting data. This module will discuss all you need to know about the different levels of locks and lock types you can use in your transactions. This module ties all the concepts you learned in transactions and isolation levels so you can create robust bullet proof code. Did I mention lot's of demos and free code examples to download?

MODULE 11 - CONDITIONAL & LOOPING LOGIC

TBA

Your knowledge of TSQL would not be complete if you did not learn how to implement conditional and looping logic in your scripts. This model covers for loops and other constructs that will let you control the flow of execution in your scripts, stored procedures, functions and even triggers.

MODULE 12 - SECURING YOUR DATABASE

TBA

This last module in my intermediate TSQL course goes really in depth in the topic of security. Something an aspiring DBA might want to take. I will show you how to create server and database logins and users and how to manage passwords. I will also discuss database roles and other toes of roles. 

This course available Spring 2025

ADVANCED CURRICULM

ADVANCED TSQL - Fall 2025

And even more advanced topics like XML, JSON and R support. Take your TSQL knowledge to the next level.

MODULE 01 - INTRODUCTION

FREE

Check out this module first. It will describe all the modules in this course. This way you will know what your getting into before you lay out your cash!

MODULE 02 - SQL SERVER & XML

TBA

This first advanced module shows you how XML and SQL Server play together. It will show you how to load XML documents into SQL Server database tables and how to produce XML documents from relational data stored in database tables. What more can I say? A powerful skill you need in your TSQL arsenal.

MODULE 03 - SQL SERVER & JSON

TBA

This next advanced module shows you how JSON and SQL Server play together. It will show you how to load JSON documents into SQL Server database tables and how to produce JSON documents from relational data stored in database tables. Again, what more can I say? Another powerful skill you need in your TSQL arsenal. 

MODULE 04 - ANALYTICAL FUNCTIONS

TBA

This is a really important module in this course. It will show you how to use advanced analytical and statistical functions in your queries. LEAD, LAG, STDDEV (standard deviation) are discussed. As usual, lot's of interesting demos and code you can download so you can play around and learn the concepts.

MODULE 05 - SQL SERVER & R

TBA

R is a powerful statistical and scientific platform. This module shows you how R and SQL Server play together. It will show you how to interface SQL Server database scripts with R scripts and how to exchange data between the two technologies. It will also show you how to call R scripts from SQL Server scripts and vice versa. Yes, another powerful skill you need in your TSQL arsenal.   

MODULE 06 - SQL SERVER & PYTHON

TBA

 This next advanced module shows you how PYTHON and SQL Server play together. It will show you how to interface SQL Server database scripts with PYTHON and how to exchange data between the two technologies. It will also show you how to call PYTHON scripts from SQL Server scripts and vice versa. Did I mention that this is another powerful skill you need in your TSQL arsenal?  

MODULE 07 - PIVOT QUERIES

TBA

PIVOT queries allow you to sway rows for columns and columns for rows, much like pivot tables in Microsoft Excel. This is a difficult top and at first, it can seem rather daunting. I will start of with simple examples and progress to more difficult examples via clips and code until you become an expert pivot query coder.

MODULE 08 - ADVANCED TABLE CONCEPTS

TBA

Up until now, my podcasts and courses have shown you how to create basic tables together with file groups and files. This module covers advanced table design topics like partitioned tables and indexes that are used to partition, or spread table data across multiple drives to increase query performance. Of course, lot's of demos and free code to download.

MODULE 09 - TEMPORAL TABLES

TBA

Normal tables show you data that was last loaded. If data was changed during the last load all prior versions of the data are gone unless you have a backup! Temporal tables (temporal meaning time) allow you to store data at different points in time in the same table so you can track the changes. This module shows you how to create, manage and query temporal tables.

MODULE 10 - MEMORY TABLES

TBA

Memory tables allow you to create tables entirely in memory as the name implies. This of course has significant implication on increasing performance when users require real-time data. This module shows you how to create, manage and query memory tables. I also discuss their limitations. Make sure your computer has lot's of memory if you want to try out the code examples.

MODULE 11 - STRETCH DATABASE

TBA

Stretch databases allow you to set up your database 

so it spans across your on premise and Azure cloud database. 

A key skill to have in your toolkit for cloud database architect roles.

MODULE 12 - GRAPH DATABASE

TBA

Complex data models need to represent 

relationships between objects called nodes. This module takes 

your database development skills to the next level by creating 

databases that support these structures. Lots of demos.dd a description about this item

https://grumpyoldguyuniversity.podia.com/beginning-sql-server-tsql-programming


Copyright © 2025 grumpy old it guy - All Rights Reserved.

Powered by

  • Home
  • PODCAST CODE
  • TSQL PODCASTS
  • GRUMPY OLD UNIVERSITY
  • Beginning SQL Programming