SQL Server 2014 Course Content

SQL Server 2014 Training Course Overview

SQL Server Training Course Prerequisite

• No Prior Experience is required

SQL Server Training Course Duration

• 40 Working days, daily one hour

SQL Server 2014 Training Course Overview

Introduction To DBMS

·         File Management System And Its Drawbacks

·         Database Management System (DBMS) and Data Models

·         Relationships in Sql Server

Introduction To SQL Server

·         Advantages and Drawbacks Of SQL Server Compared To Oracle And DB2

·         SQL Server Installation.

·         Connecting To Server

·         Server Type

·         Server Name

·         Authentication Modes

·         Sql Server Authentication Mode

·         Windows Authentication Mode

·         Login and Password

·         Sql Server Management Studio and Tools In Management Studio

·         Object Explorer

·         Object Explorer Details

·         Query Editor

TSQL (Transact Structured Query Language)
Introduction To TSQL

·         History and Features of TSQL

·         Types Of TSQL Commands

·         Data Definition Language (DDL)

·         Data Manipulation Language (DML)

·         Data Query Language (DQL)

·         Data Control Language (DCL)

·         Transaction Control Language (TCL)

Data Definition Language(DDL)

·         Database

·         Creating Database

·         Altering Database

·         Deleting Database

·         Constrains

·         Procedural Integrity Constraints

·         Declarative Integrity Constraints

·         Not Null, Unique, Default and Check constraints

·         Primary Key and Referential Integrity or foreign key constraints

·         Delete and update Rules in Foreign Key

1.     On update/delete no action

2.     On update/delete cascade

3.     On update/delete set null

4.     On update/delete set default

·         Data Types In TSQL

·         Table

·         Creating Table

·         Altering Table

·         Dropping Table

Data Manipulation Language(DML)

·         Insert

·         Identity

·         Creating A Table From Another Table

·         Inserting Rows From One Table To Another

·         Update

·         Computed Columns

·         Delete

·         Truncate

·         Differences Between Delete and Truncate

·         Merge Statement

Data Query Language (DQL)

·         Select

·         Where clause

·         Order By Clause

·         Distinct Keyword

·         Isnull() function

·         Column & table aliases

Operators:

·         Arithmetic operators

·         comparison operators

·         range operators

·         list operators

·         string /pattern matching operator

·         unknown value operators

·         logical operators

·         set operators

Built In Functions

·         Scalar Functions

·         Numeric Functions

·         Character Functions

·         Conversion Functions

·         Date Functions

Aggregate Functions

·         Convenient Aggregate Functions

·         Statistical Aggregate Functions

·         Group By and Having Clauses

·         Super Aggregates

·         Over(partition by …) Clause

·         Ranking Functions

v  Rank()

v  Dense_rank()

v  Row_Number()

v  Ntile(n)

Table Expressions

·         Derived tables

·         Common Table Expressions (CTE)

Top n Clause
Joins

·         Inner Join

·         Equip Join

·         Non-Equi Join

·         Self Join

·         Outer Join

·         Left Outer Join

·         Right Outer Join

·         Full Outer Join

·         Cross Join

Sub Queries

·         Single Row Sub Queries

·         Multi Row Sub Queries

·         Any or Some

·         ALL

·         Nested Sub Queries

·         Co-Related Sub Queries

·         Exists and Not Exists

Indexes

·         Clustered Index

·         NonClustered Index

·         Create , Alter and Drop Indexes

·         Creating indexed view

·         Using Indexes

Security

·         Login Creation

·         SQL Server Authenticated Login

·         Windows Authenticated Login

·         User Creation

·         Granting Permissions

·         Revoking Permissions

Schema

·         Creating a schema

·         Creating an object under schema

·         Alterring a schema

·         Droping a schema

·         Providing security to schemaa

Views

Purpose Of Views

·         Creating , Altering and Dropping Views

·         Simple and Complex Views

·         Updating(insert/delete/update)  the views

·         With check option

·         Encryption and Schema Binding Options in creating views

Transaction Management-(TCL)

 Introduction

·         Explicit Transactions

·         Begin Transaction

·         Commit Transaction

·         Rollback Transaction

·         Save Transaction

·         Implicit Transactions

TSQL Programming (like PL/SQL in Oracle)

·         Drawbacks Of TSQL that leads to TSQL Programming

·         Introduction To TSQL Programming

·         Control statements In TSQL Programming

·         Conditional Control Statements

·         If

·         Case

·         Looping Control Statements

·         While

Cursors

·         Working With Cursors

·         Types Of Cursors

·         Forward_Only and Scroll Cursors

·         Static, Dynamic and Keyset Cursors

·         Local and Global Cursors

·         Cursors with functions, procedures and triggers(will be dealt at the end)

Stored Sub Programs

·         Advantages Of Stored Sub Programs compared to Independent SQL Statements

·         Stored Procedures

·         Creating , Altering and Dropping

·         Optional Parameters

·         Input and Output Parameters

·         Permissions on Stored Procedures

·         User Defined Functions

·         Creating, Altering and Dropping

·         Types Of User Defined Functions

·         Scalar Functions

·         Table Valued Functions

·         Inline Table Valued Functions

·         Multi Statement Table Valued Functions

·         Permissions On User Defined Functions

·         Diff between fucntions and procedures

·         Triggers

·         Purpose of Triggers

·         Differences Between Stored Procedures and User Defined Functions and Triggers

·         Creating, Altering and Dropping Triggers

·         After Triggers

·         Magic Tables

·         Instead Of Triggers

·         Updating the complex view using instead of triggers

·         Exception Handling

·         Implementing Exception Handling

·         Try –catch mechanism

·         Adding and removing User Defined Error Messages To And From SQL Server Error Messages List

·         Raising Exceptions Manual

·         Generating Errors through throws key word

Normalization

·         First Normal Form

·         Second Normal Form

·         Third Normal Form

·         Boyce-Codd Normal Form

Backup and Restore Of Database
Attach and Detach of Database

Thanks

Srinivas
 9059361460
srinivas.r.salesforce@gmail.com