• United India colony ,Kodambakkam, Chennai.
  • +91 - 99406 11306     044-24731102/ 24732532
  • training@rjpinfotek.com

MCSA SQL Server

Duration :120 Hrs

1. Course 10774A: Querying Microsoft SQL Server 2012

Course Outline

Module 1: Introduction to Microsoft SQL Server 2012

  1. Introducing Microsoft SQL Server 2012
  2. Getting Started with SQL Server Management Studio

Module 2: Getting Started with SQL Azure

  1. Overview of SQL Azure
  2. Working with SQL Azure

Module 3: Introduction to T-SQL Querying

  1. Introducing T-SQL
  2. Understanding Sets
  3. Understanding Predicate Logic
  4. Understanding the Logical Order of Operations in SELECT statements

Module 4: Writing SELECT Queries

  1. Writing Simple SELECT Statements
  2. Eliminating Duplicates with DISTINCT
  3. Using Column and Table Aliases
  4. Writing Simple CASE Expressions

Module 5: Querying Multiple Tables

  1. Understanding Joins
  2. Querying with Inner Joins
  3. Querying with Outer Joins
  4. Querying with Cross Joins and Self Joins

Module 6: Sorting and Filtering Data

  1. Sorting Data
  2. Filtering Data with a WHERE Clause
  3. Filtering with the TOP and OFFSET-FETCH Options
  4. Working with Unknown and Missing Values

Module 7: Working with SQL Server 2012 Data Types

  1. Introducing SQL Server 2012 Data Types
  2. Working with Character Data
  3. Working with Date and Time Data

Module 8: Using Built-In Functions

  1. Writing Queries with Built-In Functions
  2. Using Conversion Functions
  3. Using Logical Functions
  4. Using Functions to Work with NULL

Module 9: Grouping and Aggregating Data

  1. Using Aggregate Functions
  2. Using the GROUP BY Clause
  3. Filtering Groups with HAVING

Module 10: Using Subqueries

  1. Writing Self-Contained Subqueries
  2. Writing Correlated Subqueries
  3. Using the EXISTS Predicate with Subqueries

Module 11: Using Table Expressions

  1. Using Derived Tables
  2. Using Common Table Expressions
  3. Using Views
  4. Using Inline Table-Valued Functions

Module 12: Using Set Operators

  1. Writing Queries with the UNION Operator
  2. Using EXCEPT and INTERSECT
  3. Using APPLY

Module 13: Using Window Ranking, Offset and Aggregate Functions

  1. Creating Windows with OVER
  2. Exploring Window Functions

Module 14: Pivoting and Grouping Sets

  1. Writing Queries with PIVOT and UNPIVOT
  2. Working with Grouping Sets

Module 15: Querying SQL Server Metadata

  1. Querying System Catalog Views and Functions
  2. Executing System Stored Procedures
  3. Querying Dynamic Management Objects

Module 16: Executing Stored Procedures

  1. Querying Data with Stored Procedures
  2. Passing Parameters to Stored Procedures
  3. Creating Simple Stored Procedures
  4. Working with Dynamic SQL

Module 17: Programming with T-SQL

  1. T-SQL Programming Elements
  2. Controlling Program Flow

Module 18: Implementing Error Handling

  1. Using TRY / CATCH Blocks
  2. Working with Error Information

Module 19: Implementing Transactions

  1. Transactions and the Database Engine
  2. Controlling Transactions

Module 20: Improving Query Performance

  1. Factors in Query Performance
  2. Displaying Query Performance Data

2. Course 10775A: Administering Microsoft SQL Server 2012 Databases

Course Outline
Module 1: Introduction to SQL Server 2012 and its Toolset

  1. Introduction to the SQL Server Platform
  2. Working with SQL Server Tools
  3. Configuring SQL Server Services

Module 2: Preparing Systems for SQL Server 2012

  1. Overview of SQL Server Architecture
  2. Planning Server Resource Requirements
  3. Pre-installation Testing for SQL Server

Module 3: Installing and Configuring SQL Server 2012

  1. Preparing to Install SQL Server
  2. Installing SQL Server
  3. Upgrading and Automating Installation

Module 4: Working with Databases

  1. Overview of SQL Server Databases
  2. Working with Files and Filegroups
  3. Moving Database Files

Module 5: Understanding SQL Server 2012 Recovery Models

  1. Backup Strategies
  2. Understanding SQL Server Transaction Logging
  3. Planning a SQL Server Backup Strategy

Module 6: Backup of SQL Server 2012 Databases

  1. Backing up Databases and Transaction Logs
  2. Managing Database Backups
  3. Working with Backup Options

Module 7: Restoring SQL Server 2012 Databases

  1. Understanding the Restore Process
  2. Restoring Databases
  3. Working with Point-in-time recovery
  4. Restoring System Databases and Individual Files

Module 8: Importing and Exporting Data

  1. Transferring Data To/From SQL Server
  2. Importing and Exporting Table Data
  3. Inserting Data in Bulk

Module 9: Authenticating and Authorizing Users

  1. Authenticating Connections to SQL Server
  2. Authorizing Logins to Access Databases
  3. Authorization Across Servers

Module 10: Assigning Server and Database

  1. Working with Server Roles
  2. Working with Fixed Database Roles
  3. Creating User-defined Database Roles

Module 11: Authorizing Users to Access Resources

  1. Authorizing User Access to Objects
  2. Authorizing Users to Execute Code
  3. Configuring Permissions at the Schema Level

Module 12: Auditing SQL Server Environments

  1. Options for Auditing Data Access in SQL
  2. Implementing SQL Server Audit
  3. Managing SQL Server Audit

Module 13: Automating SQL Server 2012 Management

  1. Automating SQL Server Management
  2. Working with SQL Server Agent
  3. Managing SQL Server Agent Jobs

Module 14: Configuring Security for SQL Server Agent

  1. Understanding SQL Server Agent Security
  2. Configuring Credentials
  3. Configuring Proxy Accounts

Module 15: Monitoring SQL Server 2012 with Alerts and Notifications

  1. Configuration of Database Mail
  2. Monitoring SQL Server Errors
  3. Configuring Operators, Alerts and Notifications

Module 16: Performing Ongoing Database Maintenance

  1. Ensuring Database Integrity
  2. Maintaining Indexes
  3. Automating Routine Database Maintenance

Module 17: Tracing Access to SQL Server

  1. Capturing Activity using SQL Server Profiler
  2. Improving Performance with the Database Engine Tuning Advisor
  3. Working with Tracing Options

Module 18: Monitoring SQL Server

  1. Monitoring Activity
  2. Capturing and Managing Performance Data
  3. Analyzing Collected Performance Data

Module 19: Managing Multiple

  1. Working with Multiple Servers
  2. Virtualizing SQL Server
  3. Deploying and Upgrading Data-Tier Applications

Module 20: Troubleshooting Common SQL Server 2012 Administrative Issues

  1. SQL Server Troubleshooting Methodology
  2. Resolving Service-related Issues
  3. Resolving Concurrency Issues
  4. Resolving Login and Connectivity Issues

3. Course 10777A: Implementing a Data Warehouse with Microsoft SQL Server 2012

Course Outline
Module 1: Introduction to Data Warehousing

  1. Overview of Data Warehousing
  2. Considerations for a Data Warehouse Solution

Module 2: Data Warehouse Hardware

  1. Considerations for Building a Data Warehouse
  2. Data Warehouse Reference Architectures and Appliances

Module 3: Designing and Implementing a Data Warehouse

  1. Logical Design for a Data Warehouse
  2. Physical Design for a Data Warehouse

Module 4: Creating an ETL Solution with SSIS

  1. Introduction to ETL with SSIS
  2. Exploring Source Data
  3. Implementing Data Flow

Module 5: Implementing Control Flow in an SSIS Package

  1. Introduction to Control Flow
  2. Creating Dynamic Packages
  3. Using Containers
  4. Managing Consistency

Module 6: Debugging and Troubleshooting SSIS Packages

  1. Debugging an SSIS Package
  2. Logging SSIS Package Events
  3. Handling Errors in an SSIS Package

Module 7: Implementing an Incremental ETL Process

  1. Introduction to Incremental ETL
  2. Extracting Modified Data
  3. Loading Modified Data

Module 8: Incorporating Data from the Cloud into a Data Warehouse

  1. Overview of Cloud Data Sources
  2. SQL Server Database
  3. The Windows Azure Marketplace

Module 9: Enforcing Data Quality

  1. Introduction to Data Quality
  2. Using Data Quality Services to Cleanse Data
  3. Using Data Quality Services to Match Data

Module 10: Using Master Data Services

  1. Introduction to Master Data Services
  2. Implementing a Master Data Services Model
  3. Using the Master Data Services Add-in for Excel

Module 11: Extending SQL Server Integration Services

  1. Using Custom Components in SSIS
  2. Using Scripts in SSIS

Module 12: Deploying and Configuring SSIS Packages

  1. Overview of SSIS Deployment
  2. Deploying SSIS Projects
  3. Planning SSIS Package Execution

Module 13: Consuming Data in a Data Warehouse

  1. Introduction to Business Intelligence
  2. Introduction to Reporting
  3. Introduction to Data Analysis

In-Demand Courses

x