The Complete TSQL Programming Course
(DBTSQL, 4 days)


Description

This SETC certified course covers every aspect of the TSQL development language. Beginning with database basics like creating databases and tables, we proceed to a thorough investigation of the TSQL language. We discuss variable declaration, conditional and looping expressions and the use of native functions. With some basics under our belts, we are freed to look at more advanced topics like string and date time manipulation as well as the use of NULL values in tables and scripts. We also take the time to consider the use of stored procedures and BLOB data. It makes for a busy and rewarding week.

Dates and Pricing


Dec 18 to Dec 21, 2017$2,775/person Jan 16 to Jan 19, 2018$2,775/person Feb 13 to Feb 16, 2018$2,775/person Mar 13 to Mar 16, 2018$2,775/person

Outline

RDBMS Essentials
About the Relational Database Management System
SQL Server and the .NET Framework
SQL Server Terminology: Databases, Tables, Columns, Rows, Keys, Etc
Application Development and SQL Server
OLTP: The Transactional Server
OLAP: The Analysis Server
XML Integration
Core SQL Server Services
Other SQL Server Services
The Role of ADO .NET
Database Planning
Planning Overview
Normal Forms
The Analysis
The Design
ER Notation
Generalizations
Aggregations
Putting It Together
Creating the Database
Planning the Installation
SQL Server Installation
Data Storage Architecture
Creating Database Files
Using File Groups
Optimizing Data Storage
Creating Tables
Integrity Overview
Creating Data Types
Creating Rules and Defaults
Creating a Table
Primary and Foreign Keys
About Constraints: Default and Check
Creating Triggers
Structured Query Language Basics
SQL Overview
The Select Statement
Aggregate Values
The Insert Statement
The Update Statement
The Delete Statement
Using Views
TSQL Language Elements
SQL Data Types
Stored Procedures and Metadata Views
Using Variables
Type Conversion
Dealing with Null Values
Dealing with Dates
Conditional Constructs
Looping Constructs
Useful Functions
Error Handling
Advanced Structured Query Language
Join Strategies
Inner, Outer and Cross Joins
About Self-joins
Sub-queries
Using Temporary Objects
Stored Procedures
Architectural Overview
Benefits of Stored Procedures
Using Built-in Stored Procedures
Creating Stored Procedures
Using Input and Output Parameters
Stored Procedure Optimization
Indexing
Index Overview
Index Architecture
Clustered Indexes
Non-clustered Indexes
Unique Indexes
Indexes and Keys
Indexed Views
Optimizing Performance
Optimization Overview
Using Query Analyzer
Gathering Performance Data
Optimizing for Select
Optimizing for Insert, Update and Delete
Query Hints
Join Strategies
SQL Server Transactions and Locks
Deadlocks and Livelocks
Practical SQL
Planning the Work
Process Oriented Tasks
Analysis Related Tasks
Statistical Tasks
Data Integrity Checks
Using SSIS