Certified Microsoft SQL Server Training Courses

We bring you this wonderful course on Certified Microsoft SQL Server Training. This detailed course will help you understand all the important concepts and topics of Certified Microsoft SQL Server Training.

What is Microsoft SQL

Microsoft SQL Server is a relational database management system which was developed initially by Microsoft in the year 1989. It helps in storing and retrieving data as requested by the software applications. There are a lot of editions of Microsoft SQL server.

Course Objectives

At the end of this course you will be able to

• Learn how to install SQL Server
• Know how to set up practice labs in SQL server
• Write queries using the SQL Server software
• Create your own tables and perform various operations

Pre requisites for taking this course

Anyone who is taking this course should have a basic understanding of databases and how they work. To learn this course more effectively you should have a copy of the SQL server installed in your system.

Target Audience for this course

This course is for anyone who is eager to learn about SQL server and understand how it works.

Course Description

Section 1: Concepts of Database Management Systems

Introduction to Database

Database Management System is the most efficient way of storing and retrieving data using appropriate measures. This chapter explains what is database, What are database systems and Introduction to DBMS.

Database Models

Data models explains the logical structure of how a database is modelled. The data models explain how the data is connected to each other and how it is stored in the system. The different data models are explained in brief under this chapter

• Relational Data Model – Explains the important concepts and constraints
• Entity Relationship Model – Explains Entity, Attributes and Relationship

Relational Database Management Systems (RDBMS)

RDBMS is a DBMS that is based on the relational model. RDBMS is the basic for SQL and other database systems like MS SQL Server, MySQL and others.

Section 2: ER Model and Normalization

Data Modelling

This gives an introduction to all the data models.

Entity Relationship: One-to-many

The logical association between the entities are called relationship. The mapping cardinalities are One to one, One to many. Many to one and Many to Many.

Normalization

Normalization is a method to remove the anomalies and bring the database to a consistent state. It explains First Normal form, Second Normal Form, Third Normal Form and Boyce-Codd Normal Form.

Section 3: Overview of SQL Server

Introduction to SQL Server

SQL Server is a relational database technology which was developed by Microsoft. SQL server has various default tools to help you with your database administration and programming tasks. These tools are given in this chapter. The key components of SQL server are – Database Engine, SQL Server Analysis Services, SQL Server Reporting Services and SQL Server Integration Services. These components and features of SQL server are explained in brief under this chapter.

Two- tier & Three- tier models

The two tier model has a client tier and data tier whereas a three tier model has client layer, business layer and data layer. These models are given in detail in this chapter.

System databases under SQL Server 2005

The SQL Server has the following system databases

• Master, Resource, TempDB, Model, MSDB, Distribution, ReportServer, ReportServer TempDB.

Logical Database Components

The logical components are used to connect to the database. There are two logical components

• Tablespaces – Permanent, Temporary, Undo
• Database’s Schema Objects – Database objects

Relational database architecture

The DBMS has a three tier architecture – Presentation tier, Application tier and Database tier. These three tiers are explained in this section.

Section 4: Overview of SQL Server (Lab Guide)

This section gives a quick recap of what SQL server is and all the basics of SQL server.

Section 5: Transact SQL Language

SQL Features and Variables

Transact SQL Language is an extension of SQL which is used in the SQL server. It is closely integrated into SQL language. This section has an introduction to T-SQL and its features.

Data types & Comments in Transact SQL

There are different data types in T-SQL – Exact Numeric type, Approximate numeric type, Date and time type, Character strings, Unicode Character strings and Binary strings. These data types and its comments in T-SQL are explained in detail in this tutorial.

Functions in transact SQL

SQL has a lot of built in functions to perform processing. The important ones are COUNT, MAX, MIN, AVG, SUM, SQRT, RAND, CONCAT, STRING and NUMERIC.

Expressions and DDL

Expressions is combination of symbols and operators used to obtain a single data value. The simple expressions, its syntax and the arguments are given in this chapter.

Data Definition Language is used to define data structures in SQL server. It includes various statements like ALTER, CREATE, DISABLE TRIGGER, DROP, ENABLE TRIGGER, TRUNCATE TABLE and UPDATE STATISTICS.

Data Manipulation Language (DML)

DML is used to retrieve and work with data in SQL Server. The DML statements explained in this section are BULK INSERT, DELETE, INSERT, MERGE, READTEXT, SELECT, UPDATE, UPDATETEXT and WRITETEXT.

Enhancements in SQL Transact

The recent enhancements made to T SQL is given in this chapter.

Section 6: Transact SQL Language (Lab Guide)

Transact SQL Commands

The TSQL commands are grouped under various headings like

• Data Definition Language (DDL) statements
• Data Management Language (DML) statements
• Query statements
• Assignment statements
• Procedure statements
• Transaction statements

Transact SQL Batch

A batch is a group of TSQL statements sent at the same time from an application asking the SQL server to execute it.

Transact SQL Script

A script in Transact SQL is a series of statements stored in a file. Scripts have more than one batches. The uses of scripts are given in this chapter.

Section 7: Creating and Managing Database

SQL Server Supported Databases

This section mentions the databases which are supported by SQL Server for its installation.

User defined Databases

User defined database roles are defined to make a group of users perform a specified task in SQL server.

Sample Database File groups and Transaction Logs

SQL Server database has three types of files – Primary, Secondary and Transaction Logs. The file groups are divided into Primary and User defined. These database files and file groups are explained in detail in this chapter.

Database Modifications, Dropping and snapshot

A database snapshot is a static view of the SQL Server database. Dropping a database snapshot deletes the snapshot from SQL server. Both the topics are covered in detail here.

Section 8: Creating and Managing Database (Lab Guide)

Creating a Database in SQL Server

This topic describes how to create a database in SQL server, how to modify it and how to delete it.

Section 9: Accessing Data From a Database

Select Statement and its Different Expressions

• The SELECT statement is used to retrieve records from one or more tables in a SQL Server.
• The SELECT INTO statement is used to create a table from an existing table
• The SELECT TOP statement is used to retrieve and limit the number of records returned.

GROUP BY Clause

This section explains how to use GROUP BY Clause in SQL server with its syntax and examples.

ORDER BY Clause

This section explains how to use ORDER BY Clause in SQL server with its syntax and examples.

Section 10: Accessing Data From a Database (Lab Guide)

Using Select statement with various clauses

In this chapter you will learn the various SELECT statements with its syntax and examples.

Section 11: Creating Tables and using Data Types

Data Types in SQL Server

Data type defines the type of data the column can hold. SQL has different data types and such data types are explained in this section.

Creating Table & Column Nullability

Here we will see how to create tables and what is columns, rows and null values in columns.

Default Definition and Identity Property

The identity property creates an identity column in a table. The syntax and examples are provided

Section 12: Creating Tables and using Data Types

Globally Unique Identifier

This chapter explains what is GUID value and how it can be obtained.

Unique Constraints

This chapter explains how to create, add, and drop unique constraints in SQL Server with syntax and examples.

Modifying Tables

Under this chapter we will see how to work with tables and modify them

Section 13: Creating Tables and using Data Types (Lab Guide)

Creating Tables

In this section you will learn how to create tables in SQL Server.

Using constraints, unique identifiers and typed xml

This section has the following topics

• Create, Modify and Delete Unique Constraints
• Create, Modify and Delete Check Constraints

Section 14: Introduction to Data Integrity

Data integrity Introduction

Data integrity makes sure about the quality of data in the database. There are four types of integrity – Entity integrity, Domain Integrity, Referential Integrity and User Defined Integrity. In this chapter all the data integration details are discussed.

Practical Example for Creating a User defined Data type

Under this chapter you will learn some examples of creating a user defined data type

Section 15: Introduction to Indexes

Extents, Clustered and Non clustered indexes

In this section you will learn how to create, rename and drop indexes in SQL Server with syntax and examples. The different types of indexes are also discussed here.

Heap, IAM and Allocation pages

IAM pages tracks the space of a single GAM interval in a single file.

The heap is a table without a clustered index.

These two topics are covered in detail in this lesson

Section 16: Types of Indexes

Unique Index

The different types of indexes like Unique, Clustered, Non clustered, Full text and regular indexes are given with practical examples.

Section 17: Assignments of SQL Server

Create database practical examples

Under this chapter we will see some practical examples of creating a database in SQL server.

Creating, altering and inserting data in table

This section helps you to learn about the various operations performed in a table like creating, altering and inserting data in a table.

Selecting records using built-in functions

This lesson explains the techniques of creating records on a table of a database in SQL Server.

Using constraints in tables

Constraints are used to enforce data integrity in SQL Server Tables. This section explains the type of constraints  and how it can be used in Tables.

Section 18: Creating Indexes

Starting with Creating Indexes

The CREATE INDEX statement is used to create indexes in tables. It is explained with syntax and example.

Creating a Composite Indexes

Composite index is a index on two or more columns of a table. This lesson contains details on composite index with example.

String Functions

SQL Server String functions are scalar functions which is used for string manipulation. The important string functions are explained in this section

Using Math Functions

The scalar math functions in SQL Server are used to perform calculations. few of the important math functions like ABS, CEILING, DEGREES are discussed in this chapter

Date Functions

This chapter contains all important date and time related functions of SQL Server.

Query Assignments

In this chapter you will learn how to set variable from a SQL query.

Section 19: Maintaining Indexes

Online index operations and degree of parallelism

The topics covered under this chapter are

• Degree of Parallelism
• Parallel Query example
• Parallel Index Operations

Locking Option, Lock escalation and Indexes

This lesson will help you to learn what is lock escalation and the lock hierarchy.

Partitioned indexes, XML index and index statistics

In this lesson you will learn about Partition index, XML index and index statistics.

Section 20: Implementing Views

Partitioned, Distributed and updatable partitioned view

View is a pre written query that is stored on the database. This chapter deals with the benefits of views, different types of views, how to create views, running a view, modifying a view, syntax and examples of views.

Section 21: Additional Examples

If Else and Case Statement

Case and If Else statement is explained with examples in this chapter.

Joins

SQL Join is used to combine rows from two or more tables. The different type of joins are explained in this chapter.

Cursors

Cursor is a database object to retrieve data from a result. The lifecycle of cursor and the syntax to do different operations in cursor are explained.

Subqueries

Here you will learn how to add Subqueries to the SELECT clause

Section 22: Managing Views

Schema binding, indexed, Query optimizer

This section explains about indexed views, schema binding option in views and the use of optimizer.

Modifying Views

You can modify the definition of views in SQL Server without recreating it. The management of views are explained in detail under this section.

Section 23: Introducing Stored Procedures

Types of Stored Procedures and Catalog SP

A stored procedure is a prepared SQL code which you can use again and again. The four types of stored procedures – System defined, Extended, User Defined and CLR. This chapter will let you learn how to create, modify, delete, execute, grant permissions, return data, recompile and rename a stored procedure.

Section 24: Practical Examples

Examples- Catalogue Stored Procedure

Under this chapter practical examples of stored procedures are given for your easy understanding.

Section 25: Additional Examples

Output Parameters- Create Procedure Summation

Output parameters is an option to pass parameter value back out from a stored procedure. Here you will learn in detail about the output parameter procedure along with few practical examples.

Cursors, Control of flow and transaction

This section gives you the keywords and statements used in Cursors and Control of flow language in Transact SQL.

Section 26: More About Stored Procedures

Understanding View Dependencies

This chapter describes how to view stored procedure dependencies in SQL Server.

Code Snippet

Code Snippet contains the pre defined snippets in Transact SQL.

Passing Values by Position

T SQL has a number of ways to pass the data between Transact SQL statements. In this section we will learn about the different ways to pass in value as a parameter.

Understanding Error Functions

The overview of error handling functions, error handling mechanism and the general error syntax used in SQL Server are discussed in this section.

Section 27: Introduction to Triggers

A trigger is a special type of stored procedure that is executed when an event occurs in the database server. The three types of triggers are DDL. DML and Nested Triggers. These are explained in detail in this lesson with syntax and examples.

Section 28: Additional examples on Triggers

Triggers Examples

This section contains practical example of triggers in SQL server.

Creating Trigger for Insert, Update or Delete Employee Records

Here you will learn how trigger can be used to Insert, Update or Delete Employee Records.

Example- Alter Table

Examples for altering a table, inserting record with salary condition and creating a table enquiry is given in this chapter.

Examples- Queries

Example for queries are provided in this chapter

FAQ’s General Questions

• What are the benefits of SQL Server training ?

Many positions in IT field are related to SQL Server either directly or indirectly. So knowing about SQL Server will widen your career paths and will help you to get your work done quickly at your workplace. It’s not that only DBA’s should know about SQL Server. Everyone can take up this course and gain knowledge about SQL Server.

Testimonials

Andrews

This is a great course for beginners as well as professionals to learn about SQL server. The course starts with a brief introduction to SQL Server and covers all the important topics. The practical examples given under each session allows to practice what you learn online using the SQL interpreter. Even if you are familiar with SQL you can take this course as a refresher. It was such a great experience learning this course. Highly recommended.

Where do our learners come from?

Professionals from around the world have benefited from eduCBA’s Certified Microsoft SQL Server Training courses. Some of the top places that our learners come from include New York, Dubai, San Francisco, Bay Area, New Jersey, Houston, Seattle, Toronto, London, Berlin, UAE, Chicago, UK, Hong Kong, Singapore, Australia, New Zealand, India, Bangalore, New Delhi, Mumbai, Pune, Kolkata, Hyderabad and Gurgaon among many.

Back to top ▴

View courses without internet connection with a Lifetime Membership

View Offline - Internet-free viewing with your iOS or Android App

Watch offline with your iOS/Android app.

Start Your Free Trial Now

You can download courses from your iOS/Android App.