Search Training
X

20761 Querying Data with Transact SQL

Course Overview

The main purpose of this course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.

Who Should Attend

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused; namely, report writers, business analysts and client application developers.

Course Objectives

After completing this course, students will be able to:

  • Describe the basic architecture and concepts of Microsoft SQL Server 2016.
  • Understand the similarities and differences between Transact-SQL and other computer languages.
  • Write SELECT queries
  • Query multiple tables
  • Sort and filter data
  • Describe the use of data types in SQL Server
  • Modify data using Transact-SQL
  • Use built-in functions
  • Group and aggregate data
  • Use subqueries
  • Use table expressions
  • Use set operators
  • Use window ranking, offset and aggregate functions
  • Implement pivoting and grouping sets
  • Execute stored procedures
  • Program with T-SQL
  • Implement error handling Implement transactions

    Course Outline

    1 - Introduction to Microsoft SQL Server 2016

    • Lesson 1: The Basic Architecture of SQL Server
    • Lesson Objectives
    • Relational Databases
    • About the Course Sample Database
    • Client Server Databases
    • Queries
    • Lesson 2: SQL Server Editions and Versions
    • Lesson Objectives
    • SQL Server Versions
    • SQL Server Editions
    • Lesson 3: Getting Started with SQL Server Management Studio
    • Lesson Objectives
    • Starting SSMS
    • Connecting to SQL Server
    • Working with Object Explorer
    • Script Files and Projects
    • Executing Queries
    • Using SQL Server Technical Documentation
    • Demonstration: Introducing Microsoft SQL Server

    2 - Introduction to T-SQL Querying

    • Lesson 1: Introducing T-SQL
    • Lesson Objectives
    • About T-SQL
    • Categories of T-SQL Statements
    • T-SQL Language Elements
    • T-SQL Language Elements: Predicates and Operators
    • T-SQL Language Elements: Functions
    • T-SQL Language Elements: Variables
    • T-SQL Language Elements: Expressions
    • T-SQL Language Elements: Control of Flow, Errors, and Transactions
    • T-SQL Language Elements: Comments
    • T-SQL Language Elements: Batch Separators
    • Demonstration: T-SQL Language Elements
    • Lesson 2: Understanding Sets
    • Lesson Objectives
    • Set Theory and SQL Server
    • Set Theory Applied to SQL Server Queries
    • Lesson 3: Understanding Predicate Logic
    • Lesson Objectives
    • Predicate Logic and SQL Server
    • Predicate Logic Applied to SQL Server Queries
    • Lesson 4: Understanding the Logical Order of Operations in SELECT Statements
    • Lesson Objectives
    • Elements of a SELECT Statement
    • Logical Query Processing
    • Applying the Logical Order of Operations to Writing SELECT Statements
    • Demonstration: Logical Query Processing

    3 - Writing SELECT Queries

    • Lesson 1: Writing Simple SELECT Statements
    • Lesson Objectives
    • Elements of the SELECT Statement
    • Retrieving Columns from a Table or View
    • Displaying Columns
    • Using Calculations in the SELECT Clause
    • Demonstration: Writing Simple SELECT Statements
    • Lesson 2: Eliminating Duplicates with DISTINCT
    • Lesson Objectives
    • SQL Sets and Duplicate Rows
    • Understanding DISTINCT
    • SELECT DISTINCT Syntax
    • Demonstration: Eliminating Duplicates with DISTINCT
    • Lesson 3: Using Column and Table Aliases
    • Lesson Objectives
    • Use Aliases to Refer to Columns
    • Use Aliases to Refer to Tables
    • The Impact of Logical Processing Order on Aliases
    • Demonstration: Using Column and Table Aliases
    • Lesson 4: Writing Simple CASE Expressions
    • Lesson Objectives
    • Using CASE Expressions in SELECT Clauses
    • Forms of CASE Expressions
    • Demonstration: Simple CASE Expressions

    4 - Querying Multiple Tables

    • Lesson 1: Understanding Joins
    • The FROM Clause and Virtual Tables
    • Join Terminology: Cartesian Product
    • Overview of Join Types
    • T-SQL Syntax Choices
    • Demonstration: Understanding Joins
    • Lesson 2: Querying with Inner Joins
    • Lesson Objectives
    • Understanding Inner Joins
    • Inner Join Syntax
    • Inner Join Examples
    • Demonstration: Querying with Inner Joins
    • Lesson 3: Querying with Outer Joins
    • Lesson Objectives
    • Understanding Outer Joins
    • Outer Join Syntax
    • Outer Join Examples
    • Demonstration: Querying with Outer Joins
    • Lesson 4: Querying with Cross Joins and Self Joins
    • Lesson Objectives
    • Understanding Cross Joins
    • Cross Join Syntax
    • Cross Join Examples
    • Understanding Self Joins
    • Self Join Examples
    • Demonstration: Querying with Cross Joins and Self Joins

    5 - Sorting and Filtering Data

    • Lesson 1: Sorting Data
    • Lesson Objectives
    • Using the ORDER BY Clause
    • ORDER BY Clause Syntax
    • ORDER BY Clause Examples
    • Demonstration: Sorting Data
    • Lesson 2: Filtering Data with Predicates
    • Lesson Objectives
    • Filtering Data in the WHERE Clause with Predicates
    • WHERE Clause Syntax
    • Demonstration: Filtering Data with Predicates
    • Lesson 3: Filtering Data with TOP and OFFSET-FETCH
    • Lesson Objectives
    • Filtering in the SELECT Clause Using the TOP Option
    • Filtering in the ORDER BY Clause Using OFFSET-FETCH
    • OFFSET-FETCH Syntax
    • Demonstration: Filtering Data with TOP and OFFSET-FETCH
    • Lesson 4: Working with Unknown Values
    • Lesson Objectives
    • Three-Valued Logic
    • Handling NULL in Queries
    • Demonstration: Working with NULL

    6 - Working with SQL Server 2016 Data Types

    • Lesson 1: Introducing SQL Server Data Types
    • Lesson Objectives
    • SQL Server Data Types
    • Numeric Data Types
    • Binary String Data Types
    • Other Data Types
    • Data Type Precedence
    • When are Data Types Converted?
    • Demonstration: SQL Server Data Types
    • Lesson 2: Working with Character Data
    • Lesson Objectives
    • Character Data Types
    • Collation
    • String Concatenation
    • Character String Functions
    • The LIKE Predicate
    • Demonstration: Working with Character Data
    • Lesson 3: Working with Date and Time Data
    • Lesson Objectives
    • Date and Time Data Types
    • Entering Date and Time Data Types Using Strings
    • Working Separately with Date and Time
    • Querying Date and Time Values
    • Date and Time Functions
    • Demonstration: Working with Date and Time Data

    7 - Using DML to Modify Data

    • Lesson 1: Adding Data to Tables
    • Lesson Objectives
    • Using INSERT to Add Data
    • Using INSERT with Data Providers
    • Using SELECT INTO
    • Demonstration: Adding Data to Tables
    • Lesson 2: Modifying and Removing Data
    • Lesson Objectives
    • Using UPDATE to Modify Data
    • Using MERGE to Modify Data
    • Demonstration: Manipulating Data Using the UPDATE and DELETE Statements and MERGING Data Using Conditional DML
    • Lesson 3: Generating Automatic Column Values
    • Lesson Objectives
    • Using IDENTITY
    • Using Sequences

    8 - Using Built-In Functions

    • Lesson 1: Writing Queries with Built-In Functions
    • Lesson Objectives
    • SQL Server Built-in Function Types
    • Scalar Functions
    • Aggregate Functions
    • Window Functions
    • Rowset Functions
    • Demonstration: Writing Queries Using Built-in Functions
    • Lesson 2: Using Conversion Functions
    • Lesson Objectives
    • Implicit and Explicit Data Type Conversions
    • Converting with CAST
    • Converting with CONVERT
    • Converting Strings with PARSE
    • Converting with TRY_PARSE and TRY_CONVERT
    • Demonstration: Using Conversion Functions
    • Lesson 3: Using Logical Functions
    • Lesson Objectives
    • Writing Logical Test with Functions
    • Performing Conditional Tests with IIF
    • Selecting Items from a List with CHOOSE
    • Demonstration: Using Logical Functions
    • Lesson 4: Using Functions to Work with NULL
    • Lesson Objectives
    • Converting NULL with ISNULL
    • Using COALESCE to Return Non-NULL Values
    • Using NULLIF to Return NULL If Values Match
    • Demonstration: Using Functions to Work with NULL

    9 - Grouping and Aggregating Data

    • Lesson 1: Using Aggregate Functions
    • Lesson Objectives
    • Working with Aggregate Functions
    • Built-in Aggregate Functions
    • Using DISTINCT with Aggregate Functions
    • Using Aggregate Functions with NULL
    • Demonstration: Using Aggregate Functions
    • Lesson 2: Using the GROUP BY Clause
    • Lesson Objectives
    • Using the GROUP BY Clause
    • GROUP BY and the Logical Order of Operations
    • GROUP BY Workflow
    • Using GROUP BY with Aggregate Functions
    • Demonstration: Using GROUP BY
    • Lesson 3: Filtering Groups with HAVING
    • Lesson Objectives
    • Filtering Grouped Data Using the HAVING Clause
    • Compare HAVING to WHERE
    • Demonstration: Filtering Groups with HAVING

    10 - Using Subqueries

    • Lesson 1: Writing Self-Contained Subqueries
    • Lesson Objectives
    • Working with Subqueries
    • Writing Scalar Subqueries
    • Writing Multi-Valued Subqueries
    • Demonstration: Writing Self-Contained Subqueries
    • Lesson 2: Writing Correlated Subqueries
    • Lesson Objectives
    • Working with Correlated Subqueries
    • Writing Correlated Subqueries
    • Demonstration: Writing Correlated Subqueries
    • Lesson 3: Using the EXISTS Predicate with Subqueries
    • Lesson Objectives
    • Working with EXISTS
    • Writing Queries Using EXISTS with Subqueries
    • Demonstration: Writing Subqueries Using EXISTS

    11 - Using Set Operators

    • Lesson 1: Using Views
    • Lesson Objectives
    • Writing Queries That Return Results from Views
    • Creating Simple Views
    • Demonstration: Using Views
    • Lesson 2: Using Inline TVFs
    • Lesson Objectives
    • Writing Queries That Use Inline TVFs
    • Creating Simple Inline TVFs
    • Retrieving from Inline TVFs
    • Demonstration: Inline TVFs
    • Lesson 3: Using Derived Tables
    • Lesson Objectives
    • Writing Queries with Derived Tables
    • Guidelines for Derived Tables
    • Using Aliases for Column Names in Derived Tables
    • Passing Arguments to Derived Tables
    • Nesting and Reusing Derived Tables
    • Demonstration: Using Derived Tables
    • Lesson 4: Using CTEs
    • Lesson Objectives
    • Writing Queries with CTEs
    • Creating Queries with Common Table Expressions
    • Demonstration: Using CTEs

    12 - Using Set Operators

    • Lesson 1: Writing Queries with the UNION Operator
    • Lesson Objectives
    • Interactions Between Sets
    • Using the UNION Operator
    • Using the UNION ALL Operator
    • Demonstration: Using UNION and UNION ALL
    • Lesson 2: Using EXCEPT and INTERSECT
    • Lesson Objectives
    • Using the INTERSECT Operator
    • Using the EXCEPT Operator
    • Demonstration: Using EXCEPT and INTERSECT
    • Lesson 3: Using APPLY
    • Lesson Objectives
    • Using the APPLY Operator
    • The CROSS APPLY Operator
    • The OUTER APPLY Operator
    • CROSS APPLY and OUTER APPLY Features
    • Demonstration: Using CROSS APPLY and OUTER APPLY

    13 - Using Window Ranking, Offset, and Aggregate Functions

    • Lesson 1: Creating Windows with OVER
    • Lesson Objectives
    • SQL Windowing
    • Windowing Components
    • Using OVER
    • Partitioning Windows
    • Ordering and Framing
    • Demonstration: Using OVER and Partitioning
    • Lesson 2: Exploring Window Functions
    • Lesson Objectives
    • Defining Window Functions
    • Window Aggregate Functions
    • Window Ranking Functions
    • Window Distribution Functions
    • Window Offset Functions
    • Demonstration: Exploring Windows Functions

    14 - Pivoting and Grouping Sets

    • Lesson 1: Writing Queries with PIVOT and UNPIVOT
    • Lesson Objectives
    • What Is Pivoting?
    • Elements of PIVOT
    • Writing Queries with UNPIVOT
    • Demonstration: Writing Queries with PIVOT and UNPIVOT
    • Lesson 2: Working with Grouping Sets
    • Lesson Objectives
    • Writing Queries with Grouping Sets
    • CUBE and ROLLUP
    • GROUPING_ID
    • Demonstration: Using Grouping Sets

    15 - Executing Stored Procedures

    • Lesson 1: Querying Data with Stored Procedures
    • Lesson Objectives
    • Examining Stored Procedures
    • Executing Stored Procedures
    • Demonstration: Querying Data with Stored Procedures
    • Lesson 2: Passing Parameters to Stored Procedures
    • Lesson Objectives
    • Passing Input Parameters to Stored Procedures
    • Working with OUTPUT Parameters
    • Demonstration: Passing Parameters to Stored Procedures
    • Lesson 3: Creating Simple Stored Procedures
    • Lesson Objectives
    • Creating Procedures to Return Rows
    • Creating Procedures That Accept Parameters
    • Demonstration: Creating Simple Stored Procedures
    • Lesson 4: Working with Dynamic SQL
    • Lesson Objectives
    • Constructing Dynamic SQL
    • Writing Queries with Dynamic SQL
    • Demonstration: Working with Dynamic SQL

    16 - Programming with T-SQL

    • Lesson 1: T-SQL Programming Elements
    • Lesson Objectives
    • Introducing T-SQL Batches
    • Working with Batches
    • Introducing T-SQL Variables
    • Working with Variables
    • Working with Synonyms
    • Demonstration: T-SQL Programming Elements
    • Lesson 2: Controlling Program Flow
    • Lesson Objectives
    • Understanding T-SQL Control-of-Flow Language
    • Working with IF…ELSE
    • Working with WHILE
    • Demonstration: Controlling Program Flow

    17 - Implementing Error Handling

    • Lesson 1: Implementing T-SQL Error Handling
    • Lesson Objectives
    • Errors and Error Messages
    • Raising Errors Using RAISERROR
    • Raising Errors Using THROW
    • Using @@Error
    • Creating Alerts When Errors Occur
    • Demonstration: Handling Errors Using T-SQL
    • Lesson 2: Implementing Structured Exception Handling
    • Lesson Objectives
    • TRY/CATCH Block Programming
    • Error Handling Functions
    • Catchable vs. Noncatchable Errors
    • Rethrowing Errors Using THROW
    • Errors in Managed Code
    • Demonstration: Using a TRY/CATCH Block

    18 - Implementing Transactions

    • Lesson 1: Transactions and the Database Engine
    • Lesson Objectives
    • Defining Transactions
    • The Need for Transactions: Issues with Batches
    • Transactions Extend Batches
    • Demonstration: Transactions and the Database Engine
    • Lesson 2: Controlling Transactions
    • Lesson Objectives
    • BEGIN TRANSACTION
    • COMMIT TRANSACTION
    • ROLLBACK TRANSACTION
    • Using XACT_ABORT
    • Demonstration: Controlling Transactions

  • Enroll Today

    This is a 5-day class

    Price: $2,975.00
    Payment Options

    ILT Instructor‑Led Training

    OLL Online LIVE

    GTR  Guaranteed to Run

    Class times are listed Eastern time. This class is available for Private Group Training

    To sort by location or date, click the ‘When’ and ‘Where’ column headings.

    Cart When Time   Where How
    12/17/2018 11:00AM - 7:00PM GTR Online LIVE OLL
    01/07/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    01/14/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    01/21/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    02/04/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    02/11/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    02/25/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    03/04/2019 11:00AM - 7:00PM GTR Online LIVE OLL
    03/18/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    03/25/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    04/01/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    04/08/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    04/22/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    04/29/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    05/13/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    05/20/2019 11:00AM - 7:00PM GTR Online LIVE OLL
    06/03/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    06/10/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    06/24/2019 11:00AM - 7:00PM GTR Online LIVE OLL
    07/08/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    07/15/2019 9:00AM - 5:00PM GTR Online LIVE OLL
    07/22/2019 9:00AM - 5:00PM GTR Online LIVE OLL

    Class times are listed Eastern time
    This class is available for Private Group Training