A Foundation in Transact SQL (T-SQL) (3 days)
TSQ-65: Fixed price Transact SQL training delivered on your site at any UK location.
COURSE OUTLINE |
pricing | more Transact SQL training |
This course is aimed at those working with Microsoft SQL Server who need to extract and insert data to an in-depth level and who also need to create and modify database objects. The course is highly practical in nature and the focus throughout is on coding T-SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are provided on a free USB pen drive to take away.
Suggested Prerequisites
No prior T-SQL training or relational database experience is assumed.
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
"A good learning experience."
Senior Divisional Analyst
The Royal Wolverhampton Hospitals
(Wolverhampton)