Querying Data with Transact-SQL (20761) Cardiff Training Course

Module 1: Introduction to Microsoft SQL Server

• The Basic Architecture of SQL Server
• SQL Server Editions and Versions
• Getting Started with SQL Server Management Studio

Lab : Working with SQL Server Tools

• Working with SQL Server Management Studio
• Creating and Organizing T-SQL Scripts
• Using Books Online

Module 2: Introduction to T-SQL Querying

• Introducing T-SQL
• Understanding Sets
• Understanding Predicate Logic
• Understanding the Logical Order of Operations in SELECT statements

Lab : Introduction to T-SQL Querying

• Executing Basic SELECT Statements
• Executing Queries that Filter Data using Predicates
• Executing Queries That Sort Data Using ORDER BY

Module 3: Writing SELECT Queries

• Writing Simple SELECT Statements
• Eliminating Duplicates with DISTINCT
• Using Column and Table Aliases
• Writing Simple CASE Expressions

Lab : Writing Basic SELECT Statements

• Writing Simple SELECT Statements
• Eliminating Duplicates Using DISTINCT
• Using Column and Table Aliases
• Using a Simple CASE Expression

Module 4: Querying Multiple Tables

• Understanding Joins
• Querying with Inner Joins
• Querying with Outer Joins
• Querying with Cross Joins and Self Joins

Lab : Querying Multiple Tables

• Writing Queries that use Inner Joins
• Writing Queries that use Multiple-Table Inner Joins
• Writing Queries that use Self-Joins
• Writing Queries that use Outer Joins
• Writing Queries that use Cross Joins

Module 5: Sorting and Filtering Data

• Sorting Data
• Filtering Data with Predicates
• Filtering Data with TOP and OFFSET-FETCH
• Working with Unknown Values

Lab : Sorting and Filtering Data

• Writing Queries that Filter Data using a WHERE Clause
• Writing Queries that Sort Data Using an ORDER BY Clause
• Writing Queries that Filter Data Using the TOP Option
• Write Queries that filter data using the OFFSET-FETCH clause

.Module 6: Working with SQL Server Data Types

• Introducing SQL Server Data Types
• Working with Character Data
• Working with Date and Time Data

Lab : Working with SQL Server Data Types

• Writing Queries that Return Date and Time Data
• Writing Queries that use Date and Time Functions
• Writing Queries That Return Character Data
• Writing Queries That Return Character Functions

Module 7: Using DML to Modify Data

• Adding Data to Tables
• Modifying and Removing Data
• Generating automatic column values

Lab : Using DML to Modify Data

• Inserting Records with DML
• Updating and Deleting Records Using DML

Module 8: Using Built-In Functions

• Writing Queries with Built-In Functions
• Using Conversion Functions
• Using Logical Functions
• Using Functions to Work with NULL

Lab : Using Built-In Functions

• Writing Queries That Use Conversion Functions
• Writing Queries that use Logical Functions
• Writing Queries that Test for Nullability

Module 9: Grouping and Aggregating Data

• Using Aggregate Functions
• Using the GROUP BY Clause
• Filtering Groups with HAVING

Lab : Grouping and Aggregating Data

• Writing Queries That Use the GROUP BY Clause
• Writing Queries that Use Aggregate Functions
• Writing Queries that Use Distinct Aggregate Functions
• Writing Queries that Filter Groups with the HAVING Clause

Module 10: Using Subqueries

• Writing Self-Contained Subqueries
• Writing Correlated Subqueries
• Using the EXISTS Predicate with Subqueries

Lab : Using Subqueries

• Writing Queries That Use Self-Contained Subqueries
• Writing Queries That Use Scalar and Multi-Result Subqueries
• Writing Queries That Use Correlated Subqueries and an EXISTS Clause

Module 11: Using Table Expressions

• Using Views
• Using Inline Table-Valued Functions
• Using Derived Tables
• Using Common Table Expressions

Lab : Using Table Expressions

• Writing Queries That Use Views
• Writing Queries That Use Derived Tables
• Writing Queries That Use Common Table Expressions (CTEs)
• Writing Queries That Use Inline Table-Valued Expressions (TVFs)

Module 12: Using Set Operators

• Writing Queries with the UNION operator
• Using EXCEPT and INTERSECT
• Using APPLY

Lab : Using Set Operators

• Writing Queries That Use UNION Set Operators and UNION ALL
• Writing Queries That Use CROSS APPLY and OUTER APPLY Operators
• Writing Queries That Use the EXCEPT and INTERSECT Operators

Module 13: Using Windows Ranking, Offset, and Aggregate Functions

• Creating Windows with OVER
• Exploring Window Functions

Lab : Using Windows Ranking, Offset, and Aggregate Functions

• Writing Queries that use Ranking Functions
• Writing Queries that use Offset Functions
• Writing Queries that use Window Aggregate Functions

Module 14: Pivoting and Grouping Sets

• Writing Queries with PIVOT and UNPIVOT
• Working with Grouping Sets

Lab : Pivoting and Grouping Sets

• Writing Queries that use the PIVOT Operator
• Writing Queries that use the UNPIVOT Operator
• Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses

Module 15: Executing Stored Procedures

• Querying Data with Stored Procedures
• Passing Parameters to Stored procedures
• Creating Simple Stored Procedures
• Working with Dynamic SQL

Lab : Executing Stored Procedures

• Using the EXECUTE statement to Invoke Stored Procedures
• Passing Parameters to Stored procedures
• Executing System Stored Procedures

Module 16: Programming with T-SQL

• T-SQL Programming Elements
• Controlling Program Flow

Lab : Programming with T-SQL

• Declaring Variables and Delimiting Batches
• Using Control-Of-Flow Elements
• Using Variables in a Dynamic SQL Statement
• Using Synonyms

Module 17: Implementing Error Handling

• Implementing T-SQL error handling
• Implementing structured exception handling

Lab : Implementing Error Handling

• Redirecting errors with TRY/CATCH
• Using THROW to pass an error message back to a client

Module 18: Implementing Transactions

• Transactions and the database engines
• Controlling transactions

Lab : Implementing Transactions

• Controlling transactions with BEGIN, COMMIT, and ROLLBACK
• Adding error handling to a CATCH block

This course includes:

• Training from a Microsoft Certified Trainer (MCT)
• Computer / Laptop to use for the duration of the course
• Microsoft official courseware
• Online labs (if available)
• Refreshments