Lucient | Course

Do you want to write accurate, effective, and robust T-SQL code? If you want to learn T-SQL the right way, understanding the logic behind the language by learning not only the how but also the why, do not miss these courses!

This is your chance to learn T-SQL using practical cases with our mentors!

A short version of this course is also available ON DEMAND. Start learning T-SQL right away!

Course Benefits

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

Prerequisites

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.

What Will I Learn?

This on-demand course provides you with strong foundations, teaching you both about the many T-SQL elements that you need to use when writing queries, and also how to think in SQL, set-based terms. It helps you write accurate, effective, and robust code.

Many SQL intro/fundamentals classes only teach you about the language elements and how to use them, but not about their mathematical roots or why the language elements were designed the way they were designed. This course teaches you both. It emphasizes the unique elements of SQL and the mindset you need to adopt, like thinking in relational terms; thinking of a set as a whole, with no ordering; what the language guarantees and what it doesn’t; common pitfalls and bugs, and best practices to avoid them.

Following this T-SQL Fundamentals course, you will be ready to take our Advanced T-SQL course, if you want to go deeper in this path.

Course Outline

Module 1. Background to T-SQL Querying and Programming.

Theoretical Background
SQL Server’s Architecture
SQL Server Management Studio
Creating Tables
Defining Data Integrity
Sample Database

Module 2. 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

Module 3. Joins.

Joins
Cross Joins
Inner Joins
Further Join Examples
Outer Joins

Module 4. Subqueries.

Self-Contained Subqueries
Correlated Subqueries
Scalar Subqueries
Multi-Valued Subqueries
EXISTS
Beyond the Fundamentals of Subqueries

Module 5. Table Expressions.

Derived Tables
Common Table Expressions
Views
Inline Table-Valued Functions
APPLY

Module 6. Set Operators.

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

Module 7. Beyond the Fundamentals of Querying.

Window Functions
Pivoting Data
Unpivoting Data
Grouping Sets

Module 8. Data Modification.

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

Module 9. Temporal Tables.

Temporal Tables, Described
Creating Tables
Modifying Tables
Querying Temporal Tables

Module 10. Transactions and Concurrency.

Transactions
Locks and Blocking
Isolation Levels
Deadlocks

Module 11. Programmable Objects.

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

T-SQL Fundamentals