Duration:

5 Days

Audience:

Employees of federal, state and local governments; and businesses working with the government.

Course Description:

In this course, you will gain a comprehensive understanding of the Transact SQL language, which is used by all SQL Server-related disciplines including database administration, database development and business intelligence.

Learning Objectives:

After finishing this course, you’ll have the confidence to use Transact-SQL commands to interact with relational databases.

You will be able to:

  • Describe the key capabilities and components of SQL Server
  • Confidently speak about T-SQL, sets and predicate logic
  • Write single- and multi-table SELECT statements
  • Explain how SQL Server uses data types
  • Manipulate set operators to combine query results
  • Work with and implement views and table-valued functions
  • Write DML statements and queries
  • Transform data by implementing pivot, unpivot, rollup and cube
  • Create and implement stored procedures
  • Add programming constructs such as variables, conditions and loops to T-SQL code

Course Outline

1 – INTRODUCTION TO MICROSOFT SQL SERVER 2016
  • Learn the Basics of SQL Server Architecture
  • Get Exposed to the Different SQL Server Editions and Versions
  • Become Familiar With SQL Server Management Studio
  • Lab: Working with SQL Server 2016 Tools
2 – INTRODUCTION TO T-SQL QUERYING
  • Learn the Components of T-SQL
  • Understand Sets and Predicate Logic
  • Master the Logical Order of Operations in SELECT statements
  • Lab: Introduction to Transact-SQL Querying
3 – WRITING SELECT QUERIES
  • Write Simple SELECT Statements and CASE Expressions
  • Eliminate Duplicates With DISTINCT
  • Practice Using Column and Table Aliases
  • Lab: Writing Basic SELECT Statements
4 – QUERYING MULTIPLE TABLES
  • Understand Joins
  • Query with Inner Joins, Outer Joins, Cross Joins and Self Joins
  • Lab: Querying Multiple Tables
5 – SORTING AND FILTERING DATA
  • Learn How to Sort Data
  • Filter Data with Predicates
  • Work With Unknown Values
  • Lab: Sorting and Filtering Data
6 – WORKING WITH SQL SERVER 2016 DATA TYPES
  • Learn About SQL Server 2016 Data Types
  • Work With Character Data and Date and Time Data
  • Lab: Working with SQL Server 2016 Data Types
7 – USING DML TO MODIFY DATA
  • Insert Data
  • Modify and Delete Data
  • Lab: Using DML to Modify Data
8 – USING BUILT-IN FUNCTIONS
  • Write Queries with Built-In Functions
  • Practice Conversion Functions and Logical Functions
  • Practice Using Functions to Work With NULL
  • Lab: Using Built-In Functions
9 – GROUPING AND AGGREGATING DATA
  • Use Aggregate Functions and the GROUP BY Clause
  • Filter Groups With HAVING
  • Lab: Grouping and Aggregating Data
10 – USING SUBQUERIES
  • Write Self-Contained and Correlated Subqueries
  • Execute the EXISTS Predicate with Subqueries
  • Lab: Using Subqueries
11 – USING TABLE EXPRESSIONS
  • Navigate Views and Inline Table-Valued Functions
  • Manipulate Derived Tables and Common Table Expressions
  • Lab: Using Table Expressions
12 – USING SET OPERATORS
  • Complete Queries with the UNION Operator
  • Use EXCEPT, INTERSECT and APPLY
  • Lab: Using Set Operators
13 – USING WINDOWS RANKING, OFFSET, AND AGGREGATE FUNCTIONS
  • Create Windows with OVER
  • Explore Window Functions
  • Lab: Using Windows Ranking, Offset, and Aggregate Functions
14 – PIVOTING AND GROUPING SETS
  • Write Queries with PIVOT and UNPIVOT
  • Work with Grouping Sets
  • Lab: Pivoting and Grouping Sets
15 – EXECUTING STORED PROCEDURES
  • Query Data with Stored Procedures
  • Pass Parameters to Stored procedures
  • Create Simple Stored Procedures
  • Navigate Dynamic SQL
  • Lab: Executing Stored Procedures
16 – PROGRAMMING WITH T-SQL
  • Master T-SQL Programming Elements
  • Control Program Flow
  • Lab: Programming with T-SQL
17 – IMPLEMENTING ERROR HANDLING
  • Execute T-SQL Error Handling
  • Implement Structured Exception Handling
  • Lab: Implementing Error Handling
18 – IMPLEMENTING TRANSACTIONS
  • Learn About Transactions and Database Engines
  • Control Transactions
  • Lab: Implementing Transactions