SQL Server Course Over
view
This SQL Server training teaches developers all the Transact-SQL skills they need to create database objects like Tables, Views, Stored procedures & Functions and triggers in SQL Server. Gives idea about writing Queries & Sub-queries, working with Joins, etc. As well as database management skills like backup, restore, etc.
SQL Server Training Prerequisite
- No Prior Experience is Presumed.
SQL Server Training Course Objective
- Learn Database models
- Overview of SQL Server Management Studio and Transact-SQL language
- Master writing simple and complex queries that retrieve data from the database
- Calculate information across result sets using aggregate queries (sum, min, max, avg, etc.)
- Insert, update, and delete data
- Retrieve data from tables
- Joins
- Sub-queries
- Working with Data Types
- Procedure and Functions
- Understand the different Views
- Working with Triggers
- Design a database
- Maintain databases, tables, and sequences with SQL statements
- Create and manage views
- Ensure the integrity of multiple, related database updates by using transactions
- Retrieve data using cursors
- Manage binary data using BLOBs
SQL Server Course Duration
- 30 Working days, daily 1.30 hours
SQL Server Training Course Content
Introduction To DBMS
- File Management System And Its Drawbacks
- Database Management System (DBMS) and Data Models
- Physical Data Models
- Logical Data Models
- Hierarchical Data Model (HDBMS)
- Network Data Model (NDBMS)
- Relational Data Model (RDBMS)
- Object Data Model (ODBMS)
- Object Relational Data Model (ORDBMS)
- Conceptual Data Models
- Entity – Relationship (E-R) Model
Introduction To SQL Server
- Advantages and Drawbacks Of SQL Server Compared To Oracle And DB2
- 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
- Connecting To Server
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)
- 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
- Data Types In TSQL
- Table
- Creating Table
- Altering Table
- Deleting Table
Data Manipulation Language
- 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
Data Query Language (DQL)
- Select
- Where clause
- Order By Clause
- Distinct Keyword
- Isnull() function
- Column aliases
- Predicates
- Between … And
- In
- Like
- Is Null
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
- Common Table Expressions (CTE)
Top n Clause
Set Operators
- Union
- Intersect
- Except
Joins
- Inner Join
- Equi Join
- Natural 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
- Using Indexes
Security
- Login Creation
- SQL Server Authenticated Login
- Windows Authenticated Login
- User Creation
- Granting Permissions
- Revoking Permissions
- Roles
Views
- Purpose Of Views
- Creating , Altering and Dropping Indexes
- Simple and Complex Views
- Encryption and Schema Binding Options in creating views
Transaction Management
- Introduction
- Begin Transaction
- Commit Transaction
- Rollback Transaction
- Save Transaction
- Role Of Log File In Transaction Management
- Implicit Transactions
TSQL Programming
- Drawbacks Of TSQL that leads to TSQL Programming
- Introduction To TSQL Programming
- Control statements In TSQL Programming
- Conditional Control Statements
- If
- Case
- Conditional Control Statements
- 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
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
- Triggers
- Purpose of Triggers
- Differences Between Stored Procedures and User Defined Functions and Triggers
- Creating, Altering and Dropping Triggers
- Magic Tables
- Instead Of Triggers
- Exception Handling
- Implementing Exception Handling
- Adding and removing User Defined Error Messages To And From SQL Server Error Messages List
- Raising Exceptions Manual
CLR Integration
- What is CLR Integration and The Steps For Implementing It
- A Simple Example With CLR Integration