Querying SQL Server with Transact-SQL

Introduction to Transact-SQL and the Querying Tools

Before you can get started writing T-SQL code, you must master the tool that we will mainly use throughout this classroom training: SQL Server Management Studio.

• A Brief History of SQL Server
• Tabular Data
• Server-Client
• SQL Server Object Hierarchy
• SQL Server Management Studio
• Using the Graphical Query Builder in Management Studio
• Connecting to SQL Server from Excel or Power BI
• Connecting to SQL Server with Visual Studio Code
• LAB: Learning the Tools

SELECT Fundamentals

The SELECT statement is used to retrieve data from tables and views, often with filtering and sorting applied to shape the result set. This module also introduces expressions for creating computed columns, as well as set operations to combine or compare results from multiple queries.

• Basic SELECT Statement
• T-SQL Expressions
• Fetching Data from Tables
• Filtering Data with the WHERE Clause
• Sorting Data with the ORDER BY Clause
• Combining Result Sets: UNION, EXCEPT and INTERSECT
• Working with the Graphical Query Builder and Management Studio Templates
• LAB: Selecting from the AdventureWorks Database

Aggregating Data

Often the data stored in the database is more detailed than the result we are interested in. This module shows how to aggregate multiple values into a group, and how to compute aggregated values on top of these groups.

• Aggregation Functions
• GROUP BY Fundamentals
• Filtering on Groups with the HAVING Clause
• Computing Subtotals with ROLLUP, CUBE and GROUPING SETS
• LAB: Aggregating Data

Joining Multiple Tables

Most operational databases are normalized: the data is spread over multiple tables. But when building reports, we need to join these different tables to get the answer we need. That's why mastering the different techniques to combine tables in a single result is crucial. In this module we cover two important ways: via joins and via nested queries.

• Database Normalization
• Inner Join
• Left, Right and Full Outer Join
• Cross and Self Join
• Creating Nested Queries
• LAB: Joining Multiple Tables

Data Types

Data types directly affect how values are stored, compared, and calculated in T‑SQL. This module explains commonly used data types, how conversions occur, and why type precedence matters when working with expressions.

• Text
• Numerical Types
• Date and Time
• Type Precedence
• Type Conversion
• LAB: Datatypes

Modifying Data

Sometimes there is the need to insert new data into the database. Or old data needs an update or has to be deleted. This module shows you the basics of how this can be done.

• Inserting Data
• Updating Existing Records
• Deleting Data
• Constraints
• Understanding Transactions
• Truncating a Table
• LAB: Updating Customers in AdventureWorks

Advanced Functions and Operators

When querying data, simply retrieving rows is often not enough — you need to summarize, transform, and analyze results directly in SQL. This module introduces advanced T‑SQL functions and operators that enable powerful data reshaping, aggregation, ranking, and analytic calculations to produce more meaningful insights from your queries.

• T-SQL Functions
• PIVOT and UNPIVOT Operator
• Apply Operator
• Windowing Functions with OVER Clause
• Ranking Functions
• Analytic Functions: LEAD, LAG, ...
• LAB: Advanced Functions and Operators

Scripting

Sometimes queries need more than SELECT statements and require programmatic logic. This module introduces T‑SQL scripting features such as variables and IF…ELSE logic, and shows how stored procedures and functions can be used to reuse logic across your database.

• T-SQL Scripts
• Stored Procedures
• User-Defined Functions
• LAB: Creating Scripts, Procedures and Functions

Levels of Abstraction

Functions and procedures are ways to abstract away logic. Two other types of abstractions are introduced in this module: views and common table expressions (CTE's). Both will be an abstraction of data and will act as a table in your queries.
Notice that, in the last two modules, we only cover an introduction into these things. For a in depth coverage you better attend the Developing and optimizing SQL Server databases training.

• Common Table Expressions
• Views
• Levels of Abstraction
• LAB: Creating and Using CTE's and Views