Azure Sql Server

1. Database

1.1 Create Database

1.2 Use Database

1.3 Temp Database

2.DataTypes (INT(INT, BIGINT, TINYINT SMALLINT based on the length of value),CHAR,BIT,VARCHAR,NVARCHAR,DATETIME,DATE,DECIMAL

3.Table

1.1 Table Name

1.2 Columns and its datatypes

1.3 Data

4.Real time project Table Creation with rules

1.1 Table Name

1.2 Columns and its datatypes

1.3 Integrity Constraints(Primary key, Composite key ,Foreign key, unique key and check constraint and Indexing wherever required as per the business requirement to suite our database maintainace)

1.3 Data

5.Table

1.1 Table Creation

1.2 Alter Table

1.4 Drop Table

1.5 Truncate Table

6.Insert

2.1 Insert the Data into Tables

7.OPERATORS(AND,OR,NOT,=,A union B,A intersection B, Union)

8.Querying using below keywords as,

TOP, Distinct, order by , where, isnull, is not null, between, AND, OR, IN ,(=),NOT IN and (!= or <>),LIKE,EXISTS,ON,CASE,GROUP by, Having

9. Table creation using Query

10. Table joins

11. Functions String Functions Date Functions Aggregate Functions Math Functions Conversion Functions Analytical Functions System Functions

11. Subquery and Corelated subquery

1.3 Table Alias for sub queries

12.Views

13.Temp tables

14. Update

15. Delete

16.T-SQL Declare Variables Variable select Table Variable

17.Stored Procedures.

18.Try catch Transactions or exception handling

19.Complex Query with all above topics in single go.

20.Complex Stored Procedure with all above topics in single stored procedure

Azure Databricks:

Azure Databricks

1.Data engineering with python pandas and pyspark

2.Python Concepts

List

Set

Dictionary

3.Dataframes

Read and write csv files

Select columns from dataframes

Flters on Dataframes

Derived Columns

Rename column name

Data type conversion

4.For loops over rows and columns of Dataframes

5.Move pandas dataframe spark dataframe and performing join select statement

6.Sample project on data engineering

Azure Sql Datawarehouse:

Azure Sql Datawarehouse

1.Sample Datawarehouse Table design

2.Azure Sql Datawarehouse Concepts

MPP(Massive Parllel processing) Architecture

Distributions

Polybase

Differences between Azure Sql and Azure SqlData warehouse Intigrity Constraints Differences Azure SqlData warehouse from Azure Sql Dataypes Differences Azure SqlData warehouse from Azure Sql

3.Stored Pocedures creations

CTAS

External Tables creation

Azure Data Factory:

Azure Data Factory

1.Introduction on ADF

What is ADF

Difference from normal ETL tools

Uses

How Robust it is and Advantages

limitations

2. Resource Creations

Azure Data Factory creation

Azure Storage explorer

Azure Data lake creation

3.Components of ADF

3.1 Connections

3.1.1 Integration Runtime

Azure IR

Selfhosted IR

SSIS IR

3.1.2 Linked Services

Azure BlobStorage

Azure Datalake store gen1

Azure Datalke store gen2

Azure Key valut

Azure Sql Database

File System

Sql Server

Azure Databricks

3.Functions in ADF

Builtin Functions

Date Functions

String Functions

Aggregate Functions

4.Datasets

4.1 Datasets creation on supported sources and targets.

File system

Sql Server

Azure Data lake store gen1 and gen2

AzueSql Data base

Azure Sql Datawarehouse

5.Parameters and Expression writing

Pipeline parameters

Dataset parameters

Dynamic Content expression writing

Expression builder using Functions and parameters

Dynamic Content expression writingusinginterpolation

Expression builder using Functions and parameters

6. Activities Common

6.1 Copy

6.2 Delete

6.3 Set Variable

6.4 GetMetaData

6.5 Lookup

6.6 Stored Procedure

7.Filter Activities Filter Foreach IF condition Switch Until

8.Dataflows Supported Dataflows

9.Pipelines Pipelines creation with all mixing activities and Realtime flow of implementations

10.Triggers

Scheduled trigger

Event based trigger

Windows trigger

End to End Azure BI stack Project implementation — 7 days