Querying Microsoft SQL Server 2012/2014 (70-461)

(70-461-v2)/ISBN:978-1-64459-034-8

This course includes
Lessons
TestPrep
Hand-on Lab
AI Tutor (Add-on)

Kick start your prep for the MCSA 70-461 exam with the Querying Microsoft SQL Server 2012/2014 (70-461) course and lab. The lab is cloud-based, device-enabled, and can easily be integrated with an LMS. The SQL training course and lab completely cover the 70-461 exam objectives and provide an understanding of topics such as creating database objects; working and modifying data; troubleshooting and optimizing queries and transactions, and more.

Here's what you will get

The Querying Microsoft SQL Server 70-461 exam is a part of the MCSA SQL Server 2012 certification. The MCSA SQL Server 70-461 exam validates a candidate's expertise in creating and altering tables using T-SQL syntax, querying and managing XML data, modifying data by using INSERT, UPDATE and DELETE commands, and so on. The MCSA 70-461is intended for SQL Server database administrators, system engineers, and developers with two or more years of experience.

Lessons

8+ Lessons | 50+ Quizzes | 50+ Flashcards | 50+ Glossary of terms

TestPrep

50+ Pre Assessment Questions | 2+ Full Length Tests | 50+ Post Assessment Questions | 100+ Practice Test Questions

Hand on lab

43+ LiveLab | 23+ Video tutorials | 00+ Minutes

Here's what you will learn

Download Course Outline

Lessons 1: Introduction

  • About SQL Server 2012/2014
  • About Transact-SQL (T-SQL)

Lessons 2: Managing Tables and Views

  • Creating Tables
  • Modifying Existing Tables
  • Understanding Indexes
  • Understanding Views
  • Partitioning Tables and Indexes
  • Summary

Lessons 3: Creating Constraints and Triggers

  • Creating and Modifying Constraints
  • Understanding Triggers
  • Modifying Triggers
  • Summary

Lessons 4: Working with Data

  • Using SELECT Statements
  • Implementing Subqueries
  • Implementing Data Types
  • Implementing Aggregate Queries
  • Managing XML Data
  • Summary

Lessons 5: Modifying Data

  • Understanding Stored Procedures
  • Using DML Statements
  • Combining Datasets
  • Working with Functions
  • Summary

Lessons 6: Troubleshooting and Optimizing Queries

  • Tuning and Optimizing Queries
  • Managing Transactions
  • Evaluating Operations
  • Implementing Error Handling
  • Summary

Appendix A

  • Installing Microsoft SQL Server 2014 Management Studio
  • Connecting to the Server

Appendix B

  • 70-461 Exam Objectives

Hands-on LAB Activities

Managing Tables and Views

  • Creating a Table
  • Adding a New Column in a Table
  • Deleting a Column from a Table
  • Deleting a Table
  • Modifying a Column in a Table
  • Creating a Clustered Index
  • Creating a Non-Clustered Index
  • Deleting an Index
  • Creating a Filtered Index
  • Creating an Indexed View
  • Creating a View

Creating Constraints and Triggers

  • Creating a Primary Key Constraint
  • Creating a Foreign Key Constraint
  • Creating a Unique Constraint
  • Dropping a Constraint
  • Creating a DML Trigger
  • Creating a DDL Trigger
  • Using INSTEAD OF Trigger

Working with Data

  • Using Logical Operators
  • Using the SELECT statement
  • Retrieving Specific Columns
  • Using Calculated Columns
  • Limiting Rows
  • Using the LIKE Operator
  • Using the BETWEEN Clause
  • Using the PIVOT Operator
  • Using Appropriate Data Types
  • Using Subqueries
  • Using the Analytic Function
  • Working with Ranking Functions
  • Using the XML Data Type

Modifying Data

  • Creating a Stored Procedure
  • Adding New Data
  • Updating a Record in a Table
  • Removing a Record from the Table
  • Combining Data Sets
  • Using User-Defined Functions

Troubleshooting and Optimizing Queries

  • Displaying the Estimated Execution Plan
  • Using SET STATISTICS IO
  • Using the DECLARE and SET statements
  • Using the BEGIN, COMMIT, and ROLLBACK TRANSACTION Statements
  • Evaluating Operations
  • Implementing Error Handling

Exam FAQs

There are no technical prerequisites for this certification.

USD 165

The exam contains 40-60 questions.

120 minutes

The passing score varies from exam to exam and is provided on the score report.

General Microsoft Certification exam retake policy

  • If a candidate does not achieve a passing score on an exam the first time, the candidate must wait 24 hours before retaking the exam. At that time, candidates can log onto their certification dashboard and reschedule the exam themselves.
  • If a candidate does not achieve a passing score the second time, the candidate must wait at least 14 days before retaking the exam a third time.
  • A 14-day waiting period is also imposed between the third and fourth attempts and fourth and fifth attempts.
  • A candidate may not take a given exam any more than five times per year (12 months). This 12-month period starts from the first attempt. The candidate is eligible to retake the exam 12 months from the date of their first attempt.
  • To take a given exam more than five times per year or to have the time between attempts waived, a candidate must have experienced one of the errors listed below and request an exception from Microsoft. Requests should be sent to mlsecure@microsoft.com. These requests must include the following information:
    • Candidate ID
    • First/Last Name
    • Email associated with your Candidate ID
    • Exam Registration ID
    • Exam Number
    • Description of error
    • Pearson VUE case number

Exam retake exceptions will be granted for the following reasons:

  • Failure due to lab crash
  • Failure due to Internet connectivity issues
  • Failure due to equipment failure

For life

Customer Review

The 70-461 exam study guide covers all the objectives of 70-461 exam and includes topics such as creating database objects; working and modifying data; troubleshooting and optimizing queries and transactions. I strongly recommend taking this training as it exposes you to even more information than needed to succeed in a data related position.