Live Chat
Monday - Friday 8am - 6pm EST Chat Now
Contact Us
Monday - Friday 8am - 8pm EST 1-800-268-7737 Other Contact Options
Checkout

Cart () Loading...

    • Quantity:
    • Delivery:
    • Dates:
    • Location:

    $

Querying Data with Transact-SQL (20761)

New – Learn basic Transact-SQL queries for Microsoft SQL Server 2016.

GK# 4395 Vendor# M20761

$895 - $2995 USD

Enroll Request Group Training

Course Overview

TOP

This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first part of the content can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum.  The remaining content focuses on the skills required to take exam 70-761.

This is MOC for course 20761.

*For more information on Microsoft SATVs, click here.

This course is also available in the On-Demand delivery format with digital Microsoft Official Courseware (dMOC). Click here to purchase.

Schedule

TOP
  • Delivery Format:
  • Date:
  • Location:
  • Access Period:

$

What You'll Learn

TOP
  • 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

Outline

TOP
Viewing outline for:

Classroom Live Outline

1. Introduction to Microsoft SQL

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

2. Introduction to T-SQL Querying

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

3. Writing SELECT Queries

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

4. Querying Multiple Tables

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

5. Sorting and Filtering Data

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

6. Working with SQL Server 2016 Data Types

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

7. Using DML to Modify Data

  • Inserting Data
  • Modifying and Deleting Data

8. Using Built-In Functions

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

9. Grouping and Aggregating Data

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

10. Using Subqueries

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

11. Using Set Operators

12. Using Set

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

13. Using Windows Ranking, Offset, and Aggregate 

  • Creating Windows with OVER
  • Exploring Window Functions

14. Pivoting and Grouping Sets

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

15. Executing Stored 

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

16. Programming with T-SQL

  • T-SQL Programming Elements
  • Controlling Program Flow

17. Implementing Error Handling

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

18. Implementing Transactions

  • Transactions and the Database Engines
  • Controlling Transactions

Labs

TOP
Viewing labs for:

Classroom Live Labs

Lab 1: Working with SQL Server 2016 Tools
Lab 2: Introduction to Transact-SQL Querying
Lab 3: Writing Basic SELECT Statements
Lab 4: Querying Multiple Tables
Lab 5: Sorting and Filtering Data
Lab 6: Working with SQL Server 2016 Data Types
Lab 7: Using DML to Modify Data
Lab 8: Using Built-In Functions
Lab 9: Grouping and Aggregating Data
Lab 10: Using Subqueries
Lab 11: Using Table Expressions
Lab 12: Using SET Operators
Lab 13: Using Windows Ranking, Offset, and Aggregate 
Lab 14: Pivoting and Grouping Sets
Lab 15: Executing Stored 
Lab 16: Programming with T-SQL
Lab 17: Implementing Error Handling
Lab 18: Implementing Transactions

Prerequisites

TOP

Students should have a working knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality.

Who Should Attend

TOP

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.

Vendor Credits

TOP

SATV Redemption

Redeeming your Microsoft Software Assurance Training Vouchers:

  • Redeem five SATVs for a Classroom Live or Virtual Classroom Live session
  • Redeem five SATVs for an individual On-Demand course plus digital Microsoft Official Courseware (dMOC)
  • Redeem two SATVs for an individual On-Demand course

For more information on Microsoft SATVs, click here.

Follow-On Courses

TOP

The following courses will assist you in expanding your SQL Server database administration and development skills. 

Training Exclusives

This course comes with 12 months access to the following benefits:

  • Practice Labs
  • 24x7 Mentoring
  • Indexed Class Recordings
  • Unlimited Course Retakes
  • Digital courseware
Find out more
Course Delivery

This course is available in the following formats:

Classroom Live

Receive face-to-face instruction at one of our training center locations.

Duration: 5 day

Virtual Classroom Live

Experience expert-led online training from the convenience of your home, office or anywhere with an internet connection.

Duration: 5 day

On-Demand

Train at your own pace with 24/7 access to courses that help you acquire must-have technology skills.

Duration: 40 hours

Request this course in a different delivery format.
Enroll