Jump to content

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


Sql_School_Training

Recommended Posts

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

 

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

 

 

 

SQLSchoolLogo.jpg

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...