T-SQL Fundamentals - Course

T-SQL Fundamentals

Choose your own date

INFORMATION COURSE OUTLINE

4-Day (SQ-FNDTSQL-201-EN)

Description
Audience
Prerequisites
Course Objectives
Course Outline

Download a PDF of this Outline

Description

This course is intended for T-SQL developers, DBAs and SQL Server power users who work 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 covers querying, 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 for people with some experience (for example, developers who are not SQL-focused). Even if you are a more experienced SQL Server practitioner, if you are self-taught and gained your knowledge “by the seat of your pants” and are looking for more organized training to cover gaps and holes in your knowledge, this course is for you. 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.
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: If you are a very 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 deep coverage of query and index tuning, please see the course Advanced T-SQL Querying Programming ans Tuning for SQL Server.

Audience

This course is intended for:

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

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

Module 01: Background to T-SQL Querying and Programming Theoretical Background SQL

Set TheoryPredicate LogicRelational ModelData Life Cycle

SQL Server Architecture

SQL Server InstancesDatabasesSchemas and Objects

Creating Tables and Defining Data Integrity

Creating TablesDefining Data Integrity

Module 02: Single-Table Queries Elements of SELECT Statement

The FROM ClauseThe WHERE ClauseThe GROUP BY ClauseThe HAVING ClauseThe SELECT ClauseThe ORDER BY ClauseThe TOP OptionThe OVER Clause

Predicates and OperatorsCASE Expressions NULLsAll-At-Once OperationsWorking with Character Data

Data TypesCollationOperators and FunctionsLIKE Predicate

Working with Date and Time Data

Date and Time Data TypesLiteralsWorking with Date and Time SeparatelyFiltering Date RangesDate and Time Functions

Querying Metadata

Catalog ViewsInformation Schema ViewsSystem Stored Procedures and Functions

LAB 02A

Module 03: Joins Cross Joins

ANSI SQL-92 SyntaxANSI SQL-89 SyntaxSelf Cross JoinProducing Tables of Numbers

Inner Joins

ANSI SQL-92 SyntaxANSI SQL-89 SyntaxInner Join Safety

Further Join Examples

Composite JoinNon-Equi JoinMulti-Table Joins

Outer Joins

Fundamentals of Outer JoinsBeyond the Fundamentals of Outer Joins

LAB 03A

Module 04: Subqueries Self-Contained Subqueries

Self-Contained Scalar Subquery ExamplesSelf-Contained Multi-Valued Subquery Examples

Correlated Subqueries

EXIST

Beyond the Fundamentals of Subqueries

Returning Previous or Next ValuesRunning AggregatesMisbehaving Subqueries

LAB 04A

Module 05: Table Expressions Derived Tables

Assigning Column AliasesUsing ArgumentsNestingMultiple References

Common Table Expressions

Assigning Column AliasesUsing ArgumentsDefining Multiple CTEsMultiple ReferencesRecursive CTEs

Views

Views and ORDER BYView OptionsENCRYPTIONSCHEMABINDINGCHECK OPTION

Inline User Defined FunctionsAPPLYLAB 05A

Module 06: Set Operations The UNION Set Operation

The UNION ALL Set OperationThe UNION DISTINCT Set Operation

The INTERSECT Set Operation

The INTERSECT ALL Set OperationThe INTERSECT DISTINCT Set Operation

The EXCEPT Set Operation

The EXCEPT ALL Set OperationThe EXCEPT DISTINCT Set Operation

PrecedenceCircumventing Unsupported Logical PhasesLAB 06A

Module 07: Pivot, Unpivot and Grouping Sets Pivoting Data

Standard Solution to PivotingPivoting with the T-SQL PIVOT Operator

Unpivoting Data

Standard Solution to UnpivotingUnpivoting with the T-SQL UNPIVOT Operator

Grouping Sets

GROUPING SETS SubclauseCUBE SubclauseROLLUP SubclauseGROUPING and GROUPING_ID Functions

LAB 07A

Module 08: Data Modification Inserting Data

The INSERT VALUES StatementThe INSERT SELECT StatementThe INSERT EXEC StatementThe SELECT INTO StatementThe BULK INSERT StatementThe IDENTITY Property

Deleting Data

The DELETE StatementThe TRUNCATE StatementDELETE Based on Join

Updating Data

The UPDATE StatementUPDATE Based on JoinAssignment UPDATE

Merging DataModifying Data through Table ExpressionsModifications with TOPOUTPUT

INSERT with OUTPUTDELETE with OUTPUTUPDATE with OUTPUTMERGE with OUTPUTComposable DML

LAB 08A

Module 09: Transactions and ConcurrencyTransactionsLocks and Blocking

LocksTroubleshooting Blocking

Isolation Levels

Read UncommittedRead CommittedRepeatable ReadSerializableSnapshot Isolation LevelsSummary of Isolation Levels

DeadlocksLAB 09A

Module 10: Programmable ObjectsVariablesBatches

A Batch as a Unit of ParsingBatches and VariablesStatements that Cannot be Combined in the same BatchA Batch as a Unit of ResolutionThe GO n Option

Flow Elements

The IF ... ELSE Flow ElementThe WHILE Flow ElementAn Example of Using IF and WHILE

CursorsTemporary Tables

Local Temporary TablesGlobal Temporary TablesTable VariablesTable Types

Dynamic SQL

The EXEC CommandThe sp_executesql Stored ProcedureUsing PIVOT with Dynamic SQL

Routines

User Defined FunctionsStored ProceduresTriggers

Error Handling