VIDEO 0: SQL SERVER 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 2017 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. SQL 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
|
