SQL Azure Development online Training
Ch 1: SQL SERVER INTRODUCTION
Data, Databases and RDBMS Software
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
Versions and Editions of SQL Server
SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Microsoft SQL Server – Career Options
SQL Server Components and Usage
Database Engine Component and OLTP
BI Components, Data Science Components
ETL, MSBI and Power BI Components
Course Plan, Concepts, Resume, Project
24 x 7 Online Lab for Remote DB Access
Software Installation Pre-Requisites
2: SQL SERVER INSTALLATIONS
System Configuration Checker Tool
Versions and Editions of SQL Server
SQL Server and SSMS Installation Plan
SQL Server Pre-requisites : S/W, H/W
SQL Server 2016 / 2017 Installation
SQL Server 2019 Installation
Instance Name and Server Features
Instances : Types and Properties
Default Instance, Named Instances
Port Numbers, Instance Differences
Service and Service Account Use
Authentication Modes and Logins
Windows Logins and SQL Logins
FileStream and Collation Properties
DBEngine and Replication Components
Ch 8: Group By, T-SQL Queries Level 2
GROUP BY Queries and Aggregations
Group By Queries with Having Clause
Group By Queries with Where Clause
Using WHERE and HAVING in T-SQL
Rollup : Usage and T-SQL Queries
Cube : Usage and T-SQL Queries
UNION and UNION ALL Operator
EXISTS Operator, Query Conditions
Sub Queries and Alternatives to Joins
Using Joins with Group By Queries
Using Joins with Nested Sub Queries
Sub Queries with Joins and Group By
Using UNION and UNION ALL in Queries
Nested Sub Queries with Group By, Joins
Comparing WHERE, HAVING Conditions
Ch 14: STORED PROCEDURES – Level 3
DML Triggers and DDL Triggers
FOR and INSTEAD OF Triggers
Magic Tables : Inserted, Deleted
Views on Tables – SCHEMABINDING
ENCRYPTION and CHECK OPTION
Cascaded Views, Encrypted Views
Updatable Views, Joins with Triggers
Stored Procedures @ Triggers, Views
Cursors – Benefits, Cursors in SProcs
ForwardOnly, Scroll & Local Cursors
Static, Dynamic & Global Cursors
Keyset Cursors and @@FetchStatus
Nesting of Stored Procedures – Dynamic
Data Formatting and WHILE Loops
Using Temporary Tables for Formatting
Ch 3: SSMS Tool, SQL BASICS – 1
SQL Server Management Studio
Local and Remote Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases
Creating Databases : Files [MDF, LDF]
Creating Tables in User Interface
Data Insertion & Storage. Limitations
SQL : Purpose and Real-time Usage
SQL Versus T-SQL : Basic Differences
DDL, DML, SELECT, DCL and TCL
Creating Tables using SQL Scripts
Data Storage, Inserts – Basic Level
Table Data Verifications with Select
SELECT Statement for Table Retrieval
Ch 4: SQL BASICS – 2
Creating Databases & Tables in SSMS
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT Statement @ Data Retrieval
SELECT with WHERE Conditions
Batch Concept and Go Statement
AND and OR Operators Usage
IN Operator and NOT IN Operator
Between, Not Between Operators
LIKE and NOT LIKE Operators
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
Logged and Non-Logged Operations
ADD, ALTER and DROP Columns
ALTER & DROP Table Statements
Ch 5: SQL Basics – 3, T-SQL INTRO
Database Objects : Tables and Schemas
Schemas : Group Tables in Database
Schemas : Security Management Object
Creating Schemas & Batch Concept
Using Schemas for Table Creation
Data Storage in Tables with Schemas
Data Retreival and Usage with Schemas
Table Migrations across Schemas
Import and Export Wizard in SSMS
Data Imports with Excel File Data
Performing Bulk Operations in SSMS
Temporary Tables : Real-time Use
Local and Global Temporary Tables
# and ## Prefix, Scope of Usage
Session Level, Connection Level Use
Ch 6 : CONSTRAINTS,INDEXES BASICS
Constraints and Keys – Data Integrity
NULL, NOT NULL Property on Tables
UNIQUE KEY Constraints: Importance
PRIMARY KEY Constraint: Importance
FOREIGN KEY Constraint: Importance
REFERENCES, CHECK and DEFAULT
Candidate Keys and Identity Property
Database Diagrams and ER Models
Relationships Verification and Links
Indexes : Basic Types and Creation
Index Sort Options, Search Advantages
Clustered and NonClustered Indexes
Primary Key and Unique Key Indexes
Need for Indexes – working with Keys
Ch 7: JOINS, T-SQL Queries : Level 1
JOINS – Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Left Outer Joins with Example Queries
Right Outer Joins with Example Queries
FULL Outer Joins – Realtime Scenarios
Join Queries with “ON” Conditions
Join Unrelated Tables in SQL Server
NULL, IS NULL Operators in Joins
CROSS JOIN and CROSS APPLY
CROSS JOIN Versus CROSS APPLY
One-way & Two Way Data Comparisons
Important Join Queries in T-SQL
Join Options: Merge, Loop, Hash
Ch 8: Group By, T-SQL Queries Level 2
GROUP BY Queries and Aggregations
Group By Queries with Having Clause
Group By Queries with Where Clause
Using WHERE and HAVING in T-SQL
Rollup : Usage and T-SQL Queries
Cube : Usage and T-SQL Queries
UNION and UNION ALL Operator
EXISTS Operator, Query Conditions
Sub Queries and Alternatives to Joins
Using Joins with Group By Queries
Using Joins with Nested Sub Queries
Sub Queries with Joins and Group By
Using UNION and UNION ALL in Queries
Nested Sub Queries with Group By, Joins
Comparing WHERE, HAVING Conditions
Ch 9: JOINS, T-SQL QUERIES Level 3
Cast, Convert, DateAdd, DateDiff Functions
Date & Time Styles, Data Formatting
Using Date and Time Formats in Queries
String Functions: SUBSTRING,REPLICATE
CHARINDEX, PATINDEX, LEFT, RIGHT
LEN, STUFF, LTRIM, RTRIM, REVERSE
DIFFERENCE, SOUNDEX, STRING_SPLIT
WHEN MATCHED and NOT MATCHED
Incremental Loads with MERGE Statement
IIF(), CASE with WHEN and ELSE, END
FETCH – OFFSET, NEXT ROWS, Order By
Using PIVOT Function and FOR Values
ROW_NUMBER() and RANK() Queries
Dense Rank and Partition By Queries
Ch 10: View, SPs, Function Basics
Views : Types, Usage in Real-time
System Predefined Views and Audits
Listing Databases, Tables, Schemas
Functions : Types, Usage in Real-time
Scalar, Inline and Multi-Line Functions
System Predefined Functions, Audits
DBId, DBName, ObjectID, ObjectName
Variables & Parameters in SQL Server
Procedures : Types, Usage in Real-time
User & System Predefined Procedures
Parameters and Dynamic SQL Queries
Sp_help, Sp_helpdb and sp_helptext
sp_pkeys, sp_rename and sp_help
Important System Objects and Metadata
When to use Which Database Objects
Ch 11: Triggers & Transactions
Triggers – Purpose, Real-world Usage
FOR/AFTER Triggers – Real time Use
INSTEAD OF Triggers – Real time Use
INSERTED, DELETED Memory Tables
Using Triggers for Data Replication
Enable Triggers and Disable Triggers
Database Level, Server Level Triggers
Auditting Triggers and Real World Use
Transactions : Types, ACID Properties
Transaction Types and AutoCommit
EXPLICIT & IMPLICIT Transactions
COMMIT and ROLLBACK Statements
Open Transaction Scenarios & Cause
Query Blocking Scenarios @ Real-time
NOLOCK and READPAST Lock Hints
Ch 12 : ER MODELS, NORMAL FORMS
Normal Forms for Entity Relationships
First, Second, Third Normal Forms Usage
Boycee-Codd Normal Form : BNCF : Usage
4 NF, EKNF, ETNF. Functional Dependency
Multi-Valued, Transitive Dependencies
Composite Keys and Composite Indexes
1:1, 1:M, M:1, M:M Relationship Types
Self Referencing Keys and Self Joins
Adding NOT NULL Property to Columns
Adding Primary Key to Existing Tables
Adding Foreign Key to Existing Tables
Synonyms : Creation and Real-time Use
Using Synonyms in Self Join Queries
Cascading Keys. UPDATE/DELETE Types
Ch 13: STORED PROCEDURES Level 2
Table Valued Parameters (TVP)
ReadOnly Parameters, Stored Procedures
Output Parameters, Stored Procedures
User Data Types and Real-time Use
Dynamic Data Insertions with SPs
Table Cloning, Inserts @ Table Variables
SQL Injection Attacks – Precautions
CTE : Common Table Expressions
Real-time Scenarios with CTEs – Usage
ROW_NUMBER() with CTE Queries
Using CTEs for Avoiding Self Joins
Using CTEs for Avoiding Sub Queries
Recursive CTEs and ANCHOR Element
Termination Checks in Recursive CTEs
Ch 14: STORED PROCEDURES – Level 3
DML Triggers and DDL Triggers
FOR and INSTEAD OF Triggers
Magic Tables : Inserted, Deleted
Views on Tables – SCHEMABINDING
ENCRYPTION and CHECK OPTION
Cascaded Views, Encrypted Views
Updatable Views, Joins with Triggers
Stored Procedures @ Triggers, Views
Cursors – Benefits, Cursors in SProcs
ForwardOnly, Scroll & Local Cursors
Static, Dynamic & Global Cursors
Keyset Cursors and @@FetchStatus
Nesting of Stored Procedures – Dynamic
Data Formatting and WHILE Loops
Using Temporary Tables for Formatting
Ch 15: XML,BLOB,FUNCTIONS Level 2
Functions : Types, Real-world Usage
Scalar Value Returning Functions
Inline Table Value Functions
Multi-Line Table Value Functions
WHILE Loops and Iteractions in T-SQL
Table Variables Usage in T-SQL
Data Type Conversions with Functions
Composite Keys , Computed Columns
XML AUTO, XML RAW and XML PATH
Using BULK INSERT & BULKCOLUMN
OPENROWSET For Data Import, CAST
XML Options in T-SQL Queries, Joins
XML AUTO, XML RAW and XML PATH
JSON Files – Data Import into SQL DB
Ch 16: Server, DB Architecture
Server Architecture and Protocols
Database Engine and Query Processor
Parser, Optimizer, SQL & DB Manager
Storage Engine Components, SQL OS
File Manager and Database Files
Transaction Services, Buffer Manager
Lock Manager, IO Manager, MDAC
CLR, WAL, Lazy Writer, Checkpoint
Database Architecture – Data Files
Database Architecture – Log Files
Primary (mdf), Secondary Files (ndf)
Filegroups Usage, ReadOnly Filegroups
Database Files : Size and Location
Pages, Extents. Uniform, Mixed Extents
Transaction Log File [LDF], LSN, VLF
Real-time Case Study – 1 (Sales & Retail)
Objective : DB Design, Table Design, Relations
Involves Purchases, Products, Customers
and Time Data with Various Data Types.
Real-time Case Study – 2 (Sales & Retail)
Objective : Query Writing, Excel Integration
Excel Pivot Tables, Pivot Charts,
Data Formatting, ODC Connections & Labelling
For More: Online Training
India|US|UK|Canada|Australia|Germany|Philippines|New Zealand|Switzerland
Mumbai|Kolkata|Bangalore|Chennai|Kerala|Pune|Hyderabad|Lucknow|New Delhi
SQL Azure Development online Training
