Mastering SQL Certification Training Bundle

Microsoft 70-461: Querying SQL Server 2012

Module 1

• 1.1 Introduction to SQL Server 2012
• 1.2 SQL Instances
• 1.3 Editions and Licensing
• 1.4 SQL Server Objects
• 1.5 System Databases
• 1.6 SQL Server Data Tools
• 1.7 Object Explorer
• 1.8 Object Scripting
• 1.9 Template Explorer
• 1.10 Query Tabs
• 1.11 Projects and Solutions
• 1.12 Customizing SSDT
• 1.13 Debugging T-SQL
• 1.14 SQLCMD
• 1.15 Exam Scenarios
• 1.16 Module Review

Module 2

• 2.1 T-SQL Overview
• 2.2 T-SQL Elements
• 2.3 Batch Separators
• 2.4 Line Terminator
• 2.5 Variables
• 2.6 Control Flow
• 2.7 Expressions
• 2.8 Functions
• 2.9 Predicates and Operators
• 2.10 SELECT
• 2.11 Column Aliasing
• 2.12 SELECT Execution
• 2.13 Exam Scenarios
• 2.14 Module Review

Module 3

• 3.1 SELECT Statements
• 3.2 Basic SELECT Statement
• 3.3 Specifying Columns
• 3.4 Derived Columns
• 3.5 Aliasing Columns
• 3.6 Calculated Columns
• 3.7 Ordering Results
• 3.8 Returning Distinct Results
• 3.9 Exam Scenarios
• 3.10 Module Review

Module 4

• 4.1 Filtering
• 4.2 WHERE Syntax
• 4.3 BETWEEN
• 4.4 WHERE Syntax IN
• 4.5 Filtering NULL Values
• 4.6 LIKE
• 4.7 Data Type Comparison
• 4.8 Filtering Data with WHERE
• 4.9 TOP
• 4.10 OFFSET-FETCH
• 4.11 Exam Scenarios
• 4.12 Module Review

Module 5

• 5.1 Join and Set Operators
• 5.2 Using Joins
• 5.3 Categories of Joins
• 5.4 INNER JOIN
• 5.5 OUTER JOIN
• 5.6 JOIN Demo
• 5.7 Set Operators
• 5.8 Set Operators Demo
• 5.9 Exam Scenarios
• 5.10 Module Review

Module 6

• 6.1 Subqueries and Table Expressions
• 6.2 Subqueries
• 6.3 Self Contained Subquery
• 6.4 Correlated Subquery
• 6.5 Subquery Demo
• 6.6 EXISTS
• 6.7 Common Table Expressions
• 6.8 Temporary Tables
• 6.9 Table Variables
• 6.10 Table Demo
• 6.11 Exam Scenarios
• 6.12 Module Review

Module 7

• 7.1 System Functions
• 7.2 Scalar Functions
• 7.3 ROWSET
• 7.4 System Function Demo
• 7.5 Aggregate Functions
• 7.6 DISTINCT
• 7.7 Aggregation Demo
• 7.8 GROUP BY
• 7.9 HAVING
• 7.10 Conversion Functions
• 7.11 Exam Scenarios
• 7.12 Module Review

Module 8

• 8.1 T-SQL Programming
• 8.2 Logical Functions
• 8.3 IIF, CHOOSE and CASE
• 8.4 Logical Function Demo
• 8.5 Using Views
• 8.6 Stored Procedures
• 8.7 Functions
• 8.8 Triggers
• 8.9 Views Demo
• 8.10 TRY CATCH
• 8.11 Exam Scenarios
• 8.12 Module Review

Module 9

• 9.1 Transactions
• 9.2 Introduction to Transactions
• 9.3 INSERT and UPDATE
• 9.4 DELETE and TRUNCATE
• 9.5 Transactions Demo
• 9.6 Managing Transactions
• 9.7 XACT_ABORT
• 9.8 Managing Transactions Demo
• 9.10 Exam Scenarios
• 9.11 Module Review

Module 10

• 10.1 Query Performance
• 10.2 Indexing
• 10.3 Index Strategies
• 10.4 Indexes Demo
• 10.5 Execution Plans
• 10.6 Statistics IO
• 10.7 Execution Plans Demo
• 10.8 Exam Scenarios
• 10.9 Module Review
• 10.10 Program Review

Microsoft 70-462: Administering SQL Server 2012 Databases

Module 1

• 1.1 Introduction to SQL Server 2012
• 1.2 SQL Instances
• 1.3 Editions and Licensing
• 1.4 SQL Server Objects
• 1.5 System Databases
• 1.6 SQL Server Data Tools
• 1.7 Object Explorer
• 1.8 Object Scripting
• 1.9 Template Explorer
• 1.10 Query Tabs
• 1.11 Projects and Solutions
• 1.12 Customizing SSDT
• 1.13 Debugging T-SQL
• 1.14 SQLCMD
• 1.15 Exam Scenarios
• 1.16 Module Review

Module 2

• 2.1 T-SQL Overview
• 2.2 T-SQL Elements
• 2.3 Batch Separators
• 2.4 Line Terminator
• 2.5 Variables
• 2.6 Control Flow
• 2.7 Expressions
• 2.8 Functions
• 2.9 Predicates and Operators
• 2.10 SELECT
• 2.11 Column Aliasing
• 2.12 SELECT Execution
• 2.13 Exam Scenarios
• 2.14 Module Review

Module 3

• 3.1 SELECT Statements
• 3.2 Basic SELECT Statement
• 3.3 Specifying Columns
• 3.4 Derived Columns
• 3.5 Aliasing Columns
• 3.6 Calculated Columns
• 3.7 Ordering Results
• 3.8 Returning Distinct Results
• 3.9 Exam Scenarios
• 3.10 Module Review

Module 4

• 4.1 Filtering
• 4.2 WHERE Syntax
• 4.3 BETWEEN
• 4.4 WHERE Syntax IN
• 4.5 Filtering NULL Values
• 4.6 LIKE
• 4.7 Data Type Comparison
• 4.8 Filtering Data with WHERE
• 4.9 TOP
• 4.10 OFFSET-FETCH
• 4.11 Exam Scenarios
• 4.12 Module Review

Module 5

• 5.1 Join and Set Operators
• 5.2 Using Joins
• 5.3 Categories of Joins
• 5.4 INNER JOIN
• 5.5 OUTER JOIN
• 5.6 JOIN Demo
• 5.7 Set Operators
• 5.8 Set Operators Demo
• 5.9 Exam Scenarios
• 5.10 Module Review

Module 6

• 6.1 Subqueries and Table Expressions
• 6.2 Subqueries
• 6.3 Self Contained Subquery
• 6.4 Correlated Subquery
• 6.5 Subquery Demo
• 6.6 EXISTS
• 6.7 Common Table Expressions
• 6.8 Temporary Tables
• 6.9 Table Variables
• 6.10 Table Demo
• 6.11 Exam Scenarios
• 6.12 Module Review

Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012

Module 1: Introduction to Data Warehouses

• 1.1 Overview of Data Warehouses
• 1.2 Examine Data Warehousing Concepts
• 1.3 Introduction to Data Analysis and Reporting Concepts

Module 2: Creating Dimensions and Changing Granularity of Dimensions

• 2.1 Create Dimensions
• 2.2 Change Granularity

Module 3: Creating Fact Tables and Column store Indexes

• 3.1 Create Fact Tables
• 3.2 Create Column store Indexes

Module 4: Implementing Data Warehouses in SQL Server 2012

• 4.1 Overview of Data Warehouse Components
• 4.2 Install Integration Services

Module 5: Working with Integration Services

• 5.1 Create an SSIS Project
  5.2 Add Packages to an SSIS Project

Module 6: Managing Control Flow

• 6.1 Deploy a Control Flow
• 6.2 Execute Packages from Different Control Flow

Module 7: Working with Dynamic Variables

• 7.1 Create Expressions with Variables
• 7.2 Create Package Templates

Module 8: Implementing Data Flow

• 8.1 Add Data Flow Components to a Data Flow Task
• 8.2 Perform Transformation Tasks
• 8.3 Track Changed Data

Module 9: Managing Data Flow

• 9.1 Add Script Tasks and Script Components to an SSIS Package
• 9.2 Profile OLTP Data
• 9.3 Cleanse Data Using DQS

Module 10: Managing SSIS Package Execution

• 10.1 Execute SSIS Packages on the Server
• 10.2 Configure the Package Execution Strategy

Module 11: Debugging and Troubleshooting Packages

• 11.1 Debug Package Execution Components
• 11.2 Troubleshoot Package Failures

Module 12: Deploying Packages

• 12.1 Deploy SSIS Packages
• 12.2 Examine MSI Packages

Module 13: Securing Packages and Databases

• 13.1 Secure Packages
• 13.2 Secure Databases

Module 14: Working with MDS and Windows Azure

• 14.1 Configure MDS
• 14.2 Work with MDS Databases
• 14.3 Work with Windows Azure

Microsoft 70-464: Developing Microsoft SQL Server 2012 Databases

Module 1: SQL 2012 And The 464 Test

• 1.1 Introduction
• 1.2 Intro to SQL Server 2012 Part 1
• 1.3 Intro to SQL Server 2012 Part 2
• 1.4 Management Studio Demo Part 1
• 1.5 Management Studio Demo Part 2
• 1.6 Management Studio Demo Part 3
• 1.7 Other SQL Server Tools

Module 2: Working with Data Types

• 2.1 Working with Data Types Part 1
• 2.2 Working with Data Types Part 2
• 2.3 Working with Data Types Part 3
• 2.4 Other Data Types

Module 3: Tables

• 3.1 Tables
• 3.2 Creating Tables

Module 4: Data Constraints

• 4.1 Data Constraints Part 1
• 4.2 Data Constraints Part 2

Module 5: Planning in Indexing

• 5.1 Planning in Indexing Part 1
• 5.2 Planning in Indexing Part 2
• 5.3 Planning in Indexing Part 3
• 5.4 Planning in Indexing Part 4

Module 6: Table Structures

• 6.1 Table Structures
• 6.2 Working with Clustered Indexes
• 6.3 Clustered Indexes
• 6.4 Designing Effective Clustered Indexes

Module 7: Improving Performance with Non-Clustered Indexes

• 7.1 Improving Performance with Non-Clustered Indexes Part 1
• 7.2 Improving Performance with Non-Clustered Indexes Part 2
• 7.3 Improving Performance with Non-Clustered Indexes Part 3

Module 8: Using Indexes and Execution Plans

• 8.1 Using Indexes
• 8.2 Execution Plans Part 1
• 8.3 Execution Plans Demo
• 8.4 Execution Plans Part 2

Module 9: Working with Views

• 9.1 Working with Views Part 1
• 9.2 Working with Views Part 2

Module 10: Designing and Implementing Stored Procedures

• 10.1 Designing and Implementing Stored Procedures Part 1
• 10.2 Designing and Implementing Stored Procedures Part 2
• 10.3 Designing and Implementing Stored Procedures Part 3

Module 11: Merging Data and Passing Tables

• 11.1 Merging Data and Passing Tables Part 1
• 11.2 Merging Data and Passing Tables Part 2
• 11.3 Merging Data and Passing Tables Part 3

Module 12: Designing and Implementing User Functions

• 12.1 Designing and Implementing User Functions

Module 13: Creating Highly Concurrent SQL Server Applications

• 13.1 Creating Highly Concurrent SQL Server Applications Part 1
• 13.2 Creating Highly Concurrent SQL Server Applications Part 2
• 13.3 Creating Highly Concurrent SQL Server Applications Part 3

Module 14: Handling Errors in TSQL

• 14.1 Handling Error in TSQL Part 1
• 14.2 Handling Error in TSQL Part 2

Module 15: Triggers

• 15.1 Triggers

Module 16: Implementing Managed Code

• 16.1 Implementing Managed Code Part 1
• 16.2 Implementing Managed Code Part 2
• 16.3 Implementing Managed Code Part 3

Module 17: Storing XML in SQL

• 17.1 Storing XML in SQL Part 1
• 17.2 Storing XML in SQL Part 2
• 17.3 Storing XML in SQL Part 3

Module 18: Querying XML

• 18.1 Querying XML Part 1
• 18.2 Querying XML Part 2
• 18.3 Querying XML Part 3

Module 19: Working with the Spatial Data

• 19.1 Working with Spatial Data Part 1
• 19.2 Working with Spatial Data Part 2
• 19.3 Working with Spatial Data Part 3

Module 20: Working with Full Text Indexes and Queries

• 20.1 Working with Full Text Indexes and Queries Part 1
• 20.2 Working with Full Text Indexes and Queries Part 2
• 20.3 Working with Full Text Indexes and Queries Part 3
• 20.4 Outro

Microsoft 70-465: Designing Database Solutions for Microsoft SQL Server 2012

Module 1: Designing A Database Server Infrastructure

• 1.1 Introduction
• 1.2 Designing A Database Server Infrastructure Part 1
• 1.3 Designing A Database Server Infrastructure Part 2
• 1.4 Designing A Database Server Infrastructure Part 3
• 1.5 Designing A Database Server Infrastructure Part 4
• 1.6 Considerations for Database Server Consolidation
• 1.7 Managing Server Resources

Module 2: Designing A Logical Database Scheme

• 2.1 Designing A Logical Database Scheme Part 1
• 2.2Designing A Logical Database Scheme Part 2
• 2.3 Designing A Logical Database Scheme Part 3
• 2.4 Designing A Logical Database Scheme Part 4
• 2.5 Designing A Logical Database Scheme Part 5

Module 3: Designing A Physical Database Implementation

• 3.1 Designing A Physical Database Implementation Part 1
• 3.2 Designing A Physical Database Implementation Part 2
• 3.3 Designing A Physical Database Implementation Part 3

Module 4: Incorporating Data Files into the Database

• 4.1 Incorporating Data Files into The Database Part 1
• 4.2 Incorporating Data Files into The Database Part 2
• 4.3 Incorporating Data Files into The Database Part 3
• 4.4 Incorporating Data Files into The Database Demo
• 4.5 Searching Data Files
• 4.6 Searching Data Files Demo

Module 5: Tuning Database Performance

• 5.1 Tuning Database Performance Part 1
• 5.2 Tuning Database Performance Part 2
• 5.3 Tuning Database Performance Part 3
• 5.4 Working with Query Plans Part 1
• 5.5 Working with Query Plans Part 2
• 5.6 Working with Query Plans Part 3
• 5.7 Working with Query Plans Part 4

Module 6: Designing Database Security

• 6.1 Designing Database Security Part 1
• 6.2 Designing Database Security Part 2
• 6.3 Contained Databases
• 6.4 Protecting Data with Encryption

Module 7: Policy Based Management

• 7.1 Policy Based Management
• 7.2 Policy Based Management Demo

Module 8: Monitoring Server Health

• 8.1 Monitoring Server Health
• 8.2 Monitoring Server Health Demo

Module 9: Designing Database Backup Solution

• 9.1 Designing Database Backup Solution Part 1
• 9.2 Designing Database Backup Solution Part 2
• 9.3 Designing Database Backup Solution Part 3
• 9.4 Designing Database Backup Solution Part 4
• 9.5 Disaster Recovery Plan Part 1
• 9.6 Disaster Recovery Plan Part 2

Module 10: Automating Multi Server Maintenance

• 10.1 Automating Multi Server Maintenance
• 10.2 Managing Multiple Servers

Module 11: Managing SQL Server with Windows PowerShell

• 11.1 Managing SQL Server With Windows PowerShell
• 11.2 Windows PowerShell Lab

Module 12: Replicating Data

• 12.1 Replicating Data Part 1
• 12.2 Replicating Data Part 2
• 12.3 Replicating Data Part 3

Microsoft 70-466: Implementing Data Models & Reports with SQL Server 2012

Module 1: Introduction to Business Intelligence and Data Modeling

• 1.1 Introduction to Business Intelligence and Data Modeling Part 1
• 1.2 Introduction to Business Intelligence and Data Modeling Part 2
• 1.3 The Microsoft Business Intelligence Platform Part 1
• 1.4 The Microsoft Business Intelligence Platform Part 2
• 1.5 The Microsoft Business Intelligence Platform Part 3
• 1.6 The Microsoft Business Intelligence Platform Part 4

Module 2: Implementing Reports The SQL Server Reporting Services

• 2.1 Implementing Reports the SQL Server Reporting Services Part 1
• 2.2 Implementing Reports the SQL Server Reporting Services Part 2
• 2.3 Implementing Reports the SQL Server Reporting Services Part 3
• 2.4 Implementing Reports the SQL Server Reporting Services Part 4
• 2.5 Implementing Reports the SQL Server Reporting Services Part 5
• 2.6 Implementing Reports the SQL Server Reporting Services Part 6
• 2.7 Implementing Reports the SQL Server Reporting Services Part 7
• 2.8 Implementing Reports the SQL Server Reporting Services Part 8

Module 3: Supporting Self Service Reporting

• 3.1 Supporting Self Service Reporting Part 1
• 3.2 Supporting Self Service Reporting Part 2

Module 4: Managing Report Execution and Delivery

• 4.1 Managing Report Execution and Delivery
• 4.2 Managing Report Execution
• 4.3 Subscriptions and Data Alerts

Module 5: Creating Multidimensional Databases

• 5.1 Creating Multidimensional Databases
• 5.2 Creating Data Sources and Data Source Views
• 5.3 Creating and Browsing a Cube

Module 6: Working with Cubes and Dimensions

• 6.1 Working with Cubes and Dimensions
• 6.2 Sorting and Grouping Attributes

Module 7: Working with Measures and Measure Groups

• 7.1 Working with Measures and Measure Groups

Module 8: Introduction to MDX

• 8.1 Introduction to MDX
• 8.2 Adding Calculations to A Cube

Module 9: Customizing Cube Functionality

• 9.1 Customizing Cube Functionality Part 1
• 9.2 Customizing Cube Functionality Part 2
• 9.3 Customizing Cube Functionality Part 3

Module 10: Implementing A Tabular Data Model

• 10.1 Implementing A Tabular Data Model Part 1
• 10.2 Implementing A Tabular Data Model Part 2

Module 11: Introduction to DAX

• 11.1 Introduction to DAX
• 11.2 Demonstration Making a Tabular Model Part 1
• 11.3 Demonstration Making a Tabular Model Part 2
• 11.4 Using DAX

Module 12: Implementing an Analysis Services Tabular Data Model

• 12.1 Implementing an Analysis Services Tabular Data Model Part 1
• 12.2 Implementing an Analysis Services Tabular Data Model Part 2
• 12.3 Deploying A Tabular Data Model

Module 13: SQL Analysis Services

• 13.1 SQL Analysis Services Part 1
• 13.2 SQL Analysis Services Part 2

Module 14: Creating Data Visualizations with Power View

• 14.1 Creating Data Visualizations with Power View

Module 15: Supporting Self Service Reporting

• 15.1 Supporting Self Service Reporting

Module 16: Performing Predictive Analysis with Data Mining

• 16.1 Performing Predictive Analysis with Data Mining
• 16.2 Using the Data Mining Wizard

Module 17: Predictive Analytics

• 17.1 Predictive Analytics Part 1
• 17.2 Predictive Analytics Part 2