Itzik Ben-Gan
  • Home
  • Books
  • T-SQL Courses
  • Resources
  • About
  • More
    • Home
    • Books
    • T-SQL Courses
    • Resources
    • About
Itzik Ben-Gan
  • Home
  • Books
  • T-SQL Courses
  • Resources
  • About

T-SQL Fundamentals for SQL Server

 5-Day (TSQL Fundamentals 2022.1) 


Download PDF or browse the course outline below

T-SQL Fundamentals 2022.1 Outline (pdf)

Download

Description

Developed and delivered by Itzik Ben-Gan, this T-SQL Fundamentals course is intended for T-SQL developers, DBAs, data scientists, analysts, and power users. Those who work with SQL Server or Azure SQL and need to write queries and develop code using Transact-SQL—SQL Server’s dialect of the ISO/IEC and ANSI standards for the language SQL. The course covers querying topics, including retrieving and modifying data, and also provides an overview of programmable objects supported by SQL Server.


This course is intended both for people who just started working with SQL Server, as well as those with some experience. If you are self-taught, and gained your knowledge “by the seat of your pants,” this course can fill the gaps in your knowledge and teach you how to think correctly in SQL terms. This course focuses on the foundations of T-SQL explaining the logic behind the language and its elements.


There are many aspects of SQL that are very different than in other programming languages. This course will help you adopt the right state of mind and get a true understanding of the language elements. You will learn how to think in terms of sets and follow good SQL programming practices.


The course is not version specific; it does, however, cover language elements that were introduced in recent versions of SQL Server. For recently added elements, the instructor will specify the version in which the element was introduced.


The course covers core SQL topics, which are delivered in lecture and discussion format. Those include: Background to T-SQL Querying and Programming; Single-Table Queries; Joins; Subqueries; Table Expressions; Set Operators; T-SQL for Data Analysis; Data Modification. The course also provides SQL-supporting topics as self-study units. Those include: Temporal Tables; Transactions and Concurrency; SQL Graph Overview; Overview of Programmable Objects. 


To complement the learning experience, students will be provided with exercises that will enable them to practice what they’ve learned.


  • Note: If you are an experienced T-SQL practitioner and are looking for an Advanced T-SQL course that dives deeply into the complexities of T-SQL Querying and Programming including coverage of performance and query tuning aspects, please see the course Advanced T-SQL Querying, Programming and Tuning for SQL Server by Itzik Ben-Gan. 

Course Materials

Each student will receive:   

  • A course Workbook
  • A digital copy of Itzik Ben-Gan’s best-selling book T-SQL Fundamentals (MSPress)
  • A copy of the course’s source code 

Author

This course was developed by Itzik Ben-Gan, author of award-winning books and numerous articles about T-SQL, a Microsoft Data Platform MVP since 1999, and a regular speaker in SQL Server related events. 

Target Audience

This course is intended for:   

  • T-SQL Programmers and DBAs, Data Scientists, Architects, Analysts and Power Users
  • Those who need to write or review code in SQL Server and/or Azure SQL

Prerequisites

It is recommended that students are familiar with basic relational database concepts and SQL. 

Course Objectives

Upon completion of this course, the student will:   

  • Understand the logic behind T-SQL and      thinking in terms of sets 
  • Be able to write T-SQL code to create      tables and define data integrity
  • Be able to write queries against single      and multiple tables
  • Be able to write T-SQL statements that      modify data
  • Get familiar with T-SQL programmable objects 

Course Outline


Core SQL topics, delivered in lecture and discussion format (Modules 1-8):

Module 01: Background to T-SQL Querying and Programming

  • Theoretical Background
  • SQL Server Architecture
  • SQL Server Management Studio
  • Creating Tables
  • Defining Data Integrity 

          Lab 01

Module 02: Single-Table Queries

  • Elements of the SELECT Statement
  • Predicates and Operators
  • CASE Expressions 
  • NULLs
  • The GREATEST and LEAST Functions
  • All-At-Once Operations
  • Working with Character Data
  • Working with Date and Time Data
  • Querying Metadata 

          Lab 02

Module 03 - Joins

  • Joins
  • Cross Joins
  • Inner Joins
  • More Join Examples
  • Outer Joins 

          LAB 03

Module 04: Subqueries

  • Self-Contained Subqueries
  • Scalar Subqueries
  • Multivalued Subqueries
  • Correlated Subqueries
  • The EXISTS Predicate
  • Returning Previous or Next Values
  • Using Running Aggregates
  • Dealing with Misbehaving Subqueries 

          LAB 04

Module 05: Table Expressions

  • Derived Tables
  • Common Table Expressions
  • Views
  • Inline Table-Valued Functions
  • The APPLY Operator 

          LAB 05

Module 06: Set Operators

  • Set Operators, Described
  • The UNION Operator
  • The INTERSECT Operator
  • The EXCEPT Operator
  • Precedence
  • Circumventing Unsupported Logical Phases  

          LAB 06

Module 07 - T-SQL for Data Analysis

  • Window Functions
  • Pivoting Data
  • Unpivoting Data
  • Grouping Sets
  • Time Series  

          LAB 07

Module 08: Data Modification

  • Inserting Data
  • Deleting Data
  • Updating Data
  • Merging Data
  • Modifying Data through Table      Expressions
  • Modifications with TOP / OFFSET-FETCH
  • The OUTPUT Clause  

          LAB 08


Supporting topics, provided as self-study units (Modules 9-12):

Module 09: Temporal Tables

  • Temporal Tables, Described
  • Creating Tables
  • Modifying Data
  • Querying Temporal Tables  

          LAB 09

Module 10: Transactions and Concurrency

  • Transactions
  • Locks and Blocking
  • Isolation Levels
  • Deadlocks   

          LAB 10

Module 11: SQL Graph Overview

  • Considerations for SQL Graph Modeling
  • Example of Traditional vs Graph      Modeling
  • Creating Tables
  • Querying Data  

          LAB 11

Module 12: Programmable Objects

  • Variables
  • Batches
  • Flow Elements
  • Cursors
  • Temporary Tables
  • Dynamic SQL
  • Routines
  • Error Handling 

Copyright © 2023 Itzik Ben-Gan - All Rights Reserved.

Powered by GoDaddy