Home

Training

Mentoring

Consulting

Events

Resources

About Us

Contact Us

Our vision is simple: We enhance Microsoft SQL Server and Microsoft Business Intelligence skills in organisations by providing expert consulting, mentoring and training services.

Description Minimize
T-SQL Fundamentals 
4-Day (COURSE-DB-TSQLFUNDAMENTALS)

Description
This course is intended for T-SQL programmers, DBAs and SQL Server power users who just started working with SQL Server and need to write queries and develop code using Transact-SQL—SQL Server’s dialect of the standard ANSI-SQL language. The course will walk students through their first steps in querying, including retrieving and modifying data, and will also provide an overview of programmable objects supported by SQL Server.

While this course is intended for beginners, 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.
There are many aspects of SQL that are unique to this language, and are very different than other programming languages. This course will help students adopt the right state of mind and get a true understanding of the language elements. Students 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. Throughout the course the instructor will specify the version in which the elements were introduced.
The topics covered in the course include: Background to T-SQL Querying and Programming; Single-Table Queries; Joins; Subqueries; Table Expressions; Set Operations; Pivot, Unpivot and Grouping Sets; Data Modification; 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: For those needing an even shorter introduction to T-SQL, consider our two day private T-SQL offering (click here)!

For details on booking courses or seminars, please visit our booking page. 

 
 
Author

This course was developed by Itzik Ben-Gan, a mentor and one of the founders of Solid Quality Mentors, author of several books about T-SQL, a columnist in SQL Server Magazine, and a regular speaker in SQL Server related events.

Audience

This course is intended for:

  • Novice T-SQL Programmers and DBAs, Architects, Analysts and Power Users
  • Those that need to write or review code in SQL Server 2005 and 2008 

 Prerequisites

Before attending this course, it is recommended that students have the following skills:

  • Familiarity with basic relational database concepts and SQL 

 Course Objectives

Through interactive presentations and concrete hands-on labs, attendees will learn:
  • 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
  • Get familiar with T-SQL programmable objects 
 
Technologies
 
The workshop will empower developers to exploit the functionality in the following technologies:
  • SQL Server 2008 T-SQL language 
While based on SQL Server 2008 versions of SQL Server, this course is highly relevant for those working on SQL Server 2005 versions as well.

Course Outline

Day 1

Course Introduction

Module 01: Background to T-SQL Querying and Programming

    • Theoretical Background
      • SQL
      • Set Theory
      • Predicate Logic
      • Relational Model
      • Data Life Cycle
    • SQL Server Architecture
      • SQL Server Instances
      • Databases
      • Schemas and Objects
    • Creating Tables and Defining Data Integrity
      • Creating Tables
      • Defining Data Integrity

Module 02: Single-Table Queries

    • Elements of SELECT Statement
      • The FROM Clause
      • The WHERE Clause
      • The GROUP BY Clause
      • The HAVING Clause
      • The SELECT Clause
      • The ORDER BY Clause
      • The TOP Option
      • The OVER Clause
    • Predicates and Operators
    • CASE Expressions
    • NULLs
    • All-At-Once Operations
    • Working with Character Data
      • Data Types
      • Collation
      • Operators and Functions
      • LIKE Predicate
    • Working with Date and Time Data
      • Date and Time Data Types
      • Literals
      • Working with Date and Time Separately
      • Filtering Date Ranges
      • Date and Time Functions
    • Querying Metadata
      • Catalog Views
      • Information Schema Views
      • System Stored Procedures and Functions
    • LAB 02A

Module 03: Joins

    • Cross Joins
      • ANSI SQL-92 Syntax
      • ANSI SQL-89 Syntax
      • Self Cross Join
      • Producing Tables of Numbers
    • Inner Joins
      • ANSI SQL-92 Syntax
      • ANSI SQL-89 Syntax
      • Inner Join Safety
    • Further Join Examples
      • Composite Join
      • Non-Equi Join
      • Multi-Table Joins
    • Outer Joins
      • Fundamentals of Outer Joins
      • Beyond the Fundamentals of Outer Joins
    • LAB 03A

Module 04: Subqueries

    • Self-Contained Subqueries
      • Self-Contained Scalar Subquery Examples
      • Self-Contained Multi-Valued Subquery Examples
    • Correlated Subqueries
      • EXISTS
    • Beyond the Fundamentals of Subqueries
      • Returning Previous or Next Values
      • Running Aggregates
      • Misbehaving Subqueries
    • LAB 04A 

Module 05: Table Expressions

    • Derived Tables
      • Assigning Column Aliases
      • Using Arguments
      • Nesting
      • Multiple References
    • Common Table Expressions
      • Assigning Column Aliases
      • Using Arguments
      • Defining Multiple CTEs
      • Multiple References
      • Recursive CTEs
    • Views
      • Views and ORDER BY
      • View Options
      • ENCRYPTION
      • SCHEMABINDING
      • CHECK OPTION
    • Inline User Defined Functions
    • APPLY
    • LAB 05A

Module 06: Set Operations

    • The UNION Set Operation
      • The UNION ALL Set Operation
      • The UNION DISTINCT Set Operation
    • The INTERSECT Set Operation
      • The INTERSECT ALL Set Operation
      • The INTERSECT DISTINCT Set Operation
    • The EXCEPT Set Operation
      • The EXCEPT ALL Set Operation
      • The EXCEPT DISTINCT Set Operation
    • Precedence
    • Circumventing Unsupported Logical Phases
    • LAB 06A

Module 07: Pivot, Unpivot and Grouping Sets

    • Pivoting Data
      • Standard Solution to Pivoting
      • Pivoting with the T-SQL PIVOT Operator
    • Unpivoting Data
      • Standard Solution to Unpivoting
      • Unpivoting with the T-SQL UNPIVOT Operator
    • Grouping Sets
      • GROUPING SETS Subclause
      • CUBE Subclause
      • ROLLUP Subclause
      • GROUPING and GROUPING_ID Functions
    • LAB 07A

Module 08: Data Modification

    • Inserting Data
      • The INSERT VALUES Statement
      • The INSERT SELECT Statement
      • The INSERT EXEC Statement
      • The SELECT INTO Statement
      • The BULK INSERT Statement
      • The IDENTITY Property
    • Deleting Data
      • The DELETE Statement
      • The TRUNCATE Statement
      • DELETE Based on Join
    • Updating Data
      • The UPDATE Statement
      • UPDATE Based on Join
      • Assignment UPDATE
    • Merging Data
    • Modifying Data through Table Expressions
    • Modifications with TOP
    • OUTPUT
      • INSERT with OUTPUT
      • DELETE with OUTPUT
      • UPDATE with OUTPUT
      • MERGE with OUTPUT
      • Composable DML
    • LAB 08A

Module 09: Transactions and Concurrency

    • Transactions
    • Locks and Blocking
      • Locks
      • Troubleshooting Blocking
    • Isolation Levels
      • Read Uncommitted
      • Read Committed
      • Repeatable Read
      • Serializable
      • Snapshot Isolation Levels
      • Summary of Isolation Levels
    • Deadlocks
    • LAB 09A

Module 10: Programmable Objects

    • Variables
    • Batches
      • A Batch as a Unit of Parsing
      • Batches and Variables
      • Statements that Cannot be Combined in the same Batch
      • A Batch as a Unit of Resolution
      • The GO n Option
    • Flow Elements
      • The IF ... ELSE Flow Element
      • The WHILE Flow Element
      • An Example of Using IF and WHILE
    • Cursors
    • Temporary Tables
      • Local Temporary Tables
      • Global Temporary Tables
      • Table Variables
      • Table Types
    • Dynamic SQL
      • The EXEC Command
      • The sp_executesql Stored Procedure
      • Using PIVOT with Dynamic SQL
    • Routines
      • User Defined Functions
      • Stored Procedures
      • Triggers
    • Error Handling
 

 

Copyright 2009 by SolidQ Australia Pty Ltd Login