MCSA SQL Server Kursbundle (bestehend aus MOC 20461/20462/20463) inkl. 3 Examen

Programme

20461 Querying Microsoft SQL Server®

1 – Introduction to Microsoft SQL Server 2014
The Basic Architecture of SQL Server
SQL Server Editions and Versions
Getting Started with SQL Server Management Studio
Lab : Working with SQL Server 2014 Tools

2 – Introduction to T-SQL Querying
Introducing T-SQL
Understanding Sets
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab : Introduction to Transact-SQL Querying

3 – Writing SELECT Queries
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab : Writing Basic SELECT Statements

4 – Querying Multiple Tables
Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab : Querying Multiple Tables

5 – Sorting and Filtering Data
Sorting Data
Filtering Data with a WHERE Clause
Filtering with the TOP and OFFSET-FETCH Options
Working with Unknown and Missing Values
Lab : Sorting and Filtering Data

6 – Working with SQL Server 2014 Data Types
Introducing SQL Server 2014 Data Types
Working with Character Data
Working with Date and Time Data
Lab : Working with SQL Server 2014 Data Types

7 – Using DML to Modify Data
Inserting Data
Modifying and Deleting Data
Lab : Using DML to Modify Data

8 – Using Built-In Functions
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Lab : Using Built-In Functions

9 – Grouping and Aggregating Data
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab : Grouping and Aggregating Data

10 – Using Subqueries
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab : Using Subqueries

11 – Using Table Expressions
Using Derived Tables
Using Common Table Expressions
Using Views
Using Inline Table-Valued Functions
Lab : Using Table Expressions

12 – Using Set Operators
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab : Using Set Operators

13 – Using Window Ranking, Offset, and Aggregate Functions
Creating Windows with OVER
Exploring Window Functions
Lab : Using Window Ranking, Offset and Aggregate Functions

14 – Pivoting and Grouping Sets
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab : Pivoting and Grouping Sets

15 – Executing Stored Procedures
Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab : Executing Stored Procedures

16 – Programming with T-SQL
T-SQL Programming Elements
Controlling Program Flow
Lab : Programming with T-SQL

17 – Implementing Error Handling
Using TRY / CATCH Blocks
Working with Error Information
Lab : Implementing Error Handling

18 – Implementing Transactions
Transactions and the Database Engine
Controlling Transactions
Isolation Levels
Lab : Implementing Transactions

20462 Administering Microsoft® SQL Server® Databases

1 – Introduction to SQL Server 2014 Database Administration
Database Administration Overview
Introduction to the SQL Server Platform
Database Management Tools and Techniques
Lab: Using SQL Server Administrative Tools

2 – Installing and Configuring SQL Server 2014
Planning SQL Server Installation
Installing SQL Server 2014
Post-Installation Configuration
Lab: Installing SQL Server 2014

3 – Working with Databases and Storage
Introduction to Data Storage with SQL Server
Managing Storage for System Databases
Managing Storage for User Databases
Moving Database Files
Configuring the Buffer Pool Extension
Lab: Managing Database Storage

4 – Planning and Implementing a Backup Strategy
Understanding SQL Server Recovery Models
Planning a Backup Strategy
Backing up Databases and Transaction Logs
Using Backup Options
Ensuring Backup Reliability
Lab: Backing Up SQL Server Databases

5 – Restoring SQL Server 2014 Databases
Understanding the Restore Process
Restoring Databases
Advanced Restore Scenarios
Working with Point-in-Time Recovery
Lab: Restoring SQL Server Databases

6 – Importing and Exporting Data
Introduction to Transferring Data
Importing and Exporting Table Data
Copying or Moving a Database
Lab: Importing and Exporting Data

7 – Monitoring SQL Server 2014
Introduction to Monitoring SQL Server
Dynamic Management Views and Functions
Performance Monitor
Lab: Monitoring SQL Server 2014

8 – Tracing SQL Server Activity
Tracing SQL Server Workload Activity
Using Traces
Lab: Tracing SQL Server Workload Activity

9 – Managing SQL Server Security
Introduction to SQL Server Security
Managing Server-Level Security
Managing Database-Level Principals
Managing Database Permissions
Lab: Managing SQL Server Security

10 – Auditing Data Access and Encrypting Data
Auditing Data Access in SQL Server
Implementing SQL Server Audit
Encrypting Databases
Lab: Auditing Data Access and Encrypting Data

11 – Performing Ongoing Database Maintenance
Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance
Lab: Performing Ongoing Database Maintenance

12 – Automating SQL Server 2014 Management
Automating SQL Server Management
Implementing SQL Server Agent Jobs
Managing SQL Server Agent Jobs
Managing Job Step Security Contexts
Managing Jobs on Multiple Servers
Lab: Automating SQL Server Management

13 – Monitoring SQL Server 2014 by Using Alerts and Notifications
Monitoring SQL Server Errors
Configuring Database Mail
Configuring Operators, Alerts, and Notifications
Lab: Monitoring SQL Server by Using Alerts and Notifications

20463 Implementing a Data Warehouse with Microsoft® SQL Server®

1 – Introduction to Data Warehousing
Overview of Data Warehousing
Considerations for a Data Warehouse Solution
Lab : Exploring a Data Warehousing Solution

2 – Planning Data Warehouse Infrastructure
Considerations for Data Warehouse Infrastructure
Planning Data Warehouse Hardware
Lab : Planning Data Warehouse Infrastructure

3 – Designing and Implementing a Data Warehouse
Data Warehouse Design Overview
Designing Dimension Tables
Designing Fact Tables
Physical Design for a Data Warehouse
Lab : Implementing a Data Warehouse

4 – Creating an ETL Solution with SSIS
Introduction to ETL with SSIS
Exploring Data Sources
Implementing Data Flow
Lab : Implementing Data Flow in an SSIS Package

5 – Implementing Control Flow in an SSIS Package
Introduction to Control Flow
Creating Dynamic Packages
Using Containers
Managing Consistency
Lab : Implementing Control Flow in an SSIS Package
Lab : Using Transactions and Checkpoints

6 – Debugging and Troubleshooting SSIS Packages
Debugging an SSIS Package
Logging SSIS Package Events
Handling Errors in an SSIS Package
Lab : Debugging and Troubleshooting an SSIS Package

7 – Implementing a Data Extraction Solution
Planning Data Extraction
Extracting Modified Data
Lab : Extracting Modified Data

8 – Loading Data into a Data Warehouse
Planning Data Loads
Using SSIS for Incremental Loads
Using Transact-SQL Loading Techniques
Lab : Loading a Data Warehouse

9 – Enforcing Data Quality
Introduction to Data Quality
Using Data Quality Services to Cleanse Data
Using Data Quality Services to Cleanse Data
Lab : Cleansing Data

10 – Master Data Services
Introduction to Master Data Services
Implementing a Master Data Services Model
Managing Master Data
Creating a Master Data Hub
Lab : Implementing Master Data Services

11 – Extending SQL Server Integration Services
Using Scripts in SSIS
Using Custom Components in SSIS
Lab : Using Custom Scripts

12 – Deploying and Configuring SSIS Packages
Overview of SSIS Deployment
Deploying SSIS Projects
Planning SSIS Package Execution
Lab : Deploying and Configuring SSIS Packages

13 – Consuming Data in a Data Warehouse
Introduction to Business Intelligence
Enterprise Business Intelligence
Self-Service BI and Big Data
Lab : Using a Data Warehouse

Description

20461 Querying Microsoft SQL Server®

Nach dem Kurs sind Sie in der Lage,

  • SELECT-Abfragen zu schreiben
  • mehrere Tabellen abzufragen
  • Daten zu sortieren und zu filtern
  • die Nutzung unterschiedlicher Datentypen in SQL Server zu beschreiben
  • mit Transact-SQL Daten zu modifizieren
  • «built-in functions» zu verwenden
  • Daten zu gruppieren und zu aggregieren
  • Sub-Queries zu verwenden
  • Table Expressions zu verwenden
  • Set-Operatoren zu verwenden
  • Window-Ranking-, Offset- und Aggregierungs-Funktionen zu verwenden
  • Pivoting und Grouping Sets zu verwenden
  • Stored Procedures auszuführen
  • mit T-SQL zu pogrammieren
  • Error Handling zu implementieren
    •Transactions zu implementieren

20462 Administering Microsoft® SQL Server® Databases

Nach dem Besuch des Kurses sind Sie in der Lage,

  • die Kern-Aufgaben und -Tools zur Administration einer Datenbank zu beschreiben
  • SQL Server 2014 zu installieren und zu konfigurieren
  • SQL-Server-Datenbanken und -Storage zu konfigurieren
  • eine Backup-Strategie zu planen und zu implementieren
  • Datenbanken aus Backups wiederherzustellen
  • Daten zu importieren und zu exportieren
  • SQL Server zu überwachen
  • SQL-Server-Aktivitäten zu verfolgen
  • SQL Server Security zu verwalten
  • Datenzugriff und Datenverschlüselung zu kennen
  • eine laufende Datenbank-Wartung und Optimierungen vorzunehmen
  • SQL-Server-Wartung mit SQL Server Agent Jobs zu automatisieren
  • Datenbank-Mail, -Alerts und -Notifications zu konfigurieren

20463 Implementing a Data Warehouse with Microsoft® SQL Server®

Nach Besuch des Kurses sind Sie in der Lage:

  • Data-Warehouse-Konzepte und Architekturüberlegungen zu beschreiben;
  • Passende Hardware-Plattformen für ein Data Warehouse auszuwählen;
  • Win Data Warehouse zu entwerfen und zu implementieren;
  • Data Flow und Control Flow in einem SSIS Package zu implementieren;
  • SSIS Packages zu debuggen und zu troubleshooten;
  • Eine ETL Lösung zu implementieren, welche inkrementelle Daten Extraktion sowie Daten Load unterstützt;
  • •Datensäuberung mit Hilfe von Microsoft Data Quality Services zu implementieren;
  • Master Data Services zu implementieren, um Datenintegrität zu erzeugen;
  • SISS um benutzerdefinierte Scripts und Komponente zu erweitern;
  • SSIS Packages einzusetzen und zu konfigurieren;
  • Zu beschreiben, wie BI Lösungen Daten aus dem Data Warehouse beziehen können.

Sign up for our digitalswitzerland newsletter: