SQL

Part 1: INTRODUCTION OF DBMS AND INSTALLATIONS PROCESS

  • Data, Databases and RDBMS Software’s
  • Advantages of SQL Server
  • Uses, Database Engine Many Component and OLTP Databases
  • BI Tool Components & Data Science Components
  • Uses of SQL, Real-time Usages, SQL versus Microsoft T-SQL [MSSQL]
  • Instance Name and Server Name, Features, Collation, Admin Users
  • Real-time Projects Included, Job Responsibilities; Versions and Editions of SQL Server
  • SQL Server and SSMS Installation, SQL Server - Career Options
  • SQL Server Pre-requisites: SQL Server 2019 / 2017/2014/2012 Installations

PART 2: BASICS SQL AND SSMS TOOL

  • SSMS, Different Connections (Local and Remote)
  • Sys Databases, Creating Objects with GUI
  • Insertions of data, Limit, Storage capacity
  • Sys Files, Commands with script, Select
  • Scanning and Retrieving of data(Basic Level)

PART 3: DATABASE CREATIONS AND COMANDS (DDL AND DML)

  • Creating DB, Entitles (Tables)
  • All about “SELECT”
  • Diff Data types in SQL, Select with simple and where conditions,
  • Different kinds of Operators OR, AND, IN, LIKE, NOT LIKE, Wild cards, IS, IS NOT OPERATORS, TOP AND DISTINCT keywords and Many more.

PART 4: EXPORT AND IMPORT (Different sources) AND SCHEMA, Different Modifications

  • Update, Delete and Truncate, Drop, Removing Schema,
  • Tables and Databases,
  • DDL Commands, Creation, Transfer of Table
  • GO Statement and Naming 2p 3p, Wizard(Export and Import)
  • Text Data types char and varchar
  • Nvarchar, Real time Uses, Batches, Real Time uses
  • All are based on Real Cases

PART 5: KEY CONCEPTS

  • Data Integrity
  • NULL, NOT NULL Property, PK, FK, CHECK , CK CONSTRAINTS, Default
  • ER DIGRAMS
  • Table Relation with Key, Identity property, increment
  • Adv. In Real Scenarios Cascading referential integrity constraint
  • Identity Column, Retrieving Identity Column values

PART 6: DATABASE OBJECT WITH BASIC PROCEDURE (Case 1)

  • Different types of Views with Real time examples,
  • Execution of views, UDF, Procedures, procedure with parameters
  • UD procedure and sys defined procedure like sp_
  • diff between procedure
  • Function and views with real Examples and performance wise comparisons
  • Advantages of stored procedures
  • Real Time Case Studies and Interview Preparations

PART 7: DIFF TYPES JOIN CONCEPT AND T-SQL QUERIES (CASE 1)

  • INNER AND OUTER JOINS WITH WHERE AND ON KEYWORDS,
  • FULL OUTER JOIN REAL TIME EXAMPLES,
  • MERGE JOINS, DIFFRENCES WITH JOINS SMALL AND BIG TABLES,
  • CROSS JOINS, ADVANCE JOINS

PART 8: GROUP BY AND T-SQL QUERIES (CASE 1)

  • Group by all clauses, Aggregate Functions,
  • GROUP BY Queries with Where Clause
  • Difference between where and having clause and uses with real cases
  • Grand Total, and all Roll Up and CUBE Function,
  • UNION AND UNION ALL, GROUPING Functions
  • Row status

PART 9: T-SQL QUERIES (CASE 2)

  • Joining with two and three different tables
  • Alias, where and on condition
  • Group by and Sub-Queries, Date and Time Function
  • Cast and convert, Datediff Function
  • String Functions, Trim, left, Right, Reverse, and Replace

PART 10: T-SQL QUERIES (CASE 3)

  • Complex Joining Tables in T-SQL; Using Joins with Sub Queries;
  • Using Joins with Nested Sub Queries
  • IIF ( ) and CASE Statement, CASE study in Joins
  • Joins in Group By, Rollup, Cube, Replacing Nulls
  • Is null, Coalesce Function
  • MERGE Statement For all commands like DML
  • Joins WHEN MATCHED & NOT MATCHED
  • DML Operations MERGE Statement
  • Row Number Generation in T-SQL
  • ROW_NUMBER () and RANK () Queries
  • DENSE_RANK, Sequence Identification; PARTITION

PART 11: STORED PROCEDURES (CASE 2)

  • Table valued parameters
  • IN and OUT Parameters
  • Dynamic Data with SPs
  • Variables (@variable)
  • Common Table Expression
  • Replacement @CTE with self Joins and
  • Sub-Queries
  • Recursive CTE

PART 11: STORED PROCEDURES (CASE 3)

  • Different Types of riggers (All DML AND DDL)
  • Views, Updatable Views
  • Uses of Triggers with Joins
  • Difference between procedures
  • views
  • Triggers, Cursors
  • Different kinds of Cursors
  • While Loop
  • Nested procedures

PART 12: TRANSACTIONS IN T-SQL

  • Different Kinds of Transactions (ACID)
  • Begin, Commit and Roll Back Transaction
  • Explicit and Implicit Transaction
  • Locking Process
  • Blocking and Deadlocking
  • No Lock
  • Real Time Case Studies, Project and Interview Preparations

PART 13: NORMAL FORMS IN T-SQL

  • Normal Forms
  • BCNF, 1NF, 2NF, 3NF, 4NF
  • Different kinds of Dependencies (Transitive) in Normalization Process
  • CK Keys

PART 14: FUNCTIONS (Part 2)

  • Different Types of Functions
  • Inline Function
  • Multi-Dimensional Function with Real case Examples
  • Pivot Function with Joins
  • Cascading with Delete and Update with Real Examples
  • BULK Insert, Different Files JSON File
  • XML with Different properties XML Path, Auto, Etc.
  • Temp Table with Real Examples

PART 15: INDEXING IN DATABASES

  • Architectures of Databases
  • Different kinds Files(.Ldf, .mdf)
  • Log Files, File Groups Etc.
  • Different INDEXES: Clustered and Non-Clustered
  • Online Indexes, Unique Indexes
  • Metalized Views
  • B-Tree Structure
  • Real Time Case Studies, Project and Interview Preparations

Module 2:- Query Optimization and Performance Tuning

CASE 1:- Method of Partitions and Audits

  • How to Edit Long Running Queries with DMVs, DMFs,
  • Monitor Tool
  • Plan Handling, Settings,
  • Uses of Partition and Non-Partitioning of Tables
  • Compression Method

CASE 2:- HOW TO MANAGE INDEXES

  • Statistics (Benefits, Creations, Updates, Management)
  • Fragmentation, Rebuild of Indexes
  • DMP
  • Online, Resume Option, Scanning Process

CASE 3:- FULL TEXT SEARCH (FTS) AND TABLE MEMORY

  • FTS Method,
  • Tuning Advisor
  • Database Catalog
  • ONLINE, PAUSE, RESUME, File Stream, File Group
  • Temp Table, Versioning of System

CASE 4:- LOCKING METHODS AND DIFFERENT LEVEL OF ISOLATIONS

  • Different Types of Locking
  • Shared Lock, Intent Lock, Schema Lock
  • Query Blocking
  • Read Committed
  • Read Uncommitted
  • Repeatable Read
  • Deadlock Method
  • Catching Blocking in Profiler Tools
  • Perfmon Tools
Download Syllabus Here