T-SQL Fundamentals

40 hours

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

Upcoming classes