Course Overview
16h 57m | 124 Videos | 75346 Views | 
Intermediate| English[Auto-generated] 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. |
