COURSE: T-SQL Fundamentals 2019
UPCOMING DATES
There are no scheduled classes for this course at the moment. Please, send us an information request to find out more.
Learn T-SQL 2019 the right way. Understand the logic behind the language and know not only the how but also the why. This course is an updated version of the TSQL Fundamentals 2016 course.
During the course you will learn: to understand the logic behind T-SQL and thinking in terms of sets, write T-SQL code to create tables and define data integrity, write queries against single and multiple tables, write T-SQL statements that modify data and get familiar with T-SQL programmable objects
Do you want more info about this course?
COURSE DELIVERY OPTIONS
5-DAYS CLASSROOM
will take place in a classroom with no more than 20 students in order to maintain a good level of interactivity.
PRIVATE ONSITE
will take place in your company’s facilities. We limit attendance to no more than 20 students in order to maintain a good level of interactivity. Request quote here.
Course Benefits
Did you know that one of our foundors wrote this course and is known worldwide?
Yes. This course was developed by Itzik Ben-Gan, a mentor and one of the founders of SolidQ, author of award winning books about T-SQL, a Microsoft Data Platform MVP since 1999, and a regular speaker in SQL Server related events.
Is the level appropriate for you?
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 doesn’t get into performance discussions and advanced problems* rather focuses on the logical aspects of T-SQL. Note though that it is not merely a step-by-step course. It doesn’t just focus on syntactical elements of T-SQL, rather explains the logic behind the language and its elements. It’s recommended to be familiar with basic relational database concepts and SQL
Expert Mentors
Our instructors have faced in previous real case projects, the same problems you are facing now. Learn from experience professionals.
Interaction and Q&A
In all of our trainings, you will have the chance to ask individual questions and be capable of solving certain issues.
Course Coverage
Developed in-house by SolidQ, this T-SQL Fundamentals course is intended for T-SQL developers, DBAs, data scientists and power users who work with SQL Server or Azure SQL Database and need to write queries and develop code using Transact-SQL—SQL Server’s dialect of the standard ANSI-SQL language. 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 topics covered in the course include: Background to T-SQL Querying and Programming; Single-Table Queries; Joins; Subqueries; Table Expressions; Set Operators; Window Functions, Pivot, Unpivot and Grouping Sets; Data Modification; Temporal Tables; Transactions; 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.
Understand the logic behind T-SQL and thinking in terms of sets
Write T-SQL statements that modify data
Write T-SQL code to create tables and define data integrity
Get familiar with T-SQL programmable objects
Write queries against single and multiple tables
”Excellent. I never thought what else I need to learn more in T-SQL.I never realized there is such a deeper world of SQL.”
COURSE OUTLINE
Module 01: Background to T-SQL Querying and Programming
- Theoretical Background
- SQL Server’s Architecture
- SQL Server Management Studio
- Creating Tables
- Defining Data Integrity
- Sample Database
Lab 01
Module 02: Single-Table Queries
- Elements of SELECT Statement
- Predicates and Operators
- CASE Expressions
- NULLs
- 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
- Further Join Examples
- Outer Joins
LAB 03
Module 04: Subqueries
- Self-Contained Subqueries
- Correlated Subqueries
- Scalar Subqueries
- Multi-Valued Subqueries
- EXISTS
- Beyond the Fundamentals of Subqueries
LAB 04
Module 05: Table Expressions
- Derived Tables
- Common Table Expressions
- Views
- Inline Table-Valued Functions
- APPLY
LAB 05
Module 06: Set operators
- Set Operators, Described
- UNION
- INTERSECT
- EXCEPT
- Precedence
- Circumventing Unsupported Logical Phases
LAB 06
Module 07: Beyond the Fundamentals of Querying
- Window Functions
- Pivoting Data
- Unpivoting Data
- Grouping Sets
LAB 07
Module 08: Data Modification
- Inserting Data
- Deleting Data
- Updating Data
- Merging Data
- Modifying Data through Table Expressions
- Modifications with TOP / OFFSET-FETCH
- OUTPUT
LAB 08
Module 09: Temporal Tables
- Temporal Tables, Described
- Creating Tables
- Modifying Tables
- Querying Temporal Tables
LAB 09
Module 10: Transactions and Concurrency
- Transactions
- Locks and Blocking
- Isolation Levels
- Deadlocks
LAB 10
Module 11: Programmable Objects
- Variables
- Batches
- Flow Elements
- Cursors
- Temporary Tables
- Dynamic SQL
- Routines
- Error Handling
Meet our Instructors
Itzik Ben – Gan
Itzik Ben – GanItzik Ben-Gan is a Mentor and Co-Founder of SolidQ. A Data Platform Microsoft MVP (Most Valuable Professional) since 1999, Itzik has delivered numerous training events around the world focused on T-SQL Querying, Query Tuning and Programming. Itzik is the author of several books including T-SQL Fundamentals Third Edition and T-SQL Querying. He has written articles for SQL Server Pro, SolidQ Journal and MSDN. Itzik’s speaking activities include SQLPASS, SQLBits, SQL Nexus, SQLU, SQLTeach and various user groups around the world. Itzik is the author of SolidQ’s Advanced T-SQL Querying, Programming and Tuning and T-SQL Fundamentals courses along with being a primary resource within the company for their T-SQL related activities.
Herbert Albert
Herbert AlbertHerbert Albert is a Mentor and Managing Director of SolidQ’s Central and Eastern Europe subsidiary. Since SQL Server 6.0 Albert worked as a consultant, a database developer and trainer and holds several Microsoft certifications including MCT. He was involved in the development of several Microsoft Official Curriculum courses. Herbert co-authored “SQL Server Upgrade Technical Reference Guide” for SQL Server 2008 and 2012 and the Microsoft Press book “SQL Server 2005 Step-by-Step Applied Techniques”. He is a regular speaker at international conferences and events. As a trainer Herbert is focused on courses for T-SQL programming and performance tuning, which he delivers successfully all over Europe.