SQL Server Course Overview

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

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
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

Siring blogs