Data Analytics with SQL and ETL Tool

The objective of this course is to Use ETL tools for extraction, transformation, and loading of data. Use SQL to query and analyze the prepared data. SQL helps in generating reports, performing ad-hoc queries, and visualizing data insights. Integrate SQL queries with reporting and visualization tools to create dashboards and reports that provide actionable insights.

Target Audience:

Students who has done B.TECH (CS/IT/), BCA , B.Sc (IT), MCA ,M.Sc (CS), M.SC(IT) and professionals seeking future in IT Industry.

Syllabus:

• Module - 1   Schema Definition and Management
• Unit 1 : DDL Operations
• Over view of Schema Definition and Management
• Implementation of DDL Operations (Create, Alter, Drop, Rename, Truncate)
• Module - 2   Data Query and Modification
• Unit 1 : DML Operations
• Over view of Data Query and Modification
• Implementation of DML Operations (Insert, Update, Delete, Select)
• Unit 2 : Working with Constraints
• Introduction of Constraints
• Practical approach to Constraints (Not Null, Unique Key, Primary Key, Foreign Key, Check, Adding and Dropping a Constraint)
• Module - 3   SQL Statements
• Unit 1 : Working with Operators
• Incorporating Column Alias to columns
• Introduction and implementation of Operators (Concatenation, Arithmetic, Comparison, Logical)
• Sorting rows by ORDER BY Clause
• Module - 4   Relational linking and Data Integration
• Unit 1 : Relational linking in SQL
• Basics of Data Integration
• Practical approach of Data Integration using Joins (Equijoins, Non-Equijoins, Left Outer Joins, Right Outer Joins, Full Outer Joins)
• Unit 2 : Working with Views and Index
• Get Verse with Views
• Index
• Module - 5   Functions and Subquery used in Data Analytics
• Unit 1 : Subquery in SQL
• Overview of Subquery
• Subquery in generating reports in Data analytics
• Group by and Having Clause
• Unit 2 : Functions in SQL
• Basic concepts of functions
• Functions Utilized in Data Analytics Reports
• Single Row Functions
• Character Functions
• Number Function
• Date Functions
• Conversion Functions
• Aggregate Functions
• Creating User define function in oracle
• Module - 6   Introduction to Pentaho Data Integration and BI
• Unit 1 : Understanding Business Intelligence
• Overview of Business Intelligence (BI)
• Introduction to Pentaho suite and its components
• Module - 7   Pentaho Data Integration
• Unit 1 : Introduction to ETL Tool
• ETL (Extract, Transform, Load) processes
• Overview of Pentaho Data Integration (PDI) and its importance
• Working with Spoon: Pentaho’s ETL Designer
• Unit 2 : Performing ETL Operations
• Extracting data from various sources (databases, files, etc.)
• Loading data into different targets (databases, files, etc.)
• Data transformations- 1: Sorting, Sequence
• Data transformations-2: Calculator, Concatenation
• Data transformations- 3: Splitting, number range, String operation
• Data transformations- 4: CSV, Merge
• Module - 8   Practical Application Of Data Analytics with SQL and ETL Tool
• Unit 1 : Application
• Apply learned skill to a practical project using real world business data.