SQL Tutorial

Course outline

1. 1SQL Basics

   • What is a SQL Statement and types of SQL statements
   • SQL Server Data type
   • Understanding Default Size of VARCHAR in SQL Server
   • What are Constraints in SQL?
   • Normalization in Databases
   • Logical Query Processing Phases in SQL

2. 2Querying Data

   • SQL Logical Operators with Examples
   • ANY and ALL operators used in SQL
   • Filtering the data with TOP and OFFSET-FETCH In SQL
   • Order by index number in SQL
   • SQL Server Pagination
   • UNION, UNION All, INTERSECT, EXCEPT used in SQL Server
   • Use of Apply(Cross and Outer) operator in SQL server
   • Common Table Expressions (CTEs) in SQL
   • Window Aggregate Functions in SQL
   • Working with Multiple Grouping Sets in SQL Server
   • Pivoting and Unpivoting Data in SQL

3. 3Modifying Data

   • What is the difference between DELETE and TRUNCATE statements in SQL?
   • SQL MERGE statement
   • Implementing Transactions,Error Handling, and Dynamic SQL in SQL
   • How to use Magic (virtual) table INSERTED and DELETED in SQL server

4. 4Functions

   • What is Functions and Difference between Functions and Stored Procedures in SQL Server
   • SQL Server User-Defined Functions (UDF)
   • What difference between the DATEPART() and DATENAME() functions?
   • How to get first date of given date or month.
   • How to get first date From month and Finyear
   • Dynamic Date Series in SQL Server (Financial Year + Calendar Year Handling)
   • Split full name into first name and last name in SQL
   • How to one series with comma separate(any Delimiter) value split into in rows
   • How to Two series with comma separate(any Delimiter) value split into in rows respectively
   • How to comma separate value by using XML in SQL server

5. 5Views, Procedures and Triggers

   • What is View in SQL server
   • Understanding Stored Procedures in MSSQL
   • sp_executesql in SQL Server
   • Triggers in SQL server
   • Understanding Cursors in +SQL Server: A Complete Guide
   • What is “CREATE TABLE TYPE” in SQL Server?
   • How to use GOTO statement in SQL server
   • WAITFOR in sql server

6. 6Tables, Schema and Storage

   • Added columns in a table by dynamic ways
   • Dynamic CREATE TABLE Script in SQL Server
   • What are System Tables, File Tables, External Tables and Graph Tables in SQL Server
   • SYS and INFORMATION_SCHEMA
   • Get All DataBase Tables Schema into Table/JSON
   • MDF, NDF, LDF, and Filegroups
   • Table Partitioning in SQL Server
   • SQL Server Data Compression: A Complete DBA Guide

7. 7Indexes and Performance

   • SQL Server Index Complete Guide
   • SQL Server index Creation by using table
   • Index Rebuilding vs. Reorganization in SQL Server
   • How to Drop All Non-Clustered Indexes Dynamically in SQL Server
   • SQL Server Index Optimization Automation (Missing & Unused Index Management)
   • Statistics in SQL Server
   • Understanding Locking Hints in SQL Server: NOLOCK, ROWLOCK, and UPDLOCK
   • What is Blocking in SQL, How to solve blocking, and Kill Session
   • SQL Server Deadlock & Performance Monitoring Made Easy
   • SQL Server Optimization Q&A

8. 8System Databases

   • SQL Server System Databases
   • Master Database in SQL Server
   • MSDB Database in SQL Server
   • Model & Resource Database in SQL Server
   • TempDB in SQL Server

9. 9Administration and Security

   • DBA
   • SQL Server Administration – Management Tools Overview
   • SQL Server Management Studio (SSMS) – Complete Beginner Guide
   • SQL Server Configuration Manager
   • Database Permissions in SQL Server

10. 10Backup, Recovery and Replication

    • Database Backup in SQL Server
    • SQL Server Backup & Recovery Complete Guide
    • Log Shipping (Ship Log) in SQL Server
    • What is the Replication in MS SQL server
    • Dynamic Linked Server Management in SQL Server

11. 11Maintenance and Archiving

    • SQL Server Database Shrink Maintenance
    • Data archive in SQL Server
    • SQL Server Data Archiving System
    • SQL Server me Default Constraint Maintenance System (Auto Fix + Logging + Rollback)

12. 12SQL Server Agent and Automation

    • SQL SQL Server Agent: Automation Ka Powerhouse in SQL ServerServer Agent
    • SQL Server Agent Jobs & Schedules – Complete Practical Guide (Step-by-Step)
    • SQL Server Agent Alerts, Operators, Database Mail & Job Activity Monitor

13. 13XML and JSON

    • XML Data – Complete Conceptual Guide (SQL Server Perspective)
    • JSON Data – Complete Conceptual Guide (SQL Server Perspective)

14. 14Import, Export and Utilities

    • Data Import from text/CSV file in SQL Server
    • Search Any Value Across SQL Server Database
    • Find column in the whole database in sql server
    • Generate SQL Server Scripts by Name Pattern
    • Insert <br> tag in SQL text string
    • SQL Server Multi-Level Approval Workflow with Auto Skip Logic

15. 15MySQL and PostgreSQL

    • What difference between between MySQL and MSSQL
    • Functions in MySQL
    • Stored Procedure in MySQL
    • Variables In MySQL
    • Comparison of WHERE Clause Operators in MSSQL vs PostgreSQL
    • Understanding Data Types and Variables in MSSQL vs PostgreSQL
    • Table Types Comparison: SQL Server vs PostgreSQL
    • The ALTER TABLE statement in Microsoft SQL Server (MSSQL) and PostgreSQL

16. 16Interview Preparation

    • SQL Interview Q&A