Using T-SQL to Manage Data, Query Data, and Program Databases

When the T-SQL in production scripts and stored procedures is written inconsistently, teams pay for it in failed data changes, slow queries, and errors that surface to users instead of being handled. This intermediate course gives an IT Director a way to standardize how database administrators and systems administrators write, query, and program against SQL Server.

Role fit: Built for IT Practitioners with at least a year of T-SQL experience and experienced DBAs looking to validate their Microsoft skills. It is not an introduction to SQL. Assign it to staff who already touch SQL Server Management Studio regularly.

Time investment: About 7.2 hours per learner across three skills: Manage Data, Query Data, and Program Databases. Teams can work through it one skill at a time over a few weeks.

Risk and reliability: The Program Databases skill covers transactions and TRY/CATCH error handling. Learners practice using error handling to control whether a transaction commits or rolls back, logging errors, and returning meaningful messages to calling applications. These habits reduce the risk of partial data changes.

Change management: The course also covers SARGable queries, when to choose joins over subqueries, APPLY, window functions, and querying XML and JSON data. Team Leads should expect to update internal script conventions as the team adopts these patterns.

CBT Nuggets delivery: Hands-on Virtual Labs use AdventureWorks and WideWorldImporters so staff practice INSERT, UPDATE, and DELETE without touching production. Group the course into a Playlist and track completion via Team Reporting.

IT teams complete this training to write T-SQL that holds up in production, not just in a query window. Each skill is taught in hands-on Virtual Lab sessions against sample databases such as AdventureWorks, so practitioners build working habits before they touch live data.

Here is how that shows up on the job:

• Safer data changes. Your team practices writing INSERT, UPDATE, and DELETE statements to modify data, with attention to the syntax and habits that help prevent avoidable mistakes. That means fewer risky changes and fewer escalations when database staff are working with live data.
• Faster, more predictable queries. Learners write SARGable filters and get the rules right for date equality and date range searches. They also learn when a join is the better choice and when a subquery works better, for example wrapping an aggregate so its result can be compared in the outer WHERE clause. These habits improve stored procedures that users rely on to search text and dates.
• Reporting without workarounds. Your team uses PIVOT, the OVER clause, and aggregate and ranking window functions to produce rankings and summaries directly in T-SQL.
• Working with non-relational data. Practitioners query XML and JSON columns, such as custom fields, tags, and stored user preferences, so application-driven data is no longer a black box.

The course also covers SQL Server Management Studio productivity, including context-sensitive help and help topics for built-in functions. These skills help Team Leads onboard new database staff faster.

Knowledge

• How INSERT, UPDATE, and DELETE statements modify data, and how to write basic and filtered SELECT queries, including character and date/time searches
• Popular built-in functions used in everyday queries and what makes a query SARGable
• When to use joins versus subqueries, including using subqueries to aggregate data and compare the result in an outer query, plus the proper use of the APPLY operator
• How the OVER clause supports aggregate and ranking window functions, and how pivoting reshapes result sets
• How SQL Server stores and queries XML and JSON data, such as custom fields and user preferences
• How transactions work, including how rollback protects data changes when a transaction needs to be reversed

Ability

• Write queries that modify data safely across tables, using the AdventureWorks and WideWorldImporters sample databases as practice ground
• Build efficient, SARGable filters for text and date searches inside scripts and stored procedures
• Query non-relational JSON and XML columns alongside relational data
• Rank, pivot, and aggregate data with window functions for reporting needs
• Apply transaction handling concepts to protect data changes and roll back work when needed
• Work faster in SQL Server Management Studio with context-sensitive help