search
    

    Home Microsoft 20461: Querying Microsoft SQL Server 2014 (Exam 70-461)

    QUICK

    INFORMATION

    course-duration

    5 Day(s) Course

    courseware

    Official Courseware

    exam-voucher

    Exam Voucher

    certified-trainer

    Certified Trainer

    one-o-one

    1:1 Training Available on Demand

    customize-batch

    Customize Your Batch

    ABOUT

    THE COURSE

    This 5-day Querying Microsoft SQL Server 2014 Exam 70-461 Training course delivers foundational knowledge to frame the Transact-SQL queries for Microsoft SQL Server 2014, by giving attention to various disciplines namely, Business Administration, Business Development, & Business Intelligence.


    This course is designed for professionals who seek interest in learning SQL Server 2012 or 2014.


    This course will help you prepare for the Exam 70-461: Writing Queries Using Microsoft® SQL Server® 2014 Transact-SQL and will earn you a certification of MCSA: SQL 2014.

    Module 1: Introduction to Microsoft SQL Server 2014

    This module describes and introduces the SQL Server platform and major tools.

    Lessons

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

    Module 2: Introduction to T-SQL Querying

    This module describes Transact-SQL as the primary querying language of SQL Server.

    Lessons

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

    Module 3: Writing SELECT Queries

    This module defines the fundamentals of the SELECT statement, focusing on queries against a single table.

    Lessons

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

    Module 4: Querying Multiple Tables

    In this module, you will learn how to write queries that combine data from multiple sources in SQL Server.

    Lessons

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

    Module 5: Sorting and Filtering Data

    This module explains how to enhance queries to limit the rows they return, and to control the order in which the rows are displayed.

    Lessons

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

    Module 6: Working with SQL Server 2014 Data Types

    This module describes the data types SQL Server uses to store data. It also explains conversions between data types, and the importance of type precedence.

    Lessons

    • SQL Server 2014 Data Types Introduction
    • Working with Date and Time Data
    • Working with Character Data

    Module 7: Using DML to Modify Data

    This module explains the use of Transact-SQL Data Manipulation Language to perform data inserts, updates, and deletes.

    Lessons

    • Inserting Data
    • Modifying and Deleting Data

    Module 8: Using Built-In Functions

    This module introduces the use of functions that are built into SQL Server Denali.

    Lessons

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

    Module 9: Grouping and Aggregating Data

    In this module you will learn methods for grouping data within a query, aggregating the grouped data and filtering groups with HAVING. 

    Lessons

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

    Module 10: Using Subqueries

    This module will introduce the use of subqueries in various parts of a SELECT statement.

    Lessons

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

    Module 11: Using Table Expressions

    This module explains T-SQL expressions which return a valid relational table, typically for further use in the query.

    Lessons

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

    Module 12: Using Set Operators

    This module defines the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets

    Lessons

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

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

    This module describes window functions including ranking, aggregate and offset function. Much of this functionality is new to SQL Server 2012.

    Lessons

    • Creating Windows with OVER
    • Exploring Window Functions

    Module 14: Pivoting and Grouping Sets

    This module explains techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause.

    Lessons

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

    Module 15: Querying data with Stored Procedures

    This module describes the use of existing stored procedures in a T-SQL querying environment. Lessons

    • Writing Queries with PIVOT and UNPIVOT
    • Creating Simple Stored Procedures
    • Working with Dynamic SQL
    • Passing Parameters to Stored Procedures

    Module 16: Programming with T-SQL

    This module gives basic information about T-SQL programming concepts and objects.

    Lessons

    • Controlling Program Flow
    • T-SQL Programming Elements

    Module 17: Implementing Error Handling

    This module describes the use of error handlers in T-SQL code.

    Lessons

    • Using TRY / CATCH Blocks
    • Working with Error Information

    Module 18: Implementing Transactions

    This module describes the concepts of transaction management in SQL Server.

    Lessons

    • Controlling Transactions
    • Transactions and the Database Engine

    Module 19: Appendix 1: Improving Query Performance

    This module discusses several key guidelines for writing well-performing queries, as well as ways to monitor the execution of your queries.

    Lessons

    • Displaying Query Performance Data
    • Factors in Query Performance

    Module 20: Appendix 2: Querying SQL Server Metadata

    SQL Server provides access to structured metadata by using a variety of mechanisms. In this module, you will learn how to write queries to return system metadata using these mechanisms.

    Lessons

    • Querying System Catalog Views and Functions
    • Executing System Stored Procedures
    • Querying Dynamic Management Objects

    The 20461: Querying Microsoft SQL Server 2014 (Exam 70-461) training course is ideal for those working with the profiles of:

    • Database Administrators,
    • Database Developers, and
    • Business Intelligence professionals.
    • The course is also attracted to SQL power users namely report writers, client application developers, and business analysts.

    • Basic understanding of relational databases.

    Name Code Fee
    Microsoft Certified Professional (MCP) - [70-461] 70-461 USD 108

    enquiry

    ENQUIRY FORM

    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.6 stars by www.facebook.com/mercurysol based on 18 reviews.