Class Details

Price: $5,290

Price Match Guarantee Phoenix TS

10-Day Course Includes:

  • Class exercises in addition to training instruction
  • Courseware books, notepads, pens, highlighters and other materials
  • Exam Voucher for 70-461, 70-462 and 70-463
  • Full breakfast with variety of bagels, fruits, yogurt, doughnuts and juice
  • Tea, coffee, and soda available throughout the day
  • Freshly baked cookies every afternoon - *only at participating locations

Redeem Training for FREE with Microsoft SATVs

For group training options please contact our Training and Certification Consultants at (240) 667-7757 or email sales@phoenixts.com.

Course Outline

20461: Querying Microsoft SQL Server 2014

Microsoft SQL Server 2014 Intro

  • SQL ServerArchitecture
  • SQL Server Versions
  • SQL Server Management Studio

T-SQL Querying Intro

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

SELECT Queries

  • Simple SELECT Statements
  • DISTINCT for Deleting Duplicates
  • Column and Table Aliases
  • CASE Expressions

Querying Multiple Tables

  • Joins
  • Inner Joins
  • Outer Joins
  • Cross Joins and Self Joins

Filtering and Sorting Data

  • Sorting Data
  • Filtering Data using Predicates
  • TOP and OFFSET-FETCH Filtering Options
  • Unknown and Missing Values

SQL Server 2014 Data Types

  • SQL Server 2014 Data Types
  • Character Data
  • Date and Time Data

DML for Editing Data

  • Adding Data
  • Editing and Eliminating Data

Built-In Functions

  • Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Functions for NULL

Aggregating and Grouping Data

  • Aggregate Functions
  • GROUP BY Clause
  • HAVING and Filtering Groups

Subqueries

  • Uses of queries nested within other queries 
  • Self-Contained Subqueries
  • Correlated Subqueries
  • EXISTS Predicate and Subqueries

Table Expressions

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

Set Operators

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

Window Ranking, Offset and Aggregate Functions

  • Windows with OVER
  • Window Functions

Grouping and Pivoting Sets

  • Queries with PIVOT and UNPIVOT
  • Grouping Sets

Querying Data with Stored Procedures

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

T-SQL Programming

  • T-SQL Programming
  • Program Flow Controls

Error Handling Implementation

  • TRY / CATCH Blocks
  • Error Information

Transaction Implementation

  • Transactions and Database Engine
  • Controlling Transactions 

Optimizing Query Performance

  • Query Performance Factors
  • Presenting Query Performance Data

SQL Server Metadata Querying

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

20462: Administering Microsoft SQL Server 2014 Databases

SQL Server 2014 Database Administration Intro

  • Database Administration Intro
  • SQL Server Platform
  • Tools and Techniques

SQL Server 2014 Installation and Configuration

  • Planning SQL Server Installation
  • Installation
  • Post-Install Configuration

Databases and Storage

  • Data Storage with SQL Server
  • Storage for System Databases
  • Storage for User Databases
  • Moving Database Files
  • Buffer Pool Extension Configuration

Planning and Implementing Backup Strategies

  • SQL Server Recovery Models
  • Backup Strategies
  • Backing up Databases and Transaction Logs
  • Backup Options
  • Backup Reliability

SQL Server 2014 Database Restoration

  • Restore Process
  • Restoring Databases
  • Advanced Restore Cases
  • Point-in-Time Recovery

Data Importing and Exporting

  • Transferring Data
  • Importing and Exporting Table Data
  • Copying or Moving Databases

Monitoring SQL Server 2014

  • Monitoring SQL Server
  • Dynamic Management Views and Functions
  • Performance Monitor

SQL Server Activity Tracing

  • Tracing Workload Activity
  • Using Traces

SQL Server Security Management

  • SQL Server Security
  • Server-Level Security
  • Database-Level Principals
  • Database Permissions

Data Access Auditing and Encryption

  • Auditing Data Access
  • SQL Server Audit Implementation

Performing Ongoing Database Maintenance

  • Database Integrity
  • Indexes Maintenance
  • Automating Routine Maintenance

Automating Management

  • SQL Server Management Automation
  • SQL Server Agent Jobs Implementation
  • SQL Server Agent Jobs Management
  • Job Step Security Contexts
  • Jobs on Multiple Servers

Alerts and Notifications for Monitoring

  • SQL Server Errors
  • Database Mail Configuration
  • Operators, Alerts, and Notifications Configuration

20463: Implementing a Data Warehouse with Microsoft SQL Server 2014

Data Warehousing Intro

  • Data Warehousing
  • Data Warehouse Solutions 

Data Warehouse Hardware Considerations 

  • Considerations for Builiding a Dat Warehouse 
  • Reference Architectures and Appliances 

Data Warehouse Design and Implementation

  • Logical Design 
  • Physical Design 

ETL Solutions

  • ETL with SSIS
  • Data Sources
  • Data Flow Implementation

Control Flow Implementation with SSIS Package

  • Control Flow
  • Building Dynamic Packages
  • Containers
  • Consistency Management

SSIS Packages Debugging and Troubleshooting

  • Debugging
  • Logging Events
  • Error Handling

Data Extraction Solution Implementation

  • Creating Plans for Data Extraction
  • Extracting Changed Data

Loading Data

  • Planning Data Loads
  • SSIS for Incremental Loads
  • Transact-SQL Loading Methods

Data Quality Enforcement

  • Data Quality
  • Cleansing Data with Data Quality Services
  • Matching Data with Data Quality Services 

Master Data Services

  • Master Data Services Intro
  • Master Data Services Model Implementation
  • Master Data Management
  • Building a Master Data Hub

Extending SQL Server Integration Services 

  • Scripts in SSIS
  • Custom Components in SSIS

SSIS Package Deployment and Configuration

  • SSIS Deployment
  • SSIS Project Deployment
  • SSIS Package Execution

Data Consumption

  • Business Intelligence Intro
  • Enterprise Business Intelligence
  • Big Data and Self-Service BI

Class Exam

Exam 70-461: Querying Microsoft SQL Server 2012/2014 Objectives:

  • Create database objects (20–25%)
  • Work with data (25–30%)
  • Modify data (20–25%)
  • Troubleshoot and optimize (25–30%)

Exam 70-462: Administering Microsoft SQL Server 2012/2014 Databases Objectives:

  • Install and configure (20–25%)
  • Maintain instances and databases (15–20%)
  • Optimize and troubleshoot (15–20%)
  • Manage data (20–25%)
  • Implement security (15–20%)
  • Implement high availability (5–10%)

Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 Objectives:

  • Design and implement a data warehouse (10–15%)
  • Extract and transform data (20–25%)
  • Load data (25–30%)
  • Configure and deploy SSIS solutions (20–25%)
  • Build data quality solutions (15–20%)

You can purchase the exam voucher separately through Phoenix TS.

Phoenix TS is an authorized testing center for Pearson VUE and Prometric websites. Register for exams by calling us or visiting the Pearson VUE and Prometric websites.