20761C TrainerPrepGuide PDF
20761C TrainerPrepGuide PDF
20761C TrainerPrepGuide PDF
The course focuses on the core querying tasks that a database developer must perform. As well as
querying, filtering, and updating data, this course also includes error handling, transactions, and
techniques for improving query performance.
The course has been written in such a way as those students only wishing to understand T-SQL as it’s
required for the remainder of the SQL curriculum only need attend days 1 thru 3. Days 4 and 5 teach
additional skills required for exam 70-761.
Module 1
Module 1 provides an overview of SQL Server. It describes the components, editions, and versions of SQL
Server 2016, SQL Server services and configuration options, and service accounts.
Module 2
Module 2 Introduces Transact-SQL and compares it to other languages.
Module 3
In Module 3, students learn how to write basic SELECT queries.
Module 4
In Module 4, students learn how to query multiple tables and return the results in a single dataset.
Module 5
Module 5 discusses how to sort the data and how to filter the query results to find the specific records
that are of interest.
Module 6
Module 6 discusses SQL Server data types and how to convert one data type to another.
Module 7
Module 7 describes how to use T-SQL to insert, update, and delete data.
Module 8
Module 8 discusses the use of built-in functions to work with the results of a data set.
Module 9
In Module 9, students learn how to group records and then provide aggregate summaries for these
groupings.
Trainer Preparation Guide for Course 20761C: Querying Data with Transact-SQL 2
Module 10
Module 10 discusses the ability to create a query within a query and includes correlated subqueries.
Module 11
Module 10 introduces table expressions.
Module 12
In Module 12, students will learn how to use the set operators UNION, INTERSECT, and EXCEPT to
compare rows between two input sets.
Module 13
Describe the benefits to using window functions. Restrict window functions to rows defined in an OVER
clause, including partitions and frames. Write queries that use window functions to operate on a window
of rows and return ranking, aggregation, and offset comparison results.
Module 14
Write queries that pivot and unpivot result sets. Write queries that specify multiple groupings with
grouping sets.
Module 15
Return results by executing stored procedures. Pass parameters to procedures. Create simple stored
procedures that encapsulate a SELECT statement. Construct and execute dynamic SQL with EXEC and
sp_executesql.
Module 16
Enhance your T-SQL code with programming elements.
Module 17
Implementing T-SQL error handling. Implementing structured error handling.
Module 18
Transactions and the database engine. Controlling transactions.
Course Handbook
Course Companion Content on the http://www.microsoft.com/learning/companionmoc/ site
Important It is recommended that you use PowerPoint 2003 or later to display the slides
for this course. If you use PowerPoint Viewer or an earlier version of PowerPoint, all the
features of the slides might not display correctly.
Preparation Tasks
Complete the following tasks to prepare for this course.
Courses or Workshops
It is highly recommended that you audit the following courses or workshops:
Exams
To identify your technical proficiency with the content of this course, it is highly recommended that you
pass the exams necessary to achieve the following certification:
Use the OneNote Trainer Pack (OTP) on MCT Download Center to prepare for delivering the course.
The OneNote Trainer Packs include the following content for each course on each page in the
OTP (and in this order):
Slides
Instructor Notes
Student Handbook Content
Also included for each module are the Lab Answer Keys (LAKs).
Practice setting up the classroom by following the instructions in the Microsoft® Hyper-V™
Classroom Setup Guide.
Review the learning product error log, which is available on the MCT Download Center.
Practice using the Microsoft product(s) and tools that are associated with this learning product:
SQL Server 2017
Review the Microsoft product error log, which is available in the Microsoft Download Center.
Trainer Preparation Guide for Course 20761C: Querying Data with Transact-SQL 4
Walk through the Introduction slide deck for the learning product.
Walk through each module presentation slide deck and read the corresponding Instructor Notes
(located in the notes view of the presentation slide deck) for the module. Note that additional hidden
slides are used in each slide deck to accommodate the amount of Instructor Notes information for a
given topic.
Familiarize yourself with the Course Handbook and the Course Companion Content on the
http://www.microsoft.com/learning/companionmoc site. Make note of when to direct students'
attention to the Course Companion Content for further learning support. More information
pertaining to the course components is present in the Introduction slide deck.
Identify the key points and must-know information for each topic.
Identify examples, analogies, impromptu demonstrations, and additional delivery tips that will help to
clarify module content and provide a more meaningful learning experience for your specific
audience.
Note any problems that you might encounter during a demonstration or a lab exercise, and
determine a course of action for how you will resolve the problems in the classroom. To access the
lab answer keys, refer to the appendix in the Course Handbook.
Work through the Module Review and Takeaways section at the end of each module and determine
how you will use this section to reinforce student learning and promote knowledge transfer to on-
the-job performance.
Note that there are a lot of services installed on the main MIA-SQL virtual machine, and occasionally some
services may fail to start in a timely fashion. Each demo and lab includes a setup script that restarts SQL
Server services.
If you experience unexpected errors, use the Services app in Administrative Tools to ensure that all
services that are configured to start automatically are running.
Trainer Preparation Guide for Course 20761C: Querying Data with Transact-SQL 5
Note: The virtual machines for this course are un-activated with a grace period of 10 days. After this grace
period, the virtual machines may shut down after 30 minutes of inactivity. It is particularly likely that the
MIA-DC domain controller will shut down unnoticed by students, which may lead to unexpected errors
during labs. If you are teaching the course with virtual machines that have been started more than 10
days previously, consider reverting all virtual machines to the snapshot that was created after the virtual
machines were imported into the Hyper-V host, started, and rearmed during classroom setup. This will
reset the grace period.
Course Timing
The following schedule is an estimate of the course timing. Your timing might vary. Every student might
not finish every lab. Use your judgment to set a reasonable time to move on to the next module.
Day 1
Start End Module
9:00 9:30 Introduction
Day 2
Start End Module
9:00 9:30 Day 1 review
Day 3
Start End Module
9:00 9:30 Day 2 review
Day 4
Start End Module
9:00 9:30 Day 3 review
Day 5
Start End Module
9:00 9:30 Day 4 review