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
