Microsoft Learning Logo is a registered trademark of Microsoft. All rights reserved.

Querying Data with Transact-SQL

Schedule

Start End Duration Location Details

Course Details

Querying Data with Transact-SQL

Course Code: 20761

Duration: 5 days

Prerequisite:  

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

Course Description:

This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first three days can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum. Days 4 & 5 teach the remaining skills required to take exam 70-761.

Course Objectives:

After completing this course, students will be able to: 

  • Describe key capabilities and components of SQL Server. •
  • Describe T-SQL, sets, and predicate logic. 
  • Write a single table SELECT statement. 
  • Write a multi-table SELECT statement. 
  • Write SELECT statements with filtering and sorting. 
  • Describe how SQL Server uses data types. 
  • Write DML statements. 
  • Write queries that use built-in functions. 
  • Write queries that aggregate data. 
  • Write subqueries. 
  • Create and implement views and table-valued functions. 
  • Use set operators to combine query results. 
  • Write queries that use window ranking, offset, and aggregate functions. 
  • 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.

Intended Audience:

The main purpose of the 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.

Course Outline:

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

Module 2: Introduction to T-SQL Querying 

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

Module 3: Writing SELECT Queries 

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

Module 4: Querying Multiple Tables 

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

Module 5: Sorting and Filtering Data 

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

Module 6: Working with SQL Server Data Types 

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

Module 7: Using DML to Modify Data 

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

Module 8: Using Built-In Functions 

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

Module 9: Grouping and Aggregating Data 

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

Module 10: Using Subqueries 

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

Module 11: Using Table Expressions 

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

Module 12: Using Set Operators

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

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

  • Creating Windows with OVER 
  • Exploring Window Functions

Module 14: Pivoting and Grouping Sets 

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

Module 15: Executing Stored Procedures 

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

Module 16: Programming with T-SQL 

  • T-SQL Programming Elements 
  • Controlling Program Flow

Module 17: Implementing Error Handling 

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

Module 18: Implementing Transactions 

  • Transactions and the database engines 
  • Controlling transactions