Certification In SQL Server

Overview:

Microsoft SQL Server is one the most popular Relational Database Management System (RDBMS) used in Microsoft universe. It can be used for data storage as well as for data retrieval for applications which can be either on desktop or Web/Browse.

Learn about SQL

Structured Query Language  Build database using Data Definition Language Statements Perform basic CRUD operations using Data Manipulation Language statements like Insert, Update and Delete Write and call Stored Procedures and Functions stored in database. Write and manage database triggers, cursors and Index.

Pre-requisite / Target Audience:

This sql server course can be taken by any beginner who wants to build career in Information Technology. The subscriber needs to have working knowledge of Windows Operating System.

Introduction to Basic Database Concepts

E-R Modeling and Diagram

  • What is Data, Field, Record and database?
  • Limitations of File Management System.
  • Basic Concepts of Advantages of DBMS.
  • Exploring Relational DBMS
  • Understanding Client and Server
  • Analyzing the Requirement v Identify Entities and their Relationships
  • Drawing E-R Diagram
  • Conversion of E.R. Diagrams into Tables

Normalization

Introduction to SQL Server

  • First Normal Form
  • Second Normal Form
  • Third Normal Form Practically Normalizing Tables
  • What is SQL Server Version history and different editions
  • Basic Features Components and Tools v Starting and Stopping SQL Server Instances / Services
  • Introduction to Management Studio
  • Types of System Databases in SQL

Introduction to SQL

DDL and DML Statements

  • Basics of SQL Types of SQL Statements
  • DDL, DML, DQL, DCL and TCL
  • Create Database using Management Studio
  • Datatypes in SQL Server
  • Exploring DDL Statements on Table using Management Studio
  • Why write statements in Frontends?
  • Create, Alter and Drop Table Insert,
  • Update and Delete Statement Truncate Statement

Working with Queries (DQL)

Aggregate Functions

  • Understanding Select Statement
  • Usage of Top, Distinct, Null etc…keywords
  • Using String and Arithmetic Expressions
  • Exploring Where Clause with Operators
  • Using Advanced Operators
  • Sorting data using Order By clause
  • Working with basic of Sub Queries
  • Using functions in Queries
  • Using Predefined Functions
  • Count, Sum, Min, Max, Avg Group By and Having Clause
  • Using Group By with Rollup and Cube

Joins and Set Operations

Implementation of Data integrity

  • Introduction to Joins Cross Joins
  • Inner Join
  • Outer Join
  • Self Join
  • Co-related Sub Queries
  • Set Operations using Unions, Intersect and Except
  • Entity integrity
  • Domain integrity
  • Referential integrity

Types of constraints

Implementing Views

  • Working with Constraints
  • Unique
  • Not NULL
  • Primary Key
  • Default Check Foreign Key
  • Introduction & Advantages of Views
  • Creating, Altering, Dropping Views
  • Advance Options while Creating a View
  • SQL Server Catalogue Views

Data Control language (DCL)

Working with Indexes

  • Creating Users & Roles
  • Granting & Revoking of Roles & privileges
  • Managing using Management Studio
  • Introduction Clustered and Non Clustered Index
  • Creating and Dropping Indexes

Writing Transact-SQL (T-SQL)

Working with Stored Procedures and Functions

  • What is T-SQL?
  • Scripts and Batches Declaring Variables
  • Using Statements
  • Working with Temp tables
  • Error Handling
  • Using System Functions / Global Variables Using Dynamic SQL
  • Introduction to stored procedures
  • Benefits of Stored Procedures
  • Creating, Executing Modifying, Dropping
  • Input–Output and Optional Parameters
  • System defined SP’s and Functions.

User defined Functions

Implementing Triggers

Working with Cursors

  • Introduction to triggers
  • Constraints vs Triggers
  • Creating, Altering, Dropping triggers
  • for/after/instead of triggers v Using Rollback Tran
  • Creating Cursors
  • Cursors vs. Select
  • Types of cursors v Locks on cursors
  • Advantages of cursors

Transaction Control Language (TCL)

  • Introduction Transactions process
  • Types of transactions (Implicit, explicit)
  • Working with Locks, Types of locks