MOC 20761 Querying Data with Transact-SQL

How To Take This Class

Live Instructor-Led Online Class

Cost: $2,375.00 or 5 Vouchers

  • Open enrollment class for individuals
  • Live class with an instructor
  • Free class retakes forever!
  • Six months of instructor email support
  • Hands-on exercises and student labs
  • Classes never cancelled due to low enrollment
  • Money-back guarantee

Onsite or Offsite Group Training

Cost: Based on number of students

  • For groups as small as 3 people
  • Class Held at our location or yours
  • Hands-on exercises and student labs
  • Customization at no extra charge
  • Six months of instructor email support
  • All-inclusive pricing
  • Money-back guarantee
Request Group Pricing Proposal

Course Duration

5 Days

What Students Receive

Each student will receive:
  • Expert instruction from a SQL Server Trainer
  • A printed courseware manual with samples and hands-on exercises for use during and after class.
  • Sample course files and solutions
  • Access to a complete, online SQL Server lab environment both during and after class to practice your new skills.
  • A course completion certificate upon successful completion of the course

Course Description

In this Querying Data with Transact-SQL Training course, students will learn how to use T-SQL to select, modify and delete data. In addition, students will learn about SQL Server data types, how to use built-in functions in T-SQL statements, how to write subqueries, how to implement views and create and use stored procedures and how to use programming constructs in T-SQL such as loops, variables and conditions. In addition, this course helps prepare students who are interested in either moving on to other more in-depth SQL Server courses or who are preparing to take the Microsoft 70-761 Querying Data with Transact-SQL certification exam.

Course Objectives

Upon successful completion of 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.

Course 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 Prerequisites

To ensure success in this course, students should have:
  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.

Certification Exam

This course helps prepare students to take the 70-761 Querying Data with Transact-SQL Exam

Course Syllabus

  1. Introduction to Microsoft SQL Server
    • The Basic Architecture of SQL Server
    • SQL Server Editions and Versions
    • Getting Started with SQL Server Management Studio
    • Lab: Working with SQL Server Tools
      • Working with SQL Server Management Studio
      • Creating and Organizing T-SQL Scripts
      • Using Books Online
  2. 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
      • Executing Basic SELECT Statements
      • Executing Queries that Filter Data using Predicates
      • Executing Queries That Sort Data Using ORDER BY
  3. 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
      • Writing Simple SELECT Statements
      • Eliminating Duplicates Using DISTINCT
      • Using Column and Table Aliases
      • Using a Simple CASE Expression
  4. Querying Multiple Tables
    • Understanding Joins
    • Querying with Inner Joins
    • Querying with Outer Joins
    • Querying with Cross Joins and Self Joins
    • Lab: Querying Multiple Tables
      • Writing Queries that use Inner Joins
      • Writing Queries that use Multiple-Table Inner Joins
      • Writing Queries that use Self-Joins
      • Writing Queries that use Outer Joins
      • Writing Queries that use Cross Joins
  5. 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
      • Writing Queries that Filter Data using a WHERE Clause
      • Writing Queries that Sort Data Using an ORDER BY Clause
      • Writing Queries that Filter Data Using the TOP Option
      • Write Queries that filter data using the OFFSET-FETCH clause
  6. Working with SQL Server Data Types
    • Introducing SQL Server Data Types
    • Working with Character Data
    • Working with Date and Time Data
    • Lab: Working with SQL Server Data Types
      • Writing Queries that Return Date and Time Data
      • Writing Queries that use Date and Time Functions
      • Writing Queries That Return Character Data
      • Writing Queries That Return Character Functions
  7. Using DML to Modify Data
    • Adding Data to Tables
    • Modifying and Removing Data
    • Generating automatic column values
    • Lab: Using DML to Modify Data
      • Inserting Records with DML
      • Updating and Deleting Records Using DML
  8. Using Built-In Functions
    • Writing Queries with Built-In Functions
    • Using Conversion Functions
    • Using Logical Functions
    • Using Functions to Work with NULL
    • Lab: Using Built-In Functions
      • Writing Queries That Use Conversion Functions
      • Writing Queries that use Logical Functions
      • Writing Queries that Test for Nullability
  9. Grouping and Aggregating Data
    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING
    • Lab: Grouping and Aggregating Data
      • Writing Queries That Use the GROUP BY Clause
      • Writing Queries that Use Aggregate Functions
      • Writing Queries that Use Distinct Aggregate Functions
      • Writing Queries that Filter Groups with the HAVING Clause
  10. Using Subqueries
    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries
    • Lab: Using Subqueries
      • Writing Queries That Use Self-Contained Subqueries
      • Writing Queries That Use Scalar and Multi-Result Subqueries
      • Writing Queries That Use Correlated Subqueries and an EXISTS Clause
  11. Using Table Expressions
    • Using Views
    • Using Inline Table-Valued Functions
    • Using Derived Tables
    • Using Common Table Expressions
    • Lab: Using Table Expressions
      • Writing Queries That Use Views
      • Writing Queries That Use Derived Tables
      • Writing Queries That Use Common Table Expressions (CTEs)
      • Writing Queries That Use Inline Table-Valued Expressions (TVFs)
  12. Using Set Operators
    • Writing Queries with the UNION operator
    • Using EXCEPT and INTERSECT
    • Using APPLY
    • Lab: Using Set Operators
      • Writing Queries That Use UNION Set Operators and UNION ALL
      • Writing Queries That Use CROSS APPLY and OUTER APPLY Operators
      • Writing Queries That Use the EXCEPT and INTERSECT Operators
  13. Using Windows Ranking, Offset, and Aggregate Functions
    • Creating Windows with OVER
    • Exploring Window Functions
    • Lab: Using Windows Ranking, Offset, and Aggregate Functions
      • Writing Queries that use Ranking Functions
      • Writing Queries that use Offset Functions
      • Writing Queries that use Window Aggregate Functions
  14. Pivoting and Grouping Sets
    • Writing Queries with PIVOT and UNPIVOT
    • Working with Grouping Sets
    • Lab: Pivoting and Grouping Sets
      • Writing Queries that use the PIVOT Operator
      • Writing Queries that use the UNPIVOT Operator
      • Writing Queries that use the GROUPING SETS CUBE and ROLLUP Subclauses
  15. 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
      • Using the EXECUTE statement to Invoke Stored Procedures
      • Passing Parameters to Stored procedures
      • Executing System Stored Procedures
  16. Programming with T-SQL
    • T-SQL Programming Elements
    • Controlling Program Flow
    • Lab: Programming with T-SQL
      • Declaring Variables and Delimiting Batches
      • Using Control-Of-Flow Elements
      • Using Variables in a Dynamic SQL Statement
      • Using Synonyms
  17. Implementing Error Handling
    • Implementing T-SQL error handling
    • Implementing structured exception handling
    • Lab: Implementing Error Handling
      • Redirecting errors with TRY/CATCH
      • Using THROW to pass an error message back to a client
  18. Implementing Transactions
    • Transactions and the database engines
    • Controlling transactions
    • Lab: Implementing Transactions
      • Controlling transactions with BEGIN, COMMIT, and ROLLBACK
      • Adding error handling to a CATCH block