A Foundation in Transact SQL (T-SQL)

Relational Databases and SQL: An Overview

• Database Server, Databases, Tables and Schemas
• Primary Keys and Foreign Keys Explained
• Some Common Character, Numeric and Date Data Types
• Client/Server Interaction
• SQL: Its Role and Purpose
• SQL and Transact SQL (T-SQL) in Context

T-SQL Statements and Queries: Their Structure

• Keywords, Identifiers and Operators
• Whitespace and Case: The Rules
• Expressions and Conditions Explained
• Conventions and Good Practice
• Working with Clauses
• A Note on Semi-Colons and GO

Retrieving Data with T-SQL: First Steps

• How to Create, Edit and Execute T-SQL Scripts
• Ways of Specifying the Database, Schema and Table
• Working with the Default Schema
• Outputting Text with PRINT
• Using SELECT to Extract Data
• Retrieving Entire Tables
• Retrieving Specific Columns
• Working with the Result Table
• Outputting Results to CSV
• Using TOP to Limiting the Number of Rows
• Using ORDER BY to Sort the Output
• Working with Calculations
• Using Column Aliases to Rename Columns
• Using Numeric and String Operators to Create Derived Output
• Ways of Working: Some Tips

Using WHERE to Filter Results

• Working with Comparison Operators (=, >= etc)
• Numeric and String Based Filtering
• Filtering Based on Calculations
• Eliminating Duplicate Results with DISTINCT
• Working with Execution Order
• Column Aliases: Where You Can and Cannot Use Them
• Extending Filters with AND and OR
• Solving AND/OR Difficulties with Brackets
• Excluding Results with NOT: Some Tips
• Range Filtering using BETWEEN and IN
• NULL and its Implications Explained
• Catering for NULL
• Matching Patterns with LIKE

Getting Results From Multiple Tables

• Qualifying Column Names
• Joins Explained
• The Different Types of Joins
• Creating an Inner Join: WHERE Syntax
• Creating an Inner Join: INNER JOIN Syntax
• Table Aliases: The Need
• Working with Self Joins
• Outer Joins: An Example
• How to Simplify Joins: An Approach

Using Standard T-SQL Functions

• How to Use Standard T-SQL Functions to Modify Results
• How to Find the Right Function
• Mathematical, String and Conversion Functions
• Functions for Modifying and Calculating Dates
• Formatting Numbers to Two Decimal Places
• Replacing NULL with a Specific Value
• Using Standard Functions in WHERE
• Using CASE to Specify Output Conditions
• Using Date functions

Grouping, Summarizing and Ranking Results

• The difference Between Tabular and Scalar Results
• Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
• The Way Aggregate Functions Work
• Where to Use and Where Not to Use Aggregate Functions
• Using GROUP BY to Group Results
• The Need for HAVING: Filtering the Result Table
• Using COMPUTE to Summarize Data
• Seeing both Detail and Summary Data with ROLLUP
• Ways of Ranking Results

Working with Subqueries

• Subqueries Explained
• Where you can Use Subqueries
• How to Successfully Construct Subqueries
• Subqueries for Filtering
• Subqueries to Create Derived Columns

Working with Views

• Views Explained
• Advantages of Views
• How to use Views to Simplify your Work
• Creating Views
• Dropping Views

Inserting, Updating and Deleting Data

• Inserting Single Rows
• Inserting Multiple Rows
• Inserting Rows by Column Position
• Inserting Rows by Column Name
• Dealing with Auto-Incrementing Values
• Dealing with Nulls when Inserting
• Inserting Data from one Table into Another
• Updating Data
• Deleting Data
• Importing Data from CSV Files
• Truncating Tables
• Modifying Data through a View

Inserting, Updating and Deleting in a Transaction Environment

• Transactions Explained
• Why Use Transactions?
• Protecting Yourself with Transactions
• How to Setup a Transaction Environment
• Checking Your Work
• Undoing your Changes with ROLLBACK
• Committing the Transaction

Creating and Modifying Tables

• Using CREATE TABLE
• Specifying Primary and Foreign Keys
• Using DEFAULT values
• Constraining Input
• Using Temporary Tables
• Creating a New Table From an Existing Table
• Altering and Dropping Tables

Working with XML

• SQL Server and XML
• Retrieving Data as XML
• Storing XML Data
• Searching for XML Data

On Site Requirements

Remember, we provide all equipment and software required to deliver a course at your premises. Aside from this, we need a suitably quiet and equipped room with enough work space for each attendee and a whiteboard or flipchart. Most courses involve the use of a PC projector and we bring our own. But either a projector screen, or usually just a clear wall, would be very helpful.

Other Courses to Consider

• TSQ-63: Querying SQL Server with Transact SQL (T-SQL)
  For those needing to use T-SQL to extract data from SQL Server (Action queries which modify data or objects not included).
• TSQ-64: Introduction to Transact SQL (T-SQL)
  Using T-SQL to extract data from, and update data in, SQL Server (for developers and end users).
• TSQ-66: T-SQL Programming with SQL Server
  Developing solutions with SQL Server's programming objects and T-SQL's procedural extensions.

on site training courses available in:  

• London
• , Birmingham
• , Edinburgh
• , Manchester
• , Scotland
• , Glasgow
• , Nottingham
• , Midlands
• , Bristol
• , Wales
• , Cardiff
• , Dublin
• , Belfast
• , Leeds
• , Liverpool
• , Sheffield
• , Reading
• , Oxford
• , Cambridge
• , Southampton
• , Newcastle
• , Durham
• , Warrington.

and across the UK and Ireland