SQL SERVER - Vinay Tech House

Home / Business Intelligence Courses / Power BI

SQL Server Training

This course covers Microsoft SQL, T-SQL related to OLTP and OLAP databases with Detailed Materials, Software Installation and Videos.REAL-TIME PROJECT and SCENARIOS will help.

Classroom | Online | Fast Track | Self Paced

Course Highlights

Live Projects

Placement Assistance

Expert Trainers

Premium Training

Live Video Share

Real-Time Case Studies

Frequently Asked Questions

What is SQL Server and What are it's uses?

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.

Is SQL Server easy to learn?

It has good graphical interfaces to interact and simple language ( T-SQL ) to perform Operations.

Does SQL Server have a market?

SQL Server providing Transactional and analytical operations with its RDBMS features. The scalability and fail over tolerance increasing the usage of SQL Server. Hence, Customers looking forward to use the strongest Database in their projects.

What if I miss a training session?

No need to worry, daily videos are in the class room folder and materials in the materials folder. You can watch and practice and also reach out to the trainer for queries.

What type of study materials and videos we will get and how long they are available?

You will get all materials after a week of your joining which you can download and use. Daily live class recordings will be placed in a folder and you will have access for maximum 12 months. Lab practice documents and required Power BI credentials provided for practice till one year.

Quick Enquiry

Why Join Vinay Tech

In-Depth Curriculum

Our educational program aims to comprehensively cover all & important points & aspects of the subject

Course Specialized Trainers

Our Trainers are high experienced industry experts with their teaching methods designed for their particular course

10+ Years of Experience

We’ve come a long way teaching from hundreds to thousands, so we use our experience in guiding students in the right path

Students Favourite

Vinay Tech House is prefered by many students as their favourite, rating as the best in teaching

Curriculum

SQL Server Content for OLTP and OLAP Databases in IT

Data warehouse Fundamentals

Data warehouse, Data Mart and differences
Types of data marts and real-time usage
ODS, Stage, EDW, and DW definitions
Data Lake and Blob Storages
DWH Life Cycles
DWH Approaches (INMON and KIMBALL)
Data Granularity, Data movement stages

SQL Introduction

What is Sql
How many areas we can use sql to communicate with Databases
Sql Versus T-Sql
Various Tools and Utilities to write Sql

SQL Server Introduction, Pre-Requisite & Installation

Server name or Instance name and authentication
Versions and Editions in SQL Server
Connections [Local and Remote]
Enterprise Edition
Standard Edition
Developer Edition
Work Group Edition
Express Edition
Sql Server and SSMS Installation
Sql Server Management Studio
Object Explorer
Query Editor
System Databases and User Databases
System tables and User tables

Databases & Tables

System Defined Databases and usages
User defined databases and usages
Master, MSDB, TEMPDB and others real-time usage
Database creation GUI and Code [MDF & LDF files]
SQL Server Agent usage
Windows Vs. Sql Server Authentication
System tables and User tables

Command, Query & SQL Server Architecture

Differences between command and query
Query and command execution in SQL Server
Parser, Compiler, Syntaxer, Optimizer, and CLR
Storage Engine [SQL Engine]
Lock, File, Transaction Manager, and BUffer Manager
Buffers and Threads
Data types impact internal process

SQL Sub Languages

Sql Server Data Definition Language[DDL]
Sql Server Data Manipulation Language[DML]
Sql Server Data Control Language[DCL]
Sql Server Transaction Dictionary Language[TCL]

Data Definition Language

Creating Table Definitions [CREATE]
Modifying Table Definitions[ALTER]
Removing Table Definitions [DROP AND TRUNCATE]
TRUNACTE and DROP performance differences
Sql Server Data Types
Create table from another table

Data Manipulation Language

Inserting data into table [SELECT, INSERT and SELECT]
Single Insert and Multiple Inserts
Modifying table data [UPDATE]
Removing table data [DELETE]
BULK INSERT and BCP [Bulk Copy Program]
MERGE comand operation and Incremental Load [SCD and CDC]
Sql Server Data Types
Insert table from another table

Data Control language

Providing privileges [GRANT]
Removing privileges [REVOKE AND DENY]

Transaction Control Language

Saving work [COMMIT]
Restore work [ROLLBACK]
Saving period of work [SAVEPOINT]

Data Filtering [ Various Clauses ]

=, !=, <>, >, <, <=, =< etc... comparison Operators
AND, OR, NOT Logical operatos
+, -, *, / , Mod, Exp etc...Mathematical Operators
Order By, Top, Where, From and Like
Group By and Having
IN, NOT IN, BETWEEN and NOT BETWEEN
ISNULL and NOT ISNULL

Multiple Table Operations [ Column Wise-Sub Queries ]

Simple Sub Query
Correlated Sub Query
Differences between simple and correlated
Nested Sub Query
Working on TOP, MAX, and MIN real time queries

Multiple Table Operation [ Rose Wise-Set Theory ]

Set theory generic protocols
INTERSECT
UNION
UNION ALL
EXCEPT
Working on incremental loading

Multiple Table Operations [ Column Wise-Joins ]

JOINS real time usage
CROSS JOIN and CROSS APPLY
INNER JOIN[EQUI, NON EQUI]
NATURAL JOIN
SELF JOIN
INNER Vs. OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Working on ON and WHERE clauses
MERGE JOIN
LOOP JOIN
HASH JOIN
Unmatched data retrieval
Incremental load in real time using Joins

Business Data Constraints & Data Rules

CHECK, NOT NULL, AND DEFAULT – Domain Integrity
Primary Key usage and limitations
Unique Key usage And limitations
Referential Integrity and FORGINE KEY
Candidate key and Alternate key
Normal column and Identity column
Surrogate key and Identity column usage
CASCADING OPTIONS
ON DELETE CASCADE ON UPDATE CASCADE
ON DELETE SET NULL
ON UPDATE SET NULL
ON UPDATE SET NO ACTION(Default)

Indexes [ basics to advanced ]

Clustered Index Design and Structures
Nonclustered Index Design and Structures
Unique Index Design
Index with Included Columns
Column storage index
Full-Text Index population
Filtered Index Design
Covering Index Design
B-Tree and Online Indexes
Indexed views Vs. Materialized views
Fill Factor, TEMPDB, Pat_Index

Normalization & Data Modelling

Data model usage in projects [OLTP and OLAP]
Data model types and differences
Database Diagrams and ER Modeling
ER Modeling tools
1st, 2nd, 3rd Normal Forms Usage
Boyce-Codd Normal Form Usage
Relationship Types
One To One One To Many
Many To One
Many To Many

Schemas, Synonnys, System Defined Views & Procedures

What is Schema? and real time advantages with practical.
What is Synonym? and real time advantages with practical.
System Defined Procedures usage with examples
sp_help
sp_helpdb
sp_helptext
sp_rename
sp_recompile
sp_tables
System Defined Views usage with examples
sys.tables/li>
sysjobactivity
sysssislog

Distinct, Group, Rollup & Cube

Differences between GROUP BY and DISTINCT and performance impact
GROUP BY and HAVING usages to identify and eliminate duplicates ROLLUP and CUBE usages
Generating FULL TOTALS and SUB TOTALS
Comparing ROLLUP, CUBE and GROUP functions

Views [ User Defined ]

Advantages Of Views in SQL
Tables Vs. Views
Complex View (Non Updatable View)
Materialized Vew and real time usage
Encrypted views Vs. Cascading views
Limitations Of Views

Functions [ System, User Defined & Analytical ]

Date and Time Formats and Functions
Scalar - Valued Functions
Table - Valued Functions
String Functions
SUBSTRING, REPLICATE, REPLACE
REVERSE, CHARINDEX
LEFT, RIGHT, LEN
DFFERENCE, SOUNDEX
STRING_SPLIT
Mathematical Functions
ABS, ROUND, LOG
FLOOR, CEILING
SUM, AVG, MAX, MIN, COUNT
SQUARE ROOT, SQUARE
SIN, TAN, COS
Date Functions
DATEADD, DATE DIFF, DATE PART
NOW, DAY, MONTH, YEAR
GETUTCDATE, GETDATE, CURRENT_TIMESTAMP, SYSDATETIME
DATE NAME, ISDATE
WEEKDAY, MONTHNAME, WEEKDAYNAME
SECOND, MINUTE, HOUR, ISDATE
Other Generic Functions
REVERSE, CHARINDEX
COALESCE, NULL IF, CURRENT USER, IIF
ISNULL, NULLIF, SESSION_USER
SESSIONPROPERT, SYSTEM_USER, USER_NAME
FORMAT, INSTR, CONCAT,
Cast and Convert Functions
IF, ELSE, CASE, WHEN & END
PIVOT & UNPIVOT
ANALYTICAL FUNCTIONS
ROW_NUMBER() and real time examples
RANK() and real time examples
DENSE RANK() and OVER () usages
NTILE advantage
PARTITION BY advantage
Using Group BY along with Analytical Partition
User Defined Functions Create
Cast and Convert Functions
User Defined Functions Calling
Differences between Function and Procedure

Stored Procedures [ User Defined ]

Use in Real Time and Types
System Defined and User Defined Procedures
Dynamic SQL Queries in Procedures
IN, OUT, INOUT Parameters
Compare Procedures and Functions
READONLY Parameters
Dynamic Data Insertions with Procedures
Table Variables, Cloning & Data Inserts
Using TEMP tables in procedures
Mathematical Functions
Stored Procedure inside Stored Procedure
Optimizing tips for procedure

Variables, Tables [ Different Types ] & CTE

Local variabes vs Global variables with examples
Local variables Vs. Temp variables and real time usage
TEMPORARY table usages in real time
Inline View Vs. Normal View
CTE: Common Table Expressions
CTE usage in real time
Multiple examples using CTE
ROW_NUMBER() with CTE Queries
Recursive

Triggers

Use in Real Time and Types
SCHEMA BINDING
Triggers Vs. Procedures
Types of triggers
TRIGGERS
DDL TRIGGERS
FOR TRIGGERS
INSTEAD OF TRIGGERS
Rel-time usage of triggers at eventsli>

Cursors [ User Defined ]

What is cursor and advantages of it.
Cursor Vs. STored Procedure
Working on different types of cursors
Static Cursors
Dynamic Cursors
Global Cursors
Local Cursors
Forwardonly Cursors
Scroll Cursors
Keyset Cursors
Cursor detailed process and phases

Transactions, Checkpoint & DTC

What is Transaction? usage of it?
What is DTC [Distributed Transaction Coordinator]
What is Checkpoint? usage of it?
Transactions Vs. Checkpoint

Programming & Error Handling Intructions

IF, IIF, CASE
WHILE, WHEN
Error Handling in T-SQL
Try, Catch, Throw

Performance Tuning & Optimization process

Examples of performance issues in real time
DTA (DB Engine Tuning Advisor)
Audit Long Running Queries using DMVs and DMFs
Activity Monitor Tool and Query Statistics Reports
Logical I/O, Physical I/O and Database I/O, Wait Time
Partition Functions and Partition Schemes - Usage
Index Management : Internal, External Fragmentation
Index Page Count and Index Condition Checks
ONLINE and RESUME Options
Fast, Detailed Scans and Stats NoRecompute usages
Creating Workload Files and Trace Files
DTA with Query Cache (Procedure Cache) & .SQL File Inputs advantage
Full Text Search (FTS) Mechanism advantage
Processor, Disk, Memory, Transactions, Database Counters
Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost
Parameter Sniffing and OPTIMIZE Options
Spooling Mechanism and Spool Types for Query Loads
Execution Plan Issues with Parameter Sniffing
Query Blocking Scenarios and Lock Monitors
Serializable, Snapshot, Repeatable Read Isolation
Deadlock Simulation and Deadlock Prevention usage
Full Text (FT) Indexes : Query Tuning. Filter Daemon Host
Histograms and Event Handling Options
Working with SQL SERVER Profiler
Using Perfmon Tool and AM Tool
Implementing Compressions for Read Only Dat
Parameter Sniffing and OPTIMIZE Options
Implementing environment areas in the real time [DEV, TEST, UAT, and PROD]

Projects Covered

Invoice OLTP Database

Designing the Database from End to End, ER-Model preparation & Data Loading

Sales OLAP Database

Moving Data from Source Database to Target Database by using Stored procedure

Interested in a Free demo? Want to learn more?

Get Certified by Vinaytech House

Vinay Tech’s course completion certificate will be provided upon the completion of the course successfully. It increases the value of your resume and you can attain leading job posts with the help of this certification in leading MNCs. The certificate can be printed out like a hard copy or handed digitally.

Need help with Microsoft ETL Certification?

SQL Server Training​