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. But there are other useful tools as well to discover in this
- Tabular Data
- SQL Server Object Hierarchy
- Introduction to SQL Server Management Studio
- Using the graphical query builder in Management Studio
- Working with Azure Data Studio
- Connecting to SQL Server from Excel or Power BI
- A brief history of SQL Server
- LAB: Learning the Tools
The SELECT statement is very important in T-SQL. It allows to retrieve data from tables and views. But that data
may need to be filtered, both in the rows and columns we return, and sorted. Another crucial concept discussed
in this module is that of expressions, which allow us to combine multiple columns into one computed column
before we return the result.
- Basic SELECT Statement
- T-SQL Expressions
- Fetching Data from Tables
- Filtering Data with the WHERE clause
- Sorting Data
- Combining Result Sets: UNION, EXCEPT and INTERSECT
- LAB: Selecting from the AdventureWorks Database
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. The
following topics are covered in this module:
- Using Aggregation Functions
- GROUP BY Fundamentals
- Filtering on Groups Using the HAVING Clause
- Computing Subtotals Using the ROLLUP and CUBE Clauses
- 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
- Database Normalization
- Inner Join
- Left, Right and Full Outer Join
- Cross Join
- Joining a table with itself
- Creating Nested Queries
- LAB: Joining Contacts, Employees and More
Each expression and each column has a datatype. It is important to be familiar with the most used types.
When we use multiple datatypes in one expression, there will be an implicit conversion to on type.
It is the type precedence that decides which type is being used, and this has big implications on your queris.
We can also do explicit conversion between types, such that e.g. the format of the date adheres to the locale.
This module goes over the important information surrounding types in T-SQL.
- Numerical Types
- Date and Time
- Type Precedence
- Type Conversion
- LAB: Datatypes
Advanced Functions and Operators
This module looks at some more advanced functions and operators that you can use. These can be handy to write
more advanced queries more easily. For instance, how do you retrieve for each country the three most important customers?
How do you calculate the day of week from a date? How do you convert data stored in columns into rows?
This module also looks into the OVER clause which has same capabilities as grouping data but with some perks.
- T-SQL Functions
- User-Defined Functions and Apply Operator
- OVER Clause
- Ranking Functions
- Analytic Functions
- PIVOT and UNPIVOT Operator
- LAB: Advanced Functions and Operators
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. The following topics are covered in this
- Inserting Data
- Updating Existing Records
- Deleting Data
- Intro Into Constraints
- Understanding Transactions
- Truncating a Table
- LAB: Updating Customers in AdventureWorks
At times you do not only need to write simple SELECT statements, but more or less "programs".
This module looks into scripting in T-SQL. You will learn about special T-SQL constructs like variables
and IF...ELSE statement. Often logic needs to be used multiple times at different places. For this you can create procedures
- T-SQL Scripts
- Stored Procedures
- Creating User-Defined Functions
- LAB: Creating Scripts, Procedures and Functions
Levels of Abstraction
Functions and procedures are ways to abstract away logic. On top of that, functions can be used in queries
and act either as scalars or tables. 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
- Levels of Abstraction
- LAB: Creating and using CTE's and views
The goal of this course is to provide students with the technical skills required to write basic Transact-SQL
queries for Microsoft SQL Server. The course covers querying SQL Server 2022 or previous versions as well as
Azure SQL Databases.
This course is intended for SQL Server database administrators and developers who are responsible for writing
queries but have no or very limited experience in writing T-SQL SELECT statements.
This introductory course requires no special skills.