20761A: Querying Data with Transact-SQL (3 Days)

The main purpose of this 3-day icourse 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.This is a cut-down version of 5-day class 20761.

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

• 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

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.

Introduction to Microsoft SQL

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

Introduction to T-SQL Querying

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

Writing SELECT Queries

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

Querying Multiple Tables

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

Sorting and Filtering Data

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

Working with SQL Server 2016 Data Types

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

Using DML to Modify Data

• Inserting Data
• Modifying and Deleting Data

Using Built-In Functions

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

Grouping and Aggregating Data

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

Using Subqueries

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

Using Set

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