Course Content

Database

• DBMS
• RDBMS
• Oracle and SQL Server

SQL Server Environment and Introduction

• SQL Server Management Studio Enhancements in SSMS – 2008 and above
• SQL Server Business Intelligence Development Studio ( BIDS)

SQL Language

• DDL
• DML (INSERT, UPDATE, DELETE, MERGE)
• TCL
• DDL
• DCL

Data Integrity

• Domain Integrity
• Entity Integrity
• Referential Integrity

Database Design

• Normalization
• De Normalization
• Database Diagrams (E-R)

Joins and Sub Queries

• SQL Operators
• Simple Queries
• Built-in Functions
• Joins
• Sub-Queries
• Advanced Queries

Views

• Create View
• Advantage of Views
• Updatable Views
• Non-Updatable Views
• Indexed View

Introduction to T-SQL

• Anonymous Block
• Stored Block
• Anonymous block(including 2008 features)
• Structured Exception Handling

Cursors

• Declaring Cursors
• Modifications Thorough Cursors
• Types of Cursors
• Advantages of Cursors

Stored block

Stored Procedures

• System Stored Procedure
• User Stored Procedures
• Advantages of Procedures
• Passing In/Out Parameters
• Altering Procedure
• Extended Stored Procedures

User-Defined Functions

• Scalar UDF
• Inline UDF
• Multi Statement Table UDF
• Table Variables, Temporary Tables

Triggers

• DML Triggers (After | Instead of )
• DDL Triggers
• Magic Tables
• Advantages of Triggers

Transactions and Locks

• Implicit Transactions
• Explicit Transactions
• TCL Commands
• Lock Types
• Isolation Levels
• Dead Locks

Indexes

• Clustered Index
• Non-Clustered Index
• Creating Index
• Advantages of Indexes

XML Integration

• FOR XML
• OPEN XML
• XML Data Type

Security

• Windows Authentication
• SQL Server Authentication
• Creating Login
• Database Permissions
• Introduction to Roles, Schema
• Dropping Login

Database Maintenance

• Backup Database
• Restore Database
• Deployment of Database
• Generating T-SQL Scripts and Batches
• System Databases and System Tables

SQL Server Agent

• Jobs
• Schedules
• Alerts
• Operators

Data Transformation Services

• BCP
• DTS Packages / SSIS Packages

Introduction to DBA Concepts

• Replication
• Performance Monitoring
• Service Broker

Advanced Concepts:

• Error handling
• Pivoting
• Common Table Expressions
• Row number functions and their usage

SSIS Course:

• Walkthrough Tools & Transformations
• Walkthrough Folders & Containers
• LOOPS
• File Processing
• Various Connection Managers
• Error Handling
• Automation Techniques
• Best Practices