SQL Server Training


SQL Server Training with Real-time Project and Support. This impeccable SQL Server (with T-SQL Queries and Tuning) course is exclusively designed for starters and experienced professionals addressing SQL Server 2014 & 2016 Installation, DB Design, Queries, Joins, CTE, Stored Procedures, Triggers, Remote Queries, Transactions and Tuning options with BLOB Data and XML. Practice Material, Certification, Resume & Interview Guidance are included in this SQL Server Training Course.

 

Trainer : Mr Sai Phanindra (12+ Yrs Exp)


Dur: 4 Weeks (Mon - Fri).

Total Course Fee: INR 6,000/-

Highlights
  • ✔ Completely Practical and Realtime
  • ✔ Theory Material provided in Advance
  • ✔ Highly Interactive and Interesting
  • ✔ Certification Guidance and FAQs
  • ✔ 80% Hands on Training, 20% Theroy part Explanantion

 

SQL Server Training Course Content

Module I: SQL Server & Design, Queries, Joins

Module II: T-SQL Queries & Programming

DAY 1: SQL SERVER (2016 / 2014) INSTALLATION -- Free Demo

  • What is Data? What is Database? File Store Limitations?
  • Why Microsoft SQL Server? Advantages (Technical/Usage)
  • SQL Server - Career Options, Certifications, Projects
  • What is SQL? What is T-SQL? Differences. Why T-SQL?
  • Versions and Editions of SQL Server - Overview
  • Session Wise Plan, Material and Real-time Project Details
  • LAB PLAN - 24x7 LIVE Server (Online Lab) For the Course
  • How to install SQL Server - Step by Step Guidelines
  • SQL Server 2016 Software - Server Installation Steps
  • SQL Server 2016 - Tools Installation and Verification
  • SQL Server 2014 / 2012 Software Installation Guidance
  • H/W & S/W Requirements. Server Configuration Options
  • Instance Types : Default and Named Instances. Instance IDs
  • Service, Authentication and Instance Collation Properties
  • SQL Server Tools - SQL Server Management Studio (SSMS)
  • Client Connectivity Tests, Browsing Servers (Local/Remote)

DAY 2,3: SQL BASICS - DDL, DML, SELECT -- Free Demo

  • Testing Installation, Understanding Server Connection
  • Understanding SSMS Tool. Object Explorer, Server, DB
  • Defining New Sessions for Writing Queries. Session IDs
  • Basic SQL for Beginners. Introducing Databases, Tables
  • What is SQL? Why T-SQL? Basic SQL Queries in SSMS
  • DDL and DML Statements - Creating & Using Databases
  • Table Creation (Basic Level) - Columns and Data Types
  • CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE
  • INSERT / Store Data into SQL Server Tables - Options
  • Single Row and Multiple Row Inserts with NULL Values
  • SELECT Queries. Basic Operators : AND, OR, IN
  • IS, NOT, BETWEEN, UNION, UNION ALL, EXISTS
  • Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases
  • UPDATE Statements and DELETE Statements with Conditions
  • TRUNCATE Statement - DELETE Comparisons, Logging
  • SYSTEM DATABASES - Purpose, Usage. MASTER, MODEL
  • Using Tempdb and Temporary Tabes. Local, Global
  • Synonyms : Purpose. Alternate Object Reference, Queries
  • Understanding BATCH Concepts and TDS Architecture
  • CLIENT - SERVER Architecture (TDS) & Client Statistics
  • SQL Native Client (SNAC) and OLE-DB Providers

DAY 4: DATABASE & TABLE DESIGN

  • SQL Server Databases - Purpose and Design Options
  • SQL Database Architecture - Logical and Physical View
  • Database Properties - Files - Types - Storage Options
  • Data Files : Purpose and Sizing. Detailed Architecture
  • Filegroups : Purpose and Grouping Options. Properties
  • Log files : Sizing, Placement & Detailed Architecture
  • Pages, Extents (Uniform, Mixed). Data Allocation Process
  • Write Ahead Log (WAL) and Log Sequence Number (LSN)
  • Virtual Log File (VLF) and MINI LSN. Operation Audits
  • Database Creation using GUI - Adding Files, Filegroups
  • Database File and Filegroup Options. GUI Limitations
  • Database Creation using T-SQL Scripts. SYNTAX Rules
  • Database with Filegrowth, Autogrowth, MAXSIZE Options
  • mdf, ndf, ldf and Custom Extensions. Planning VLDBs
  • Adding Filegroups and Files. Size, Property Modifications
  • Routing Tables to Database File Groups, Advantages
  • Schemas - Purpose, Creation and Usage with Tables
  • CHAR versus VARCHAR Differences - Type, Size Allocations
  • Database Log Files for DML - Logged, NonLogged Options
  • Default Schema and Default Filegroup for Table Design
  • Data Types, Length, NULLs and Naming Conventions
  • SELECT Queries with Schema on Tables, Column Aliases

DAY 5: CONSTRAINTS and KEYS

  • Constraints and Keys - Ensuring Table Data Integrity
  • Normal Forms - Types, Relational Database (RDB) Design
  • OLTP Database Model & BCNF - Relations with PK / UQ
  • NULL, NOT NULL and Default Nullability for Columns
  • UNIQUE KEY Constraints: Importance, Uniqueness, Nulls
  • PRIMARY KEY Constraint: Properties, Priority, Limitations
  • FOREIGN KEY Constraint: References, Relations & Usage
  • FOREIGN KEY Constraints : Relating Two or more tables
  • CASCADED Foreign Keys and Relations - UPDATE, DELETE
  • CHECK Constraints: Properties, Conditions and Usage
  • CHECK Constraints: Multi Column Checks & Operators Use
  • DEFAULT Constraints: Properties, Usage and Limitations
  • Relations with Tables across Multiple Schemas, Usage
  • Identity Property with / without PRIMARY KEY, Usage
  • Composite Primary Keys & Practical Use. Recommendations
  • Self Referencing Keys & Usage. Using Unicode References
  • Adding / Modifying Constraints, Keys and Data Types
  • Naming Conventions For Constraints, Columns and Tables
  • Normal Forms - Types, Purpose and Usage. With Examples
  • BCNF: Boycee-Codd Normal Form and Practical Usage

DAY 6: JOINS, SUB QUERIES & NESTED QUERIES

  • JOINS - Purpose and Types, Use Case Scenarios
  • JOIN - Types, Queries and Importance of Reports
  • CROSS JOIN in detail. Examples and Conditions @ WHERE
  • INNER JOIN in detail. Examples with WHERE and ON
  • Comparing INNER JOIN with CROSS JOIN for Conditions
  • OUTER JOINS in detail. LEFT, RIGHT and FULL Joins
  • SELF JOINS with INNER / OUTER Joins. Usage Scenarios
  • Working with Self Joins on non key columns, advantages
  • JOINS with more than 2 tables. Syntax, Precedence Order
  • Query Optimization Considerations with Schema References
  • Deciding the best Join Type, Order and Query Options
  • Basic Sub Queries and Joins. Alternate Syntax & Queries
  • Using ON and WHERE for Join Conditions. Working with NULLs
  • Using SubQueries for Self Joins and Outer Joins
  • Working with Nested Queries and Nested Sub Queries
  • Using Sub Queries and Nested Sub Queries with Outer Joins
  • End User Access to SQL Databases - Reporting Tools, Options
  • Looking for More Joins and Queries with Procedures? DAY 16

DAY 7, 8: VIEWS, FUNCTIONS, JOINS, QUERIES

  • VIEWS - Benefits For Data Access, Table Operations
  • Defining Views on Tables - Syntax, Options, Uses
  • Views as Stored SELECT Statements, Data Access
  • SCHEMABINDING and ENCRYPTION Options - Advantages
  • Cascaded Views and WITH CHECK OPTION, Advantages
  • Orphan Views - Scenarios and Realworld Solutions
  • Common System Views For Metadata Access, Object IDs
  • Functions: Types, Purpose and Usage. Return Values
  • Scalar Value Returning Functions - Examples, Usage
  • Inline Table Value Returning Functions - Dynamic Joins
  • Multi-Line Table Value Functions - WHILE Loop
  • Table Variables and Usage with Functions. Table Data Type
  • Variables and Parameters in SQL Server. Usage Differences
  • Dynamic Query Conditions with Functions. Return, Returns
  • Queries with GROUP BY, HAVING, ON & WHERE
  • ROLLUP and CUBE - Sub Totals, Grand Totals, Aggregates
  • ROLLUP of Table Data. Column Aggregations. ORDER BY
  • CUBE on Table Data - Purpose & Usage. Permutations
  • Queries with GROUPING() Option in SELECT, Using HAVING
  • HAVING versus WHERE Conditions - Usage Differences
  • Query Execution Order with Joins, ORDER BY and ROLLUP
  • Important System Functions and Metadata. Object Name, IDs
  • Date and Time Functions, Date Format, Styles and DATEDIFF
  • CASE Statement (with/without Expressions), PIVOT Usage
  • MERGE Statement - MATCHED and NONMATCHED Operations
  • Using Views for Queries and Sub Queries with Functions

DAY 9: STORED PROCEDURES - LEVEL 1

  • Stored Procedures - Purpose, Syntax, Properties and Types
  • Compilation, Precompilation and Query Optimization (QO)
  • Variables - Usage and Data Types in Stored Procedures
  • Parameters - Usage and Data Types in Stored Procedures
  • Stored Procedure Executions - Syntax, Alternate Options
  • Stored Procedures for Data Validations & Missing Identity
  • Stored Procedures for Dynamic SQL Queries. Views & SPs
  • Stored Procedures for Data Reporting. Advantages, Tuning
  • Important System Procedures For Metadata Access. Usage
  • Important Extended Procedures For Application Operations
  • IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT statements
  • Error Handling Techniques in T-SQL: TRY, CATCH, THROW
  • Dynamic Parameters and Variables. Examples with Views
  • Default Parameter Values, Data Types and NULL Values
  • Batch Executions with Stored Procedures. Variants
  • Unicode Data and Dynamic SQL Queries. sysname Data

DAY 10: STORED PROCEDURES - LEVEL 2

  • Stored Procedures for Sub Queries, Dynamic Sub Queries
  • Stored Procedures for Recursive and Nested Queries
  • OUTPUT Parameters in Stored Procedures. Usage Options
  • Common Table Expressions (CTE) and In-Memory - Syntax
  • Row Number and Rank Generation, Sub Queries, Self Joins
  • Stored Procedures for Parameterized CTE (Sub) Queries
  • Using CTE for Table Data Operations - DML & Retrieval
  • CTE for DML and DDL Operations in Stored Procedures
  • Cursors - Benefits, Syntax. Using SProcs with Cursors
  • FORWARD_ONLY and SCROLL Cursors Types. Limitations
  • STATIC and DYNAMIC Cursors Types. ABSOLUTE Fetch
  • LOCAL and GLOBAL Cursor Types & Scope, Reusability
  • KEYSET DRIVEN Cursor Types & Performance Options
  • Embedding Cursors in Procedures and User Functions
  • SPs with Cursors @ Dynamic Data Loads, Data Formatting
  • Memory Limitations with Cursors with SP Recompilations
  • More examples for CTEs and Stored Procedures: DAY 14,15,21

DAY 11: TRIGGERS & TRANSACTIONS

  • Triggers - Purpose and Types. Scope Of Usage
  • DML Triggers - Events, Types and Practical Usage
  • FOR / AFTER Triggers - Syntax, Usage and Importance
  • INSTEAD OF Triggers - Syntax, Usage and Importance
  • INSERTED & DELETED Memory Tables with DML Triggers
  • Memory Usage with INSERTED/DELETED Tables. Usage
  • Triggers for Disabling DML Operations. Trigger Priority
  • Triggers for DML Operation Audits and Data Sampling
  • Triggers for Data Distribution to Multiple Tables / Views
  • Database Level Triggers and DDL Operations - FOR Type
  • Server Level Triggers and DDL Operations - FOR Type
  • Triggers for Data Distribution and JOINS. Value Mapping
  • Looking for more CTEs and Recursive CTEs? DAY 21
  • Need for Transactions, Transaction Scenarios
  • ACID Properties and Transaction Types. Atomic Property
  • EXPLICIT, IMPLICIT Transactions - Query Blocking
  • IMPLICIT Transactions - Usage, Database Settings
  • AUTOCOMMIT Transactions - Advantages, Usage Examples
  • OPEN Transactions and Audits. OPENTRAN commands
  • Nested Transactions and COMMIT / ROLLBACK Rules
  • SavePoint Options with Explicit Transactions, Rollbacks
  • LOCK HINTS : READPAST, NOLOCK, HOLDLOCK - Usage

DAY 12: INDEXES and QUERY TUNING OPTIONS

  • Indexes: Architecture (Page Level), Purpose and Types
  • Clustered Indexes - Architecture, Fragmentation Issues
  • Non Clustered Indexes - Architecture, Column References
  • SORT_IN_TEMPDB, FILLFACTOR and PAD_INDEX Options
  • Execution Plans and Query Optimization (QO) Techniques
  • Execution Plan - Table Scan, Index Scan and Index Seek
  • INCLUDED INDEXES - Purpose, Index Seeks, Query Tuning
  • COLUMNSTORE Indexes - Advantages, Usage Examples
  • COLUMNSTORE Indexes - Limitation @ Filtered Index
  • COLUMNSTORE Indexes and Online Indexes - Memory Options
  • FILTERED Indexes - Sizing Advantages and Limitations
  • ONLINE Indexes and OFFLINE Indexes - UNIQUE Indexes
  • Materialized Views / Indexed Views - Tuning Options
  • Working with UNIQUE Indexes on Tables, Views
  • Query Optimizer (QO) Options for Index Pages, Data Pages
  • Limitations of Indexes - Impact on DML and SELECT
  • Primary Key Index, Composite Indexes and Precautions
  • RID and Index Key Concepts. Index Page - Data Page Arch"
  • Real-world Considerations For Indexes (Tables, Views)
  • Stored Procedures and Recompilations with Indexes

DAY 13: SQL SERVER ARCHITECTURE

  • Client - Server Architecture of SQL Server
  • SQL Server Tools - Connection Options, TDS Packets
  • Protocols : TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client (SNAC) and OLE DB Drivers / Providers
  • ISO - OSI Model of Data Connections, Encrypted Data
  • Query Processing and Query Optimizer (QO) Components
  • SQL Server Architecture For Database Engine, LCM Options
  • Architecture - Query Processor and Storage Engine
  • Architecture - Query Parser, Optimizer, Mini LSN, MDAC
  • Architecture - SQL Engine, SQL Manager and Query Buffers
  • Architecture - Write Ahead Log (WAL), Lazy Writer Threads
  • Architecture - SQLOS Threads and Task Schedulers, CLR
  • SQL Database Architecture - RAID Levels (S/W, H/W)
  • Log Sequence Numbers (LSN) and Time Mapping. Audits
  • Log File Architecture - Virtual Log Files and Usage
  • Log File Architecture - Mini LSN & Degree Of Parallelism
  • DB Catalogs, CLR Integration and MDAC Components
  • LSN Timestamps and MINILSN. Background Threads @ SQL

DAY 14-15: REAL-TIME PROJECT (BANKING)

  • Phase 1: Understanding Project Requirement - Banking
  • Phase 1: Database Design with FileGroups, Schemas
  • Phase 1: Table Design with FileGroups, Schemas
  • Phase 1: Defining Constraints, Relations, Synonyms
  • Phase 1: Design Data Structures for Optimal Performance
  • Phase 2: Views for Data Inserts, Joined Queries
  • Phase 2: Common Reporting Functions, User Access
  • Phase 2: RANK, ROW_NUMBER, DENSE_RANK, PIVOT
  • Phase 2: INSERTS with PIVOT, Calculations, Sub Queries
  • Phase 2: Implement Indexes and Column Store Options
  • Phase 3: End-to-End Implementation - Data Validations
  • Phase 3: Stored Procedures for Dynamic Data Inserts
  • Phase 3: Updatable Views and Triggers for DML, Indexes
  • Phase 3: DML Operations with PIVOT and Pagination
  • Phase 3: ADVANCED, COMPLEX Stored Procedures in T-SQL
  • Phase 3: DB Documentation Tools, Deployment Options
  • Reading Log Files and Data Audits & 3rd Party Tools
  • Transaction Audits and Offline Query Logs for SQL DEVs

Targeted Audience

SQL Server Developers, HADOOP Developers, .NET Engineers and Database / BI Professionals

Skills Required

This SQL Server Training carefully designed with Basic and Advanced Level of Examples with 24x7 LIVE Online Lab. No pre-requisites for this SQL Server Training.

Register Today