Grumpy Spring 2024 Curriculum

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!

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

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.

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. 

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

 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! 

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.

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.

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.

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.

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 

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.

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. 

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.

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?

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.

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. 

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!

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.

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. 

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.

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.   

 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?  

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.

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.

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.

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.

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.

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