20461: Querying Microsoft SQL Server

Overview

This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.

Duration:  40 hours

Course Outline

Module 1: Sorting and Filtering Data

Module 2: Working with SQL Server 2014 Data Types

Module 3: Introduction to T-SQL Querying

Module 4: Writing SELECT Queries

Module 5: Querying Multiple Tables

Module 6: Using Built-In Functions

Module 7: Using Subqueries

Module 8: Using Set Operators

Module 9: Querying data with Stored Procedures

Module 10: Programming with T-SQL

Module 11: Appendix 2: Querying SQL Server Metadata

Module 12: Using DML to Modify Data

Module 13: Grouping and Aggregating Data

Module 14: Introduction to Microsoft SQL Server 2014

Module 15: Using Table Expressions

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

Module 17: Pivoting and Grouping Sets

Module 18: Implementing Error Handling

Module 19: Implementing Transactions

Module 20: Appendix 1: Improving Query Performance

20462: Administering Microsoft SQL Server Databases

Overview

This course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2014 database. The course focuses on teaching individuals how to use SQL Server 2014 product features and tools related to maintaining a database.

Duration: 40 hours

Course Outline

Module 1: Importing and Exporting Data

Module 2: Monitoring SQL Server 2014

Module 3: Tracing SQL Server Activity

Module 4: Introduction to SQL Server 2014 Database Administration

Module 5: Installing and Configuring SQL Server 2014

Module 6: Working with Databases and Storage

Module 7: Planning and Implementing a Backup Strategy

Module 8: Restoring SQL Server 2014 Databases

Module 9: Managing SQL Server Security

Module 10: Auditing Data Access and Encrypting Data

Module 11: Performing Ongoing Database Maintenance

Module 12: Automating SQL Server 2014 Management

Module 13: Monitoring SQL Server 2014 by Using Alerts and Notifications

20463: Implementing a Data Warehouse with Microsoft SQL Server

Overview

This course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with Microsoft® SQL Server® 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

Duration: 40 hours

Course Outline

Module 1: Designing and Implementing a Data Warehouse

Module 2: Creating an ETL Solution with SSIS

Module 3: Implementing Control Flow in an SSIS Package

Module 4: Debugging and Troubleshooting SSIS Packages

Module 5: Implementing an Incremental ETL Process

Module 6: Using Master Data Services

Module 7: Extending SQL Server Integration Services

Module 8: Deploying and Configuring SSIS Packages

Module 9: Enforcing Data Quality

Module 10: Introduction to Data Warehousing

Module 11: Data Warehouse Hardware Considerations

Module 12: Consuming Data in a Data Warehouse