SQL Advance

Syllabus

• Data Governance and Compliance: Explore SQL-based tools and techniques for implementing data governance policies, ensuring data quality, enforcing regulatory compliance (e.g., GDPR, HIPAA), and auditing data access and usage.

• Advanced Analytical SQL: Delve into advanced SQL techniques for analytics, including window functions, recursive queries, time series analysis, statistical functions, predictive analytics, sentiment analysis, and natural language processing (NLP) using SQL.

• Advanced SQL Tuning and Optimization: Learn advanced techniques for SQL tuning and optimization, including query rewriting, index optimization, database partitioning, parallel query execution, and database workload management.

Prerequisites:

• Completion of SQL Intermediate

• Understanding of database normalization

• Experience with SQL functions (string, date, math)

• Basic understanding of indexing

• Materialized Views: Materialized views are precomputed result sets stored as tables. They can improve query performance by caching the results of complex queries and refreshing them periodically or on-demand.

• Online Analytical Processing (OLAP): OLAP involves querying and analyzing multidimensional data from data warehouses or data marts. Learn about OLAP concepts like cubes, dimensions, measures, and different OLAP operations such as slicing, dicing, drilling, and pivoting.

• Data Mining: Explore SQL extensions and functions for data mining tasks such as clustering, classification, regression, and association rule mining. Many databases provide built-in functions or libraries for performing data mining tasks directly in SQL.

• Spatial Data Analysis: If your applications involve spatial data (e.g., geographic information systems), learn about SQL extensions for handling spatial data types, geometric operations, and spatial indexing techniques for efficient spatial queries.

• Temporal Data Modeling: Dive deeper into temporal data modeling techniques for managing time-varying data, including temporal tables, system-versioned temporal tables, application-time period tables, and bitemporal tables.

• Database Internals: Gain a deeper understanding of how databases work under the hood, including topics like query optimization, query execution plans, indexing strategies, buffer management, locking mechanisms, and transaction processing.

• In-Memory Databases: In-memory databases store data primarily in system memory rather than disk storage, resulting in faster data access and query performance. Learn about in-memory database architectures, data durability, and best practices for using in-memory databases.

• Data Warehousing Concepts: Deepen your understanding of data warehousing concepts such as dimensional modeling, star schema, snowflake schema, fact tables, dimension tables, slowly changing dimensions (SCDs), and data mart design principles.

• Columnar Databases: Explore columnar database architectures optimized for analytical workloads. Learn about advantages such as better compression, faster query performance, and efficient analytics on large datasets.

• NoSQL Databases: While SQL is the standard language for relational databases, explore NoSQL databases like document stores (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores (e.g., Cassandra), and graph databases (e.g., Neo4j) to understand their data models, query languages, and use cases.

• Parallel and Distributed SQL: Learn about SQL extensions for parallel and distributed processing, enabling efficient querying and analysis of large datasets across distributed computing environments. Understand concepts like sharding, replication, data partitioning, and distributed query execution.

• Data Virtualization: Data virtualization allows users to access and query data from multiple disparate sources (e.g., databases, APIs, files) as if it were a single, unified database. Learn about SQL-based data virtualization platforms and techniques for querying virtualized data sources.

Python Advance

Syllabus

• Basic Concepts of Data Visualization : Basic concepts, Plotting Graphs, Customization

Prerequisites:

• Completion of Python Intermediate

• Proficiency in intermediate Python concepts (classes, modules, file I/O)

• Understanding of more complex data structures (sets, tuples)

• Basic knowledge of algorithms and problem-solving

• Numpy Module : Install numpy module

  • Numpy Arrays : N-dimensional arrays. One dimensional, two dimensional, three dimensional arrays and so on

  • Array creation : Mostly used functions to create arrays using built-in functions

  • Random sampling in Numpy : Some of the widely used functions to generate data randomly and draw samples from various distributions

  • Array Attrributes and Methods : Set of attributes and methods to simplify data analysis process

  • Array Indexing and Iterating : Indexing and Subsetting. Boolean Indexing. Iterating over arrays

• Filtering Pandas DataFrame : filtering dataframe with some logical functions

• Iterating Pandas DataFrame : Merge, Append and Concat Pandas Data frame

• Pandas Installation : Installing with pip, Installing with Conda environments, Testing Pandas installation

  • Problems solved by Pandas : List of problems solved by Pandas

  • Pandas Series : Simple operations with Panda Series

• Pandas DataFrames : How to create and manipulate a DataFrame

• Importing data in Pandas : How to read several data formats using Pandas DataFrame

  • Importing data from csv file : functions to read csv file

  • Customizing Pandas import : Depending upon requirement, importing specific rows from a csv file

  • Importing data from Excel file : How to read excel file data and import

• TimeSeries in Pandas : Tools to work with series or dataframe indexed in time

  • Indexing Pandas TimeSeries : using date_range() method, we can create a time range with a certain frequency

  • Resampling Pandas TimeSeries : To pass the frequency from one to another

Power BI Advance

Syllabus

Prerequisites:

• Completion of Power BI Intermediate

• Proficiency in creating complex data models

• Understanding of DAX (Data Analysis Expressions) for advanced calculations

• Familiarity with advanced data visualization techniques

• Experience with Power Query for data transformation

• Power Q&A : Describe Power Q&A. Why Power BI Q&A

• Best Practices : Design best practices for Power Q&A

• Data Binding : Define Data Binding

• Define Formatting : Define Formatting in Data Visualizations

• Data Insights : Power BI adapter

  • Export Analytics Queries : Various methods of Export analytics Queries

  • Continuous export : Continuous export of data from Power BI to other destinations

  • Stream Analytics : Define Stream Analytics

• Hands On : Explore Power BI Q&A

• Visual Life Cycle : Explain the Visual Life-Cycle

• Developer Tools : Explain Developer Tools

• Advance Functions : Use functions like Init, Update and Destroy

• Hands On : Use Power BI Visuals Gallery