SQL Server & T-SQL Training (LIVE, Instructor-Led)

This impeccable Microsoft SQL Server Training Course is exclusively designed for aspiring Data Analysts, Business Analysts, Data Scientists, MSBI / Power BI Engineers and SQL Database Developers. This SQL Server and T-SQL Training Course is designed for both starters as well as for experienced professionals. This course includes SQL Basics, T-SQL Queries, Quer Writing and Query Understanding required for Data Analysts and Business Analysts. This course also includes Programming, Tuning and Azure SQL (Cloud) concepts required for BI Developers and SQL Database Developers. One Real-time project is included in this SQL Server and T-SQL Training Course addressing Basic to Advanced SQL Queries, Detailed Database Design, Complex Joins, Common Table Expressions (CTEs), Complex Stored Procedures, Triggers, Remote Queries, Transactions and Query Tuning Operations with XML, BLOB and JSON Data Sources with complete guidance to MCSA 70-761 and MCSA 70-762 Microsoft Certification Exams.

Practice Material, Certification, Resume & Interview Guidance are included in this SQL Server T-SQL Course.

SQL Server & T-SQL Developer Training with Azure. Trainer : Mr Sai Phanindra T, 12+ Yrs Exp

  PLAN A PLAN B PLAN C PLAN D
Applicable For Data Analysts SQL Dev (0-1 yrs) SQL Dev (1-2 yrs) SQL Dev (2+ yrs)
Course Duration 2.5 Weeks 3.5 Weeks 4.5 Weeks 5.5 Weeks
Completely Real-time, Practical Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Mock Interviews, Case Studies Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Basics and Query Writing Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL DB Design, Table Design Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Normal Forms, Joins, Queries Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Indexes, Stored Procedures Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Advanced Stored Procedures Croos-symbol-for-No Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Real-time Project [Banking] Croos-symbol-for-No Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
In-depth Query Tuning Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Complex SPs, MCSA - 70 761 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure SQL Database (Cloud) Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
DB Deployments, MCSA 70-762 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Total Course Fee INR 4000/- INR 5000/- INR 7000/- INR 10000/-

* Data Analyst: MSBI Developer, Power BI, Tableau, SAP HANA, Data Scientists

Arrange Callback from the Trainer

 
 
Sch 1: 6:30 AM to 7:30 AM
October 24th - Register
Sch 2: 9 AM to 10 AM
Oct 30th - Register
Sch 3: 10:15 AM to 11:15 AM
Nov 7th - Register
Sch 4: 11:15 AM to 12:15 PM
Nov 2nd Week - Register
Sch 5: 5:30 PM to 6:30 PM
November 13th - Register
Sch 6: 7 AM to 9 AM (Wk)
October 27th - Register
 

SQL Server T-SQL (DEV) Training Course Contents:

PART 1 Of 2: SQL Server Basics, Queries, Stored Procedures and Database Development

Module I: SQL Basics, SQL Server Concepts

Applicable for Plan A, B, C, D

Module II: T-SQL Queries & Programming Basics

Applicable for Plan A, B, C, D

Module III: T-SQL Programming & Real-time Project

Applicable for Plan B, C, D

DAY 1: DATABASE & SQL SERVER BASICS

  • Introduction to Data, Databases, DBMS
  • Database Basics : Types of Databases
  • DB Types: OLTP, OLAP, DWH, HTAP
  • Microsoft SQL Server : Advantages
  • SQL, Transact SQL (T-SQL) Variants
  • Microsoft SQL Server - Career Options
  • SQL Developers : Job Responsibilities
  • BI Developers : Roles, Responsibilities
  • Data Analyst : Roles, Responsibilities
  • Business Analyst Vs Data Analyst
  • Course Plan, Mock Interview Details
  • Real-time Project Details For Resume
  • 24x7 LIVE Server Access (Online Lab)

DAY 7: JOINS AND QUERIES - Level 1

  • JOINS - Types and Real-time Usage
  • JOIN Types and JOIN Options in T-SQL
  • INNER JOIN - Examples, WHERE, ON
  • OUTER JOIN - LEFT, RIGHT, FULL
  • CROSS JOIN and CROSS APPLY
  • MERGE JOIN - Joining Bigger Tables
  • LOOP JOIN - Joining Smaller Tables
  • HASH JOIN - Joining HEAP Tables
  • GROUP BY Queries and Aggregations
  • GROUP BY Queries with Having Clause
  • ROLLUP, CUBE Queries with Group By
  • Grouping () Functions in Rollup, Cube
  • Replacing Nulls: ISNULL, COALESCE

DAY 13: STORED PROCEDURES - Level 2

  • Error Handling in T-SQL: TRY & CATCH
  • Error Handling : THROW. In-Memory, CTE
  • Common Table Expressions (CTE)
  • In-Memory Query Store and Result Store
  • CTE for DML, DDL Operations in SPs
  • Recursive CTEs and ANCHOR Elements
  • TERMINATION CHECK, Looping Options
  • Table Valued Parameters (TVP) - Usage
  • Cached Plans, Stored Procedures Memory
  • SQL Injection Attacks, Parameter Sniffing
  • Stored Procedure - ReadWrite Parameters
  • READONLY Parameters in Stored Procs
  • User Defined Data Types, Real-time Use

DAY 2: SQL SERVER INSTALLATION

  • SQL Server 2019 Installation Guidance
  • SQL Server 2017 and 2016 Installation
  • Installation Pre-Requisites and Precautions
  • SQL Server Management Studio (SSMS)
  • SSMS - SQL Server Instance Connections
  • SQL Server Versions, Editions Comparisons
  • Evaluation, Developer Editions - Instances
  • Instances, Instance Types & Instance IDs
  • System Administrator Users @ Instance
  • Windows Authentication Type and its Use
  • Mixed Mode Authentication Type and Use
  • Default Admin Account in SQL Server : "sa"
  • SQL Server Services and Service Accounts
  • File Stream and Collation in SQL Instances

DAY 8: SUB QUERIES, JOINS - Level 2

  • Joining 2 Tables with Group By, Having
  • Joining 3 Tables with Group By, Having
  • Joining 4 Tables with Group By, Having
  • Using MERGE and ROLLUP with Joins
  • Sub Queries Versus Joined Queries
  • Nested Queries and Joins in WHERE
  • Nested Sub Queries with JOIN Options
  • Cast and Convert Functions - Usage
  • Date, Time Functions, Sub Queries
  • DateAdd, DateDiff, Date Time Types
  • Date & Time Styles Usage in Queries
  • Data Formatting Options in Queries
  • Using Date and Time Formats in Joins
  • Join Queries - Aggregations, Formats

DAY 14: STORED PROCEDURES - Level 3

  • Cursors - Benefits, Cursors in SProcs
  • FORWARD_ONLY and SCROLL Cursors
  • STATIC Cursors, DYNAMIC Cursors
  • LOCAL Cursors and GLOBAL Cursors
  • KEYSET DRIVEN Cursor - Performance
  • ABSOLUTE Keyword & FETCH Options
  • FETCH_STATUS and CURSOR_ROWS
  • SPs and Cursors - Dynamic Data Loads
  • Nesting of Stored Procedures - Dynamic
  • Data Formatting in Stored Procedures
  • WHILE Loops, Dynamic SQL in SPs
  • Using Temporary Tables for Formatting
  • Procedure Output to Temp Tables
  • Temporary Tables Vs Table Variables

DAY 3: BASIC SQL QUERIES - Level 1

  • DDL, DML, SELECT, DCL and TCL
  • Creating SQL Databases and Tables
  • CREATE, ALTER, DROP Statements
  • INSERT, UPDATE, DELETE Statements
  • Data Inserts, Values and Table Scan
  • Data Types & their Usage (Basic Level)
  • INTEGER, CHAR, FLOAT Data Types
  • Character Range for Character Data
  • INSERT and INSERT INTO Statements
  • Single Row Inserts, Multi Row Inserts
  • SELECT Statement for Table Retrieval
  • WHERE Examples : =, !=, <,>, <=, >=
  • AND, OR, NOT, IN, NOT IN Conditions
  • BETWEEN, NOT BETWEEN Conditions

DAY 9: INDEXES, VIEWS QUERIES

  • Indexes: Architecture and Types
  • Clustered and Non Clustered Indexes
  • Included and ColumnStore Indexes
  • FILTERED and COVERING Indexes
  • UNIQUE Indexes, Query Optimizer
  • LIVE Online Indexes in Real-time
  • B Tree Structures and IAM Pages
  • Tuning Joined Queries, Conditions
  • Views on Tables - SCHEMABINDING
  • ENCRYPTION and CHECK OPTION
  • Orphan Views - Real-world Solutions
  • Cascaded Views, Encrypted Views
  • System Views for Metadata Access
  • Indexed Views / Materialized Views

DAY 15: Constraints, Functions - Level 2

  • CASCADING with Character Keys
  • ON UPDATE, ON DELETE CASCADE
  • Self Referencing Keys, Practical Use
  • Adding / Disabling Constraints, Keys
  • PIVOT Operation & MERGE Statement
  • TRY_PARSE and TRY_CONVERT
  • PATINDEX, CHARINDEX & STUFF
  • Correlated Sub Queries and Aliasing
  • Using BULK INSERT & BULKCOLUMN
  • OPENROWSET For Data Import, CAST
  • Generating Calendar Values and Types
  • CASE, SWITCH, IIF, CHOOSE Functions
  • XML Options in T-SQL Queries, Joins
  • XML AUTO, XML RAW and XML PATH

DAY 4: BASIC SQL QUERIES - Level 2

  • CHAR Versus VARCHAR Data Types
  • VARCHAR & NVARCHAR Data Types
  • GO Statement, SQL BATCH Concept
  • BIGINT, BIT, SQL_Variant Data Types
  • IS NULL, NOT NULL. LIKE, NOT LIKE
  • ORDER BY with ASC, DESC Options
  • DISTINCT, TOP and COUNT() Options
  • FETCH, OFFSET, NEXT ROW Options
  • UNION, UNION ALL and Sub Queries
  • Single Quotes, Double Quotes, Aliases
  • UPDATE : Conditional & Unconditional
  • DELETE and TRUNCATE Commands
  • ALTER Command   DROP Command

DAY 10: Functions, Procedures - 1

  • Scalar Value Returning Functions
  • Inline Table Functions, Dynamic Joins
  • Multi-Line Table Functions Usage
  • Table Variables and Table Data Type
  • Variables & Parameters in SQL Server
  • OBJECTID, OBJECTNAME Functions
  • System Functions & Metadata Access
  • Stored Procedures - Purpose, Usage
  • IF .. ELSE and ELSE IF Conditions
  • Using Procedures with Parameters
  • Recompilation of Stored Procedures
  • Sp_help, Sp_helpdb and sp_helptext
  • Sp_recompile, sp_pkeys System SPs

DAY 16 - 18: REAL-TIME PROJECT
This SQL Server T-SQL Course includes a Real-world BANKING Project. The project includes about 2500 Lines of Code with basic to complex Stored Procedures, Join Queries, Triggers, Transactions and Lock Hints. (COMPLETELY SOLVED).
Phase 1: DATABASE DESIGN
  • Understanding Project Requirements
  • End to End Project Work Flow
  • Naming Conventions in Real-time
  • DB Creations with Files, Filegroups
  • Table Schemas and Synonyms
  • Implementing Normal Forms (OLTP)
  • Creating Data Sheets, Test Data

 

Phase 2: QUERY DESIGN
  • Joining Tables for Reports
  • Query Design and Tuning
  • Views with JOIN Options
  • LIVE Query Stats, Index Selection
  • Implementing Indexed Views

 

Phase 3: PROGRAMMING
  • Automating DML Operations
  • Using Triggers with Views
  • Event Handling , Error Handling
  • Stored Procedures for DMLs
  • Stored Procedures with Triggers
  • Stored Procedures with Transactions

 

Phase 4: RESUME - PROJECT PLAN
  • Project Interview Questions
  • Project Challenges, Solutions
  • Project Deployments, Go LIVE
  • Resume Plan - MSBI / Power BI, Cloud

 

Career Options for SQL Developers
  • Advantages - MSBI(SSIS,SSAS,SSRS)
  • Advantages with Power BI
  • Advantages with Tableau
  • Advantages with Azure SQL

DAY 5: SERVER, DB ARCHITECTURE

  • Client Server Architecture, TDS Packets
  • SNAC and OLE DB Command for TDS
  • TDS Packets and SQL Query Processing
  • SQL Server Architecture Components
  • Protocols, SQL Native Client (SNAC)
  • Query Optimizer (QO) and SQL Manager
  • Storage Engine, File and DB Manager
  • Transaction Manager and Lock Manager
  • Buffer Manager, SQL OS and IO Buffer
  • Synchronization and Thread Scheduler
  • Logs, Checkpoints, Virtual Log File (VLF)
  • DB Data Files (mdf) and Log Files (ldf)
  • Primary/Secondary Files (ndf), Filegroups
  • Write Ahead Log (WAL) and Lazy Writer
  • Log Sequence Number (LSN), Mini LSN

DAY 11: TRIGGERS, TRANSACTIONS

  • Triggers - Purpose, Types Of Usage
  • DML Triggers - Events, Practical Use
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Enable Triggers and Disable Triggers
  • Database Level DDL Triggers - Usage
  • Server Level DDL Triggers - Usage
  • COMMIT and ROLLBACK Statements
  • EXPLICIT & IMPLICIT Transactions
  • Autocommit Transaction, ACID Options
  • Conditional Commits and Rollbacks
  • Open Transactions and Query Impact
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints

DAY 6 : CONSTRAINTS Level 1

  • Constraints, Keys - Table Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraints: Importance
  • PRIMARY KEY Constraint: Importance
  • FOREIGN KEY Constraint: Importance
  • REFERENCES, OLTP Relational Tables
  • CHECK, DEFAULT Constraints Usage
  • Candidate Keys and Identity Property
  • Normal Forms : 1 NF, 2 NF and 3 NF
  • BCNF, 4 NF, EKNF and ETNF Designs
  • Schemas - Purpose. Using DBO schema
  • User Defined Schemas & Data Transfer
  • Database Diagrams, PK-FK Base Tables
  • Local Temporary Tables and Sessions
  • Global Temporary Tables and Connections

DAY 12: REAL-TIME CASE STUDIES, EXCEL

SCENARIO 1: HEALTHCARE DOMAIN
  • Writing Queries : Joins, Group By
  • Joining 2 and More Tables, Formatting
  • Writing Sub Queries with Joins
  • ROW_NUMBER and RANK Functions
  • DENSE_RANK, PARTITION BY Queries

SCENARIO 2: SALES & RETAIL DOMAIN
  • Excel File - Imports and Exports
  • Excel Pivot Tables and Pivot Charts
  • Excel ODC Connections in Real-time
  • CASE and IIF() Conditional Queries
  • String Functions with JOIN Queries
PART 2 Of 2: Performance Tuning and Azure SQL Database (Cloud)

Module IV: Performance Tuning & MCSA - 70 761

Applicable for Plan C, D

Module V: Azure SQL Database Development & MCSA - 70 762

Applicable for Plan D

DAY 19: QUERY TUNING 1 - INDEXES, PARTITIONS

  • Audit Long Running Queries - DMV / DMF
  • Activity Monitor Tool and Query Statistics
  • Working with Plan Handle and Execution Time
  • Index Creation Options: SORT_IN_TEMPDB
  • FILL FACTOR and PAD INDEX Options
  • IAM Pages, GAM, SGAM and PFS Bit Maps
  • Resumable Indexes in SQL Server 2019
  • Table Partitions : Advantages, Performance
  • Database Filegroups and Partition Ranges
  • Partition Functions and Partition Schemes
  • Partition Un-partitioned Tables: Indexes
  • Aligned / Indexed Partitions - Importance
  • Partition Management - LIVE Online Tables
  • Partition SPLIT and Partition MERGE

Day 23: AZURE CLOUD & AZURE SQL DATABASE

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Advantages of Microsoft Cloud - Azure Platform
  • Azure Products and Azure Services - market Place
  • Comparing Azure with Google Cloud for SQL Server
  • Comparing Azure with AWS Cloud for SQL Server
  • Azure Sources - Types, Microsoft Market Place
  • Azure SQL & Databases - Need, Importance
  • Azure SQL Database Architecture Components
  • Creating Azure SQL Server Instances
  • Creating Azure SQL Databases, Price Tiers
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • Isolated Price Trier - Advantages, Performance
  • Advantages of Azure SQL Databases & Tools

Day 20: QUERY TUNING 2 - STATISTICS, FULLTEXT SEARCH

  • Execution Plans : Table Scan, Index Scan
  • Execution Plans : Index Seek, Query Cost
  • IO Cost and CPU Cost for Query Analysis
  • Operator Cost and Sub Tree Cost Analysis
  • Spooling, Temp DB Role for Query Prefetch
  • Statistics Creation and Usage in Real-time
  • Full Text Search (FTS) Architecture - Tuning
  • Stop Words, Stemmer and Thesaurus For Queries
  • Indexer Program and Query Processor Usage
  • Database Catalogs (FTC) and FDHost.exe
  • Full Text (FT) Indexes for Query Tuning
  • CONTAINS() and FREETEXT() Functions
  • Data Compression - ROW & PAGE Levels

DAY 24: AZURE SQL DATABASE CONFIGURATION

  • DTU : Data Transaction Units : Architecture, Pools
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Elastic Pools & Tier Selection - Recommendations
  • Database Name Identifiers, Naming rules & restrictions
  • Server Names - Locations, Admin Users, Passwords
  • S1/S2/S3 DTU bands and Performance, Storage
  • Firewall Rules and IP Configuration Ranges
  • Azure Dashboard - Metrics, Notification Options
  • Azure SQL Database Collation, Connection Options, Tools
  • SQL Server Management Studio (SSMS) & Visual Studio
  • SQL Server Data Explorer Tool in Azure Cloud
  • Add-On Storage Options. Database Provisioning

Day 21: QUERY TUNING 3 - INDEX MANAGEMENT

  • Index Management and Fragmentation
  • Index Rebuilds, Index Reorganization
  • Database Maintenance Plans (DMP) and Jobs
  • Query Execution Plans, Stats Updates
  • SAMPLED, DETAILED Scans. FillFactor
  • Index Management Options : Page Count
  • Index Usage and Degree of Parallelism [DOP]
  • SQL Profiler Tuning, Tuning Templates
  • Workload Files, Trace Tables in Profiler
  • DTA Tool with Profiler, Trace Tables, PDS
  • DTA with Query Cache, Extended Events
  • Memory Optimized Tables & FileStream
  • Memory Elevated Snapshots at DB Level
  • UPDATE STATS with RECOMPUTE Options

DAY 25 : AZURE SQL DATABASE MIGRATIONS

  • Data Migration Assistant (DMA) Tool
  • Schema Generation and Compatibility Issues
  • Generating Data Scripts and Assessment
  • Generate and Validate Scheams. Migrations
  • Database Scripting Wizard in SSMS
  • Scripting On-Premise Databases in T-SQL
  • Resolving Database Migration Compatibility Issues
  • Export Data Tier and BACPAC Files Generation
  • Migration Options with BACPAC Files
  • Creating Azure Cloud Storage Account
  • BLOB Data File Uploads and DB Imports
  • Creating Storage Containers for BLOB Data
  • Partially Supported and Unsupported Functions
  • Comparing SQL DB OnPrem and Cloud Migrations

Day 22: QUERY TUNING 4 - PERFMON, ISOLATION LEVELS

  • Perfmon Counters and Real-time Tracking
  • Processor, Disk and Memory Counters
  • TPS Counters and Log Space Counters
  • TempbDb Data Space Counters, Thread Count
  • NUMA Nodes, Boosting SQL Priority
  • Processor and IO Affinity - TDS Packets
  • Query Memory and Index Memory Settings
  • LOCKS : Mechanism, Types, Concurrency
  • X, S, IS, IX, U, Sch-M and Sch-S Locks
  • Lock Escalations, Deadlock Graphs, LIVE Locks
  • Isolation Levels - Types and Session Settings
  • ReadCommitted, Read Uncommitted Isolations
  • Serializable, Snapshot, Repeatable Read
  • Read Committed Snapshot Isolation Level
  • Choosing Correct Isolation Levels For OLTP

DAY 26 : Azure SQL DATABASE - QUERY TUNING

  • Azure SQL Server Level Tuning Options
  • Azure SQL Database Level Tuning Options
  • Automated Tuning Options and Peak-Loads
  • Force Plan, Create Index, Drop Index
  • Azure SQL Database Performance Insight
  • Index Recommendations and History Logs
  • IO Metrics, CPU Metrics & Query Statistics
  • In-Memory Tables and Memory Optimization
  • In-Memory Performance - TPS for P15, P12
  • Azure Search Service - Purchasing Plans
  • Indexer Program - RETREIVABLE, SORTABLE
  • FILTERABLE, FACETABLE, SEARCHABLE
  • Searchable Indexes and Index Types - Options
  • Tuning Textual Data, WaterMarks in Azure
  • Bounding Box Model and DTU Scaling Options

MCSA - 70 761 Exam Guidance

MCSA - 70 762 Exam Guidance

 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today
 

SQL Server T-SQL Online Training - Highlights :

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • TWO Real-time Case Studies, One Project
  • Certification Guidance to MCSA Exams
  • Interview Preparation & MOCK Interviews
 
 
  • End-End Database Design & Implementation
  • Detailed SQL Server Architecture, DB Design
  • Query Tuning, Stored Procedures, Linked Servers
  • In-Memory, New Features of SQL Server 2017
  • Azure SQL Database Programming, Sharding
  • In-Memory Tables and Azure Performance Insights
 
Register Today  Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T

 
 
 
 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today