SQL

Introduction to SQL

  • The various types of databases
  • Introduction to Structured Query Language
  • Distinction between client server and file server databases
  • Understanding SQL Server Management Studio
  • SQL Table basics
  • Data types and functions
  • Transaction-SQL
  • Authentication for Windows
  • Data control languages
  • Identification and Keywords in T-SQL
  • Drop Table

SQL operators

  • Introduction to relational databases
  • Basic concepts of relational tables
  • Working with rows and columns
  • Various operators used like logical and relational
  • Domains
  • Constraints
  • Stored procedures
  • Understanding the concept of Indexes
  • Understanding the concept of Primary key
  • Understanding the concept of Foreign key
  • Understanding group functions
  • Unique key

Working with SQL –Join, Tables & Variables

  • Deep dive into SQL Tables
  • Working with SQL functions
  • Operators and queries
  • Creation of tables
  • Retrieving data from tables
  • Combining rows from tables using the following
    • Inner
    • Outer Cross
    • Self joins
  • Deploying Operators like
    • Union
    • Intersect
    • Except
  • Creation of Temporary Table
  • Set Operator rules
  • Working with Table variables

Deep dive into SQL Functions

  • Understanding what SQL functions do
  • Understanding the concept of Aggregate functions
  • Scalar functions
  • Functions that work on different data sets like
    • Numbers
    • Characters
    • Strings
    • o Dates
  • Learning Inline SQL functions
  • General functions and duplicate functions

WORKING WITH SUBQUERIES

  • Understanding of SQL Subqueries
  • Tool Support for File Formats
  • Rules of Subqueries
  • The statements and operators with which Subqueries can be used
  • Modification of sub queries using set clause
  • Understanding the different types of Subqueries like Where, Insert, Update, Select, Delete, etc ways to create and view Subqueries
  • Ways to create and view Subqueries

SQL Views, Functions & Stored Procedures

  • Learning about SQL Views
  • Ways of creating, using, altering, dropping, renaming and modifying Views
  • Understanding Stored Procedures
  • Key benefits of it
  • Working with Stored Procedures
  • Error handling
  • Studying user-defined functions

Deep Dive into User-defined Functions

  • Detailed study of user-defined functions
  • Various types of UDFs like Scalar
  • Inline Table Value
  • multi-statement Table
  • what are Stored Procedures?
  • when to deploy Stored Procedures?
  • What is Rank Function?
  • Understanding Triggers, when to execute Triggers?

SQL Optimization& Performance

  • Detailed understanding of SQL Server Management Studio
  • learning what is Pivot in Excel and SQL Server
  • Understanding the concept of XL path
  • Differentiating between Char, Varchar and NVarchar
  • working with Indexes
  • Creation of Index
  • Its Advantages
  • What is Records grouping
  • How Searching works?
  • Sorting concepts
  • Modifying data
  • Creation of clustered indexes
  • Using index to cover queries
  • Index guidelines and Common Table Expression

Design and implement database objects

  • Design and implement a relational database schema
  • Design and implement indexes
  • Distinguish between indexed and included columns
  • Implementing clustered index
  • Designing and implementing views
  • Implementing column store views

Implementing programmability objects

  • Defining table and foreign key constraints
  • Writing Transact-SQL statement
  • Identifying results of Data Manipulation Language (DML)
  • Designing stored procedure components
  • Implementing input and output parameters
  • Implementing error handling
  • Transaction control logic in stored procedures
  • Designing trigger logic
  • Understanding DDL triggers.

Managing database concurrency

  • Implementing transactions
  • Identifying DML statements based on transaction behavior
  • Understanding explicit and implicit transactions
  • Managing isolation levels
  • Identifying concurrency and locking behavior
  • Implementing memory-optimized tables

Optimizing database objects

  • Determining accuracy of statistics
  • Designing statistics maintenance tasks
  • Using dynamic management objects
  • Identifying missing indexes
  • Consolidating overlapping indexes
  • Analyzing and troubleshooting query plans
  • Managing performance of database instances
  • Monitoring SQL server performance