Vault™ > SQL250: Transact-SQL for Developers

Access to the Vault™ requires an active Microsoft Live Subscription.

Login Buy Subscription

SQL250: Transact-SQL for Developers

By Peter Avila
5 Days
Live Class Recording
February 24, 2025
Course Description

In this course, you’ll establish a solid foundational understanding of database concepts and terminology. Then you’ll master the use of various Microsoft tools to submit queries to quickly and efficiently get the results you want.

We teach students how to write a query the same way that SQL Server processes a SQL statement – following a step-by-step process for creating SQL queries from business requirements. This approach uses the natural way of breaking down a problem into logical steps. Each step can be validated before moving to the next step. This differs from most courses, which present SQL as a set of features.

Finally, the you’ll learn to harness the power of using T-SQL statements inside common database objects like Views, Stored Procedures and User-Defined Functions.

Class lectures highlight and explain Transact-SQL concepts which are reinforced with extensive follow-along demonstrations and hands-on labs.

Course Outline

The Basics

The material we cover in this module will be used throughout the course. Understanding objects and object names, the processing order of a query, literal values, identifiers and delimiters, SMSS basics, and more will be important often when you work with SQL.

  • Introducing databases
  • The database table
  • SQL basics
  • SSMS basics
  • Query basics
  • The metadata

The Relational Model

Any discussion about relational databases will inevitably require an understanding of the Relational Model. The RM describes the organization and structure of the database and will have an impact on almost everything in this course and back at work. Also, understanding this important topic is the first step in understanding and applying joins.

  • Introducing the Relational Model
  • The need for joins
  • Table relationships
  • OLAP and OLTP databases (Optional – Time permitting)

Getting the Data

The FROM clause always references a table. The table can be a database table, it can be generated by table-type functions, views, subqueries or joins. In this module, our focus will be on joins and subqueries.

  • The FROM clause
  • Single-join queries
  • Multiple-join queries
  • Derived-table Subqueries
  • Old join syntax

Filtering the Data

Now that we can use the FROM clause to define the data for our query, we need a way to select what we want to include in our results and what we want to exclude. In this module, we look at different ways we can use the WHERE clause to filter the data.

  • Introduction
  • Expressions
  • Data type precedence
  • Advanced filtering
  • WHERE clause optimization

Displaying the Data

At this point, we have learned how to define the data to query using the FROM and WHERE clauses. We are ready to display the results from our query. This is where we project columns and apply any sorting of the results. We will also take advantage of this opportunity to dive a little more deeply into functions.

  • Introduction
  • The column list
  • Functions
  • CASE expression

Aggregating the Data

All levels of management need information to provide decision support. Lower levels of management may need to make decisions about what products and how much to have in stock, and higher levels of management may need to understand industry trends to support strategic decisions about where to guide the company. In this module, you will learn how to create Aggregate queries; they provide this powerful type of high-level information.

  • Introduction
  • Aggregation with GROUP BY
  • Advanced aggregation
  • Ranking data

Maintaining the Data

Everything we’ve seen so far in this course has been about reading the data. In this module, we look at the three write operations that we collectively call data maintenance: Insert, delete, and update. We start with a look at transactions, and we end with a look at the Merge command that allows us to perform all three data maintenance operations in one pass of the data.

  • Transactions
  • Inserting data
  • Updating data
  • Deleting data
  • Merging data

Development in TSQL

So far in this course, we’ve seen how the conceptual schema works. In this module we turn to the external schema where we can encapsulate much of what we’ve learned. We will encapsulate our age calculation, see how a stored procedure can be used to save a 1:N relationship, process transactions, perform data validation, and more.

  • Basic Programming constructs
  • Scalar functions
  • Views
  • Inline table-valued functions
  • Stored procedures | Part 1
  • Stored Procedures | Part 2
  • Multi-statement table-valued functions
Course Content
Intoduction
Intoduction And Course logistics
Intoduction And Course logistics (Continuation after break)
Module 1: The basics
Introducing databases
The database table
The database table (Continuation after lunch)
SQL basics
SSMS basics
SSMS basics (Continuation after break)
Query basics
The metadata
Lab Introduction
Review
Day 1 review
Module 2: The Relational Model
Introducing the Relational Model
The need for joins
Table relationships
Table relationships (Continuation after lunch)
Table relationships (Continuation after break)
OLAP and OLTP databases (Optional Time permitting)
OLAP and OLTP databases (Continuation next day)
Review
Day 1 Review
Module 3: Getting the Data
The FROM clause
Single-join queries
Single-join queries (Continuation after break)
Single-join queries (Continuation after lunch)
Multiple-join queries
Multiple-join queries (Continuation after break)
Derived-table Subqueries
Old join syntax
Module 4: Filtering the Data
Introduction
Expressions
Expressions (Continuation after break)
Data type precedence
Advanced filtering
Advanced filtering (Continuation after lunch)
WHERE clause optimization
Module 5: Displaying the Data
Introduction
The column list
The ORDER BY clause
Functions
Review
Day 4 review
Module 5: Displaying the Data
CASE expression
Housekeeping
Housekeeping
Module 6 Aggregating the Data
Introduction
Aggregation with GROUP BY
Advanced aggregation
Advanced aggregation (Continuation after break)
Ranking data
Module 7: Maintaining the Data
Transactions
Inserting data
Deleting data
Updating data
Merging data
Module 8: Development in TSQL
Basic Programming constructs
Basic Programming constructs (Continuation after lunch)
Scalar functions
Views
Inline table-valued functions
Stored procedures: Part 1
Stored procedures: Part 2
Multi-statement table-valued functions