I.       Overview:

The main purpose of this 6 day instructor led 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. 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.

II.    Duration: 06 days (48 hours)

III. 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
• Execute stored procedures.
• Program with T-SQL
• Implement error handling
• Implement transactions.
• Design and Implement Tables.
• Describe advanced table designs.
• Ensure Data Integrity through Constraints.
• Design and Implement Views.
• Design and Implement Stored Procedures.
• Design and Implement User Defined Functions

IV. Intended Audience:

This course is intended for Database Administrators, Database Developers, 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.

V.    Prerequisites:

This course requires that you meet the following prerequisites:

• Working knowledge of relational databases.
• Basic knowledge of the Microsoft Windows operating system and its core functionality.

VI. Course outlines:

Module 1: 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

Module 2: 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

Module 3: Querying Multiple Tables

• Understanding Joins
• Querying with Inner Joins
• Querying with Outer Joins
• Querying with Cross Joins and Self Joins
• Lab: Querying Multiple Tables

Module 4: 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

Module 5: Working with SQL Server 2016 Data Types

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

Module 6: Using DML to Modify Data

• Adding Data to Tables
• Modifying and Removing Data
• Generating Automatic Column Values
• Using DML to Modify Data

Module 7: Using Built-In Functions

• Lesson 1: Writing Queries with Built-In Functions
• Lesson 2: Using Conversion Functions
• Lesson 3: Using Logical Functions
• Lesson 4: Using Functions to Work with NULL
• Lab: Using Built-in Functions

Module 8: Grouping and Aggregating Data

• Using Aggregate Functions
• Using the GROUP BY Clause
• Filtering Groups with HAVING
• Lab: Grouping and Aggregating Data

Module 9: Using Subqueries

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

Module 10: Using Table Expressions

• Using Views
• Using Inline TVFs
• Using Derived Tables
• Using CTEs
• Lab: Using Table Expressions

Module 11: Using Set Operators

• Writing Queries with the UNION Operator
• Using EXCEPT and INTERSECT
• Using APPLY
• Lab: Using Set Operators

Module 12: 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

Module 13: Programming with T-SQL

• T-SQL Programming Elements
• Controlling Program Flow
• Lab: Programming with T-SQL

Module 14: Implementing Error Handling

• Implementing T-SQL Error Handling
• Implementing Structured Exception Handling
• Lab: Implementing Error Handling

Module 15: Implementing Transactions

• Transactions and the Database Engine
• Controlling Transactions
• Lab: Implementing Transactions

Module 16: Designing and Implementing Tables

Describes the design and implementation of tables. (Note: partitioned tables are not covered)

• Designing Tables
• Data Types
• Working with Schemas
• Creating and Altering Tables
• Lab: Designing and Implementing Tables

Module 17: Advanced Table Designs

Describes more advanced table designs.

• Partitioning Data
• Compressing Data
• Temporal Tables
• Lab: Using Advanced Table Designs

Module 18: Ensuring Data Integrity through Constraints

Describes the design and implementation of constraints.

• Enforcing Data Integrity
• Implementing Data Domain Integrity
• Implementing Entity and Referential Integrity
• Lab: Using Data Integrity Through Constraints

Module 19: Introduction to Indexes

Describes the concept of an index and discusses selectivity, density and statistics. It covers appropriate data type choices and choices around composite index structures.

• Core Indexing Concepts
• Data Types and Indexes
• Heaps, Clustered, and Nonclustered Indexes
• Single Column and Composite Indexes
• Lab: Implementing Indexes

Module 20: Designing and Implementing Views

Describes the design and implementation of views.

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views
• Lab: Designing and Implementing Views

Module 21: Designing and Implementing Stored Procedures

Describes the design and implementation of stored procedures.

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context
• Lab: Designing and Implementing Stored Procedures

Module 22: Designing and Implementing User-Defined Functions

Describes the design and implementation of functions, both scalar and table-valued. (Also discusses where they can lead to performance issues)

• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Considerations for Implementing Functions
• Alternatives to Functions
• Lab: Designing and Implementing User-Defined Functions