Microsoft Technology


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/Browser. As part of the training, you will manage the database solutions, manage various operations on databases and  will work on multiple hands-on exercises and projects.

It will be instructor-led online live training.

Duration: 50 Hours


Course Topics:

1. Introduction to Basic Database Concepts

             In this module, we will learn about Basic concepts and advantages of DBMS and limitations of file management system and about 3 data base models.

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

2. E-R Modeling and Diagram

             In this module, we will learn about entity, attributes and relationship, identify the entities and attributes How to draw a E-R diagram and translating the E-R diagram in relation schema.

Analyzing the Requirement

Identify Entities and their Relationships

Drawing E-R Diagram

Conversion of E.R. Diagrams into Tables

3. Normalization

              In this module, we will learn about what is normalization, types of normalization, data before and after normalization, benefits of normalization.

First Normal Form

Second Normal Form

Third Normal Form

4. Introduction to SQL Server

In this module, we will learn about SQL Server, history of SQL server, types of system databases, communication between frontend and backend and sql server editions.

What is SQL Server Version history and different editions?

Basic Features, Components and Tools

Starting and Stopping SQL Server Instances / Services

Introduction to Management Studio

Types of System Databases in SQL

5. Introduction to SQL

             In this module, we will learn about types of SQL statements, databases in SQL server, how to create a database, datatypes in SQL server and about DDL 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

6. DDL and DML Statements

              In this module, we will learn about how to create a table, alter and drop a table and about DML statements, like insert   update and delete statements.

Why write statements in Frontends?

Create, Alter and Drop Table Insert,

Update and Delete Statement Truncate Statement

7. Working with Queries (DQL)

            In this module, we will learn about select statement, top, distinct string and arithmetic expressions, Sorting the data and about sub queries and where clause(condition).

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

8. Aggregate Functions

            In this module, we will learn about how to use aggregate functions like sum, mean, max, avg, what is difference between having and where clause, group by clause rollup and cube operator.

Using functions in Queries

Count, Sum, Min, Max, Avg Group by and Having Clause

Using Group by with Rollup and Cube

9. Joins and Set Operations

            In this module, we will know about joins and types of joins how to join the tables and about Sub queries, types of operators like union, intersect and except and how to add the tables and relationship between them.

Introduction to Joins Cross Joins

Inner Join

Outer Join

Self Join

Co-related Sub Queries

Set Operations using Unions, Intersect and Except

10. Implementation of Data integrity

            In this module, we will learn correctness of data and types of integrity and types of constraints.

Entity integrity

Domain integrity

Referential integrity

Types of constraints

11. Working with Constraints

            In this module, we will learn about how to create a constraint, types of constraints, and difference between unique, not null and primary key constraints.

Unique

Not NULL

Primary Key

Default Check Foreign Key

12. Implementing Views

            In this module, how to create a view, advantages of views, altering and dropping a view and advanced options while creating a view.

Introduction & Advantages of Views

Creating, Altering, Dropping Views

Advance Options while Creating a View

SQL Server Catalogue Views

13. Data Control language (DCL)

              In this module, we will learn how to grant permission and revoking of roles.

Creating Users & Roles

Granting & Revoking of Roles & privileges

Managing using Management Studio

14. Working with Indexes

            In this module, how to create an index, advantages and disadvantages of index, and types of index and dropping index, and about index structure.

Introduction Clustered and Non-Clustered Index

Creating and Dropping Indexes

15. Writing Transact-SQL (T-SQL)

              In this module, what is script and what is batches, variables and if else statements and working with temporary tables, and checking with the existence of database objects.

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

16. Working with Stored Procedures and Functions

               In this module, we will learn how to create a stored procedure and difference between stored procedure and table, advantages of procedures and about types of parameters.

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

17. Implementing Triggers

            In this module, we will learn about what is trigger and how to create a trigger, types of triggers How to use a rollback Tran.

Introduction to triggers

Constraints vs Triggers

Creating, Altering, Dropping triggers

for/after/instead of triggers

Using Rollback Tran

18. Working with Cursors

     In this module, how to create a cursor, what is cursor, types of cursor and benefits of cursor.

Creating Cursors

Cursors vs. Select

Types of cursors

Locks on cursors

Advantages of cursors

19. Transaction Control Language (TCL)

            In this module, we will learn what is transaction, transaction management and what are acid properties, what are isolation levels and about implicit and explicit transactions.

Introduction Transactions process

Types of transactions (Implicit, explicit)

Working with Locks, Types of locks

20. Backup and Restore

            In this module, how to backup database and how to restore a database and how to generate a sql script, and executing a sql script and how to attach and de-attach a database.

Generating SQL Script

Executing SQL Script

Generating Change Script

Taking database Backup

Restoring database using backup

Attaching and Detaching of database

21. Advanced Features

      In this module, we will learn about ranking functions and about how to use xml datatype.

Pivot Table

Common Table Expression

Ranking Functions Using BLOB data type

Using XML data type

Projects:

            As part of this course, you will be working in 2 projects.

 

  • Name
  • E-mail
  • Course
  • Phone number
  • Subject
  • Message