SQL

Understanding SQL
Understanding ANSI SQL
ANSI SQL Data Types
DDL, DML, DQL, DCL, TCL
Data Definition Language : CREATE, ALTER,
RENAME, DROP, TRUNCATE
Data Manipulation Language : INSERT, UPDATE,
DELETE
Data Query Language : SELECT, FETCH FIRST
Data Control Language : GRANT, REVOKE,
Transaction Control Language : COMMIT,
SAVEPOINT, ROLLBACK
Case study
Understanding Constraints and
their Types
Data Integrity
Integrity Constraints
Entity integrity
PRIMARY KEY Constraint
Sequence generators
Referential Integrity
FOREIGN KEY Constraint
Domain Integrity
NOT NULL Constraint
UNIQUE KEY Constraint
CHECK Constraint
User Defined Integrity
Enabling and Disabling Constraints
Case Study
SQL Operators
SQL Operators & their types
Arithmetic operators
Comparison operators
Logical operators
Set operators
Case Study
SQL Functions
ANSI (SQL 99) SQL Functions Classification
Deterministic and Nondeterministic functions
Aggregate Functions and Scalar Functions
String Functions, Mathematical Functions
Miscellaneous Functions (COALESCE & NULLIF)
Nesting of Functions & SQL Expression
Case study
Clauses in SQL
Group By Clause
Having Clause
Order By Clause
Order of Execution of Clauses in SELECT Statement
Case study
Joins and their Types
JOIN & JOIN Style
Theta Style
ANSI Style : JOIN ... ON & JOIN ... USING
CROSS JOIN
INNER JOIN
EQUI-JOIN
NATURAL JOIN
 OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
Case study
Sub-queries
Understanding Subqueries
Advantages of subqueries
Rules of subqueries
Using Subqueries With SELECT, INSERT, UPDATE,
DELETE
Subqueries Types
Scalar Subquery
Single Row Subquery
Multiple Row Subquery
Usage of IN, NOT IN, ALL, ANY, and SOME
Correlated Subqueries
Usage of EXISTS, NOT EXISTS
Difference between Correlated & Non-Correlated
Subquery
Case study
Views and Indexes
Database Objects
What is View?
Advantages of View
Inline View
What is Index ?
Index Architecture : Non-clustered & Clustered
Unique Index
DBMS

Introduction to Database Systems

Need for a database
File- Based Systems
Define database and DBMS
features of the DBMS
usage of database
DBMS Architecture
three-level architecture of DBMS
functions of Database Systems
overall system architecture
Types of Databases
structure of data
process of data access in the various data
models
Overview of Data Model
Role of the Data Model in Application
development.
Benefits of Data Model.
Categories of Data Model
OLTP
Dimensional Modeling
Stages of Data Model
Conceptual Modeling
Logical Modeling
Physical Modeling
Logical Data Model Contents
Entity
Attribute
Relationship
 Notation
Keys-PK, FK,AK etc
Demo on ErwinTool
Creating Entities,Attributes
Creating different types of relationships
Converting Logical to Physical
Model
Steps for logical to physical data model
conversion
Physical Model -Primary Keys & Constraints
Requirement Analysis
The goals of requirement analysis
Points to keep in mind for requirement analysis.
Structured Data Modeling Process
Normalization and
De-Normalization
Why Normalization?
Normalization Forms - First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Why do we need to de-normalize?
Pros & Cons of de-normalization
Specialization & Generalization
What is Specialization & Generalization?
Why do we need Specialization & Generalization?
Rollup & Rolldown Concepts

NOSQL

NoSQL
 Introduction to NoSQL and MongoDB

Importing and Exporting Data

Mongo Query Language

Updating Documents

The Aggregation Framework

Variables in Aggregation Expressions

Schema Validation and Data Modelling

Indexes and Performance

MongoDB Drivers (Python)

DATA WAREHOUSE

Introduction and
Architecture
What is Operational System?
Characteristics of Operational Systems
Need for a Separate Informational System
Information Center
Basics of Data
Warehouse
Data Warehouse: Definition
Data Warehouse: Features,Data,Business
Benefits,Application Areas
Basic Data Warehouse Architecture & Implementation
Data Warehouse: Differences from Operational Systems
Data Marts
Data Marts: Overview
Data Marts: Needs
Data Marts: Features
Data Marts: Types
Advantages of Data Mart
 Disadvantages of Data Mart
Data Warehouse vs Data Mart
Operational Data Store
Operational Data Store Definition
ODS: Needs
ODS: Data
ODS: Benefits
Operational Data Store: Update schedule
ODS Vs Data Warehouse
What is OLAP
OLAP Terminology
Enterprise Data
Warehouse
Enterprise Data Warehouse (EDW)
EDW- “Top Down” Approach
EDW- “Bottom up” Approach

ETL

Introduction to ETL Concepts

What is ETL

ETL Architecture

Transformation Options

ETL Standards

ETL and metadata

FACT and Dimension Tables

SCD I/II/III

ETL for the Data Warehouse

Data Sourcing / Changed Data Capture

Data Transport
 Data Staging

Changed Data Determination

Loading normalized warehouse structures

ETL for the Data Mart

Surrogate key lookup and assignment

Slowly Changing Dimensions - Types 1,2, 3 & 6

Denormalization and impact on ETL

Populating “junk” dimensions using a Cartesian product

Aggregation

ETL for ODS

Real/near time approaches

Data Modeling differences

Overview on Advanced ETL

Indexing (b-tree, bitmap, join indexes, etc)

Forms of Parallelism

RDBMS tuning and ETL

Caching/Partitioning

ETL Tools in the market and their Comparison

UNIX

Introduction to Unix and Basic

Concepts
Overview of Unix Operating
system,Kernel,History
File system basics
Editors
Unix commands
Unix commands Shell scripting
More Unix commands
Introduction to shell scripting
Shell variables,Operators
program flow controls,Functions,sample shell
scripts
Advanced Shell scripting
Command redirection,Job control,Embedded
scripts
Regular expressions,Signals,traps,Other useful
commands

PYSPARK

Spark

Introduction to Spark

Transformations, Actions, RDD, DataSet

Key Value Methods and Caching Data

Distribution and Parallelism

Spark Streaming

Optimization

Data Exploration and Analysis

Transforming and Cleaning Unstructured Data

Summarizing Data Along Dimensions

Broadcasting and Accumulator

Handling Data Apache Spark SQL
and Streaming

Introduction

Querying Data with the DataFrames

Improving Type Safety with Datasets

Processing Data with the Streaming API

Optimizing, Structured Streaming, and Spark 2.x

SPARK

Spark
Programming
Introduction to Spark
Why do we need spark
Installing and using Apache spark
Spark execution model and
architecture
Spark programming model
Structured API foundation
Data sources and sinks
Dataframe and dataset transformations
Aggregations in Spark
Dataframe joins
Alternatives for Spark

DATA PREPROCESSING IN PYTHON

Data Preprocessing in
Python
Data Cleaning
Encoding of the categorical features
Transformations of the numerical
features
Pipelines
Scaling
Principal Component Analysis
Filter-based feature selection
A complete pipeline
Oversampling