SQL Server & T-SQL (DEV) Video Training at SQL School Training Institute

[Sql_School_Training]

SQL Server & T-SQL (DEV) Video Training at SQL School Training Institute

SQL Server T-SQL (DEVELOPER) course includes Database Fundamentals, Installation, In-depth DB and Table Design, Detailed Constriants, Joins, Views, Functions including Basic to Advanced Stored Procedures, Transactions, Triggers, JSON & Query Tuning with one REAL-TIME PROJECT. Each video includes Study Material, Lab Works and Tasks & Solutions with 24x7 LIVE Server Access. Register for T-SQL Video Training

 

T-SQL Video Training How it works?

---

HIGHLIGHTS
Real-time, Practical Training Sessions
Acessible 24x7 - Mobile/PC/Tab
Study Material and Lab Work
ONE Real-time Project Included
Trainers for doubts clarifications
Service payable in TWO Installments

Trainer : Mr. Sai Phanindra T (11+ Yrs EXP). Profile

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

Brouchure Download 

 

Module I: SQL Server & Design, Queries, Joins	Module II: T-SQL Queries, Tuning & Programming
VIDEO 0: 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)	VIDEO 8: 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. Advantanges, 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
VIDEO 1: SQL BASICS - DDL, DML, SELECT -- Free Demo Testing Installation, Understanding Server Connection 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 Issues with Digital Data into Characters. Missing Values INSERT / Store Data into SQL Server Tables - Options Single Row and Multiple Row Inserts with NULL Values SELECT Queries and Basic Operators : IN, BETWEEN IS, UNION, UNION ALL, Other Basic SQL Operators UPDATE Statements with / without Conditions. SET DELETE Statements with Conditions. Logging Options TRUNCATE Statement - DELETE Comparisons, Logging SYSTEM DATABASES - Purpose and Importance. Resource CLIENT - SERVER Architecture (TDS) & Client Statistics SQL Native Client (SNAC) and OLE-DB Providers	VIDEO 9: 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 Using Recursive CTEs and Self Joins with Stored Procedures Precautions for Recursive CTEs - Performance Impact Query Tuning Operations with CTEs. Query Store Options CTE Advantages and Limitations - Precompilations Dynamic SQL Queries with Parameters and Variables Cached Plans and Memory Store for Stored Procedures RECOMPILE Options and ENCRYPTION Options - Scenarios Identity Inserts - Manual Sequence. Dynamic Inserts ANCHOR Members and RECURSIVE Members. Termination
VIDEO 2: SQL SERVER DATABASE 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. Dynamic Extensions Planning and Designing Very Large Databases (VLDB) Adding Filegroups and Files. Size, Property Modifications CHAR versus VARCHAR Differences - Type, Size Allocations	VIDEO 10: STORED PROCEDURES - LEVEL 3 SQL Injection Attacks & Vulnerables: Parameter Sniffing Stored Procedure for ReadWrite Parameters - Usage READONLY Parameters, Table Data Type (User Defined) Error Handling with Table Valued Parameters in SProcs Startup Stored Procedures: Configuration, Server Property Server Startup, Auto Log Options with Stored Procedures Extended Stored Procedures - Purpose, Options & Usage Using Extended Stored Procedures with User Procedures Stored Procedures for Dynamic Values, Calendar Data 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
VIDEO 3: TABLE DESIGN & QUERIES Table Design - Creation. Columns - Data Types, Length Routing Tables to Database File Groups, Advantages Schemas - Purpose, Creation and Usage with Tables Table Design using T-SQL Scripts - Syntax, Examples Table Design using User Interface - Usage Options Data Types, Length, NULLs and Naming Conventions BATCH and TRANSACTION Concepts - Insert Examples UNION, UNION ALL Operators. Differences, Row Order CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE SELECT Queries with Schema on Tables, Column Aliases T-SQL Data Types and NULL Values. Computed Columns Database Log Files for DML - Logged, NonLogged Options Comparing DELETE and TRUNCATE Statements - TLog Files T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS Default Schema and Default Filegroup for Table Design Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases Temporary Tables : Purpose and Types. Local and Global Synonyms : Purpose. Alternate Object Reference, Queries	VIDEO 11: TRIGGERS - DML/DDL AUTOMATIONS 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 Bulk Operations, Updatable Views (Indexed) Triggers for Data Distribution and JOINS. Value Mapping Recursive Triggers with Examples. Performance Impact Declarative Referential Integrity with Triggers Real-time Considerations with Triggers - Precautions Stored Procedures with Triggers and Advantages Limitations with Triggers for DDL & DML Operations
VIDEO 4: 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	VIDEO 12: TRANSACTIONS & ISOLATION LEVELS Introduction to Transactions - Types 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 Isolation Levels : Types of Isolation Levels ReadCommitted & Read UnCommitted Isolation Levels Snapshot Isolation, Serializable Isolation Levels ReadCommitted Snapshot Isolation with Tempdb Usage Impact of Isolation Levels with Concurrent Database Users Choosing the Best Isolation Level in OLTP Environment TRY..CATCH..THROW & Error Handling with Transactions Stored Procedures with with Triggers and Transactions Choosing Transaction Type and Lock Hints Real-world Considerations For Transactions
VIDEO 5: 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 JOIN Queries with Options and UNION, UNION ALL Operators 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 A Real-world Case Study understanding Joins & Queries	VIDEO 13: 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)
VIDEO 6: VIEWS - FUNCTIONS (LEVEL 1) 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 Issues with Views For Data Validations - Solutions Cascaded Views and WITH CHECK OPTION, Advantages Orphan Views - Scenarios and Realworld Solutions Common System Views For Metadata Access, Object IDs Views on Multi Level Tables. Joins. Partitioned Views Data Synchronization and Metadata Refresh with Views Functions: Types, Purpose and Usage. Return Values Scalar Value Returning Functions - Examples, Usage Inline Table Value Returning Functions - Dynamic Joins Multi-Line Table Value Returning Functions - Usage Table Variables and Usage with Functions. Table Data Type Variables and Parameters in SQL Server. Usage Differences Dynamic Query Conditions with Functions. Return, Returns SCHEMABINDING and ENCRYPTION Options with Functions	VIDEO 14: 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
VIDEO 7: FUNCTIONS - QUERIES - VIEWS (LEVEL 2) 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 SOUNDEX, DIFFERENCE, CASE, ISNULL, COALESCE Functions CAST, CONVERT, TRY_PARSE, ROW_NUMBER, RANK Functions PATINDEX, CHARINDEX,RTRIM/LTRIM, REVERSE Functions CASE Statement (with/without Expressions), PIVOT Usage MERGE Statement - MATCHED and NONMATCHED Operations Miscellaneous System Functions and Dynamic Conditions Using Views for Queries and Sub Queries with Functions Real-time Case Study on Online Medicare Project - Joins, Functions, Sub Queries	VIDEO 15: REAL-TIME PROJECT (BANKING) End - to - to End Project Implemetation 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 2: Views for Data Inserts, Joined Queries Phase 2: Common Reporting Functions, User Access Phase 2: Queries for PIVOT, DENSE_RANK, PARTITION BY Phase 2: INSERTS with PIVOT, Calculations, Sub Queries 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 3rd Party Tools - Dell Litespeed for SQL Server 2014/2016 Reading Log Files and Data Audits & 3rd Party Tools Transaction Audits and Offline Query Logs for SQL DEVs