What is the Microsoft (MS) SQL Server?

Why we use MS SQL?
What is a database table?
Key factors to create database/tables?
The syntax for creating a database?
Database files and its usage?
Datatypes and its usage in SQL Server tables?
The syntax for creating tables?
Databases - Relational and Others
The Database as part of the System Architecture
SQL Server Versions and Editions
SQL Server Services and Components
SQL Server Client Tools
Introducing the Transaction Log
Database Snapshots
Schemas and Database Objects
Practice

Querying Databases

What are DDL, DML statements?
SQL Server wildcards?
Single and Multi-rows insertion?
What are filters and why we need to use them?
Table/Column Aliases & Usage?
Delete v/s Truncate?
SQL Server Operators - Its Types and Usage?

SQL Server Operators

Arithmetic Operators
Range Operators
Logical Operators
List Operators
Comparison Operators

Integrity, Constraints, CASE Statements, and Case Studies Case statement and Case Study

Data Integrity And Normal Forms (BCNF)
Foreign Key And Reference Attributes
Tables With Keys & Constraints (BCNF)
Check And Default Constraints Usage
Null And Identity Properties
Usage Duplicating Identity Property Values
Unique Key Constraint And Not Null
Disabling Constraints & Composite Keys
Primary Key Constraint & Importance

Joins, Sub Queries, and Nested Queries

SQL JOIN - What are Joins and why we need them?
INNER JOIN - Purpose and Performance?
OUTER JOIN - Types, Advantages, and Usage?
CROSS JOIN - Advantages and Limitations?
Self Joins, Merge Joins and Sub Queries?
SELECT Statements - IF, SWITCH, CHOOSE?
Sub Queries, Nested Queries and EXISTS?
DELETE from SELECT, UPDATE from SELECT?
INSERT INTO ... SELECT & Data Copy
All together with Case Studies

Joins, Views and Nested Queries

SQL Server Views Introduction?
What are Schemas?
SQL Server Views Types?
What are Schema Binding and its usage in SQL Server Views
What is the difference between User Views and System Views?
Check option in SQL Server

Indexes and Query Tuning

SQL Indexes - What are the Indexes?
SQL Indexes Types - Indexes Types?
SQL Indexes Usage - Purpose and Performance?
SQL Column Store Index - Syntax and Usage?
Performance Comparison Of Column Store V/S Clustered

Indexes and Query Tuning

Stored Procedure
What is SP'S and why we need them?
Stored Procedure Types and Parameter Stored Procedure?
Nested Stored Procedure?
Stored Procedure Usage For Insertion, Updating and Deleting?
Error Handling in Stored Procedure?
Stored Procedure With Out Parameter?

System Functions, User Defined Functions and Joins

What are SQL Functions?
Different types of SQL Functions?
What are the System Functions?
What are different types of System Functions and Its Usage with examples?
What are User Defined Functions and Its Usage with examples?
What are the different types of User Defined Functions?
What is the difference between Stored Procedure and User Defined Functions?

Triggers and Memory Limitations

SQL Triggers - What are the Triggers?
SQL Magic Tables?
SQL Triggers Types - Triggers Types?
SQL After Trigger Usage - Purpose?/li>
SQL Instead of Trigger Usage - Purpose?

Cursors, Stored Procedures, CTE

What are cursors
Different types of SQL cursors
Detail discussion about cursor types
What is CTE and Recursive CTE
What is Dynamic SQL with examples
Difference between Temp tables and Table variables
Difference between CTE and Temp Tables
What are Pivot and Unpivot

Transactions Process

What are the Transactions?
Advantages of Transactions?
What is SQL Server Merge?
What are SQL Injections?
What are SQL Keys, Rank, Locks, and Isolation Levels?

SSIS Architecture and DW Concepts

Need for SSIS for ETL Operations
Data Pipelines and Buffer Usage
Data Warehouse Design and SSIS
SIS Data Types and Conversions
DW Components and SSIS Tools
SSIS Local & Global Variables
SSIS Configuration and Catalog DB
Dynamic Precedence Constraints
Control Flow Tasks Architecture
Dynamic Connection Managers
Data Flow Tasks Architecture

SSIS Expressions & Debugging

SSIS Parameters Usage and Variable?
Control Flow Breakpoints?
Data Flow Data Viewers and Audit

Data Flow Transformations

Transformations in SSIS?
Different types of SSIS Transformations?
Case Study About Transformation
Common V/S Other Transform

Deployment Procedures

Deployment Versioning & Options >SSIS Deployment Phases & Manifest Files
Project Packaging > Validation & Verification Techniques
SSIS Checkpoints and Usage > Data Analytics and Debugging > Data Profiler & ADO Connections

Event Logging and Event HandlingConfigurations and benefits

Package Event Handling
System Events and Audits
SQL Server Logging Limitations

SSIS Package Security and Upgrades

Encrypting SSIS Catalogs
Package Store and SSISDB
Comparing SSIS 2008 R2 & 2012 & 2014

Sample Project

Project Business Requirement Document > Analysis of requirement
Databases Design/Schema Design
Kick off ETL
Business Validations
Business Transformations
Deployment in different environments like QA, Staging, and Production

Reporting Services Architecture

Introduction to Reporting Services
SSRS 2012 and Web Service URLs
Three-Phase Report Life Cycle
SSRS 2012 Configuration & Ports Reporting Engine Architecture

Report Design with Data Sets & Aggregates

Report Designer Usage and Scope
Enterprise and Ad-hoc Reports
Report Builder Usage and Scope
Working with multiple Datasets
Report Manager and Options
Configuring Dynamic DataSets
Report Delivery & Layout Planning

Sub Reports - Parameters & Filters

Create a New Project in SSRS 2014
Shared Data Source, Shared Dataset, Report
Embedded Data Source Embedded Data Sets
SSRS Table Report
Creating SSRS report using Wizard
How to add Groups in SSRS reports

Sub Reports - Parameters & Filters

Format Fonts and Background Color of a Textbox in SSRS
Format Numbers in SSRS 2014
Repeat Headers on Each Page in SSRS
Report Models and parameters
Keep Headers Visible While Scrolling in SSRS
Filters at Tablix Level in SSRS
Report Designer Vs Report Builder
Report Server Configurations Tools
Report Migrations and Precautions
Cascading Parameters in SSRS 2014
Drill down reports in SSRS 2014

SSRS Filers And Sorting Techniques

Using Report Builder Wizards
Map Reports with UI and Site Settings
Report Parts and Web Services

Delivering SSRS Reports Usint Parameters

Report Parameters in SSRS 2014
Multi-Value Parameters in SSRS 2014
Multiple Parameters in SSRS 2014

Managing Reports Using Procedures

How to pass stored procedures in SSRS 2014
SSRS Matrix Reports
SSRS Matrix Reports Using Wizard
Grouping in SSRS Matrix Reports
Drill down in SSRS Matrix Reports
Column Charts In SSRS

SSRS Charts Techniques

Pie charts in SSRS 2014
Indicators in SSRS 2014
Formatting the Pie Chart
Gauges in SSRS 2014
Data bars in SSRS 2014

SSRS Report Deployment Process

Sub reports in SSRS 2014
Deployment process for SSRS 2014

Data warehouse Concepts

Data Warehousing - Overview
Data Warehouse Features
Types of Data Warehouse
Functions of Data Warehouse Tools and Utilities
Data Warehousing - Terminologies Metadata & Metadata Repository
Data Mart & Points to Remember About Data Marts
Data Warehousing - Delivery Process
Data Warehouse Models
Data Warehousing - Schemas

SSAS Concepts

SSAS Data Source
STAR SCHEMA in SSAS EXAMPLE
SSAS Data Source View
SNOWFLAKE SCHEMA in SSAS
Add or Remove Tables from Data
EXAMPLE Source View in SSAS
Create Dimension in SSAS
Star schema and Snowflake Schema
Create OLAP Cube in SSAS in SSAS 2012
Sales Projects