Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

Data is a collection of facts and figures and we have humungous data available to the users via the internet and other sources. To manipulate the data, Structured Query Language (SQL) in short has been introduced years ago. There are different versions of SQL available in the market provided by different organizations.

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

Syllabus:

• Module 1:- Introduction to Basic Database Concepts
• What is Data, Field, Record and database?
• Limitations of File Management System.
• Basic Concepts of Advantages of DBMS
• Levels of Abstraction | Database Models
• Exploring Relational DBMS
• Understanding Client and Server
• Module 2: E-R Modeling and Diagram
• Analyzing the Requirement
• Identify Entities and their Relationships
• Drawing E-R Diagram
• Conversion of E.R. Diagrams into Tables
• Module 3:Normalization
• First Normal Form
• Second Normal Form
• Third Normal Form Practically Normalizing Tables
• Module 4: Introduction to SQL Server
• 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
• Module 5: Introduction to SQL
• 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
• Module 6: DDL and DML Statements
• Why write statements in Frontends?
• Create, Alter and Drop Table Insert
• Update and Delete Statement Truncate Statement
• Module 7: Working with Queries (DQL)
• 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
• Module 8: Aggregate Functions
• Using functions in Queries
• Using Predefined Functions
• Count, Sum, Min, Max, Avg Group By and Having Clause
• Using Group By with Rollup and Cube
• Module 9: Joins and Set Operations
• Introduction to Joins Cross Joins
• Inner Join
• Outer Join
• Self Join
• Co-related Sub Queries
• Set Operations using Unions, Intersect and Except
• Module 10: Implementation of Data integrity
• Entity integrity
• Domain integrity
• Referential integrity
• Types of constraints
• Module 11:Working with Constraints
• Unique
• Not NULL
• Primary Key
• Default Check Foreign Key
• Module 12:Implementing Views
• Introduction & Advantages of Views
• Creating, Altering, Dropping Views
• Advance Options while Creating a View
• SQL Server Catalogue Views
• Module 13:Data Control language (DCL)
• Creating Users & Roles
• Granting & Revoking of Roles & privileges
• Managing using Management Studio
• Module 14:Working with Indexes
• Introduction Clustered and Non Clustered Index
• Creating and Dropping Index
• Module 15:Writing Transact-SQL (T-SQL)
• 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
• Module 16:Working with Stored Procedures and Functions
• Introduction to stored procedures
• Benifits of stored procedures
• Creating, Executing Modifying, Dropping
• Input–Output and Optional Parameters
• System defined SP’s and Functions
• User defined Functions
• Module 17:Implementing Triggers
• Introduction to triggers
• Constraints vs Triggers
• Creating, Altering, Dropping triggers
• for/after/instead of triggers
• Using Rollback Tran
• Module 18:Working with Cursors
• Creating Cursors
• Cursors vs. Select
• Types of cursors
• Locks on cursors
• Advantages of cursors
• Module 19:Transaction Control Language (TCL)
• Introduction Transactions process
• Types of transactions (Implicit, explicit)
• Working with Locks, Types of locks
• Module 20:Backup and Restore
• Generating SQL Script
• Executing SQL Script
• Generating Change Script
• Taking database Backup
• Restoring database using backup
• Attaching and Detaching of database
• Module 21:Advance Features
• Pivot Table
• Common Table Expression
• Ranking Functions Using BLOB data type
• Using XML data type