SQL Intermediate

Syllabus :

• Partitioning: Learn about table partitioning techniques to improve manageability and performance for large tables. Understand different partitioning strategies such as range partitioning, hash partitioning, and list partitioning.

• Full-Text Search: Explore full-text search capabilities provided by your database system for efficient searching and indexing of textual data. Learn how to create and optimize full-text indexes and perform complex text searches.

• Backup and Recovery: Understand backup and recovery strategies for databases, including full, differential, and transaction log backups. Learn how to create and manage backup schedules, perform backups and restores, and ensure data integrity in disaster recovery scenarios.

Prerequisites:

• Completion of SQL Basics

• Basic understanding of database design (tables, keys)

• Basic knowledge of data types (e.g., INT, VARCHAR)

• Indexing: Understand the concept of indexing and how it improves query performance by allowing faster data retrieval.

• Transactions: Learn about transactions and how they ensure data integrity by grouping SQL statements into atomic units of work.

• Normalization: Learn the basics of database normalization to design efficient and maintainable database schemas.

• Window Functions: Window functions allow you to perform calculations across a set of rows related to the current row, without grouping the rows into a single output row like aggregate functions. Common window functions include ROW_NUMBER, RANK, DENSE_RANK, LEAD, and LAG.

• Common Table Expressions (CTEs): CTEs provide a way to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They make complex queries more readable and maintainable by breaking them into smaller, logical chunks.

• Recursive Queries: Recursive queries, also known as hierarchical queries, allow you to query hierarchical data structures such as organizational charts, file systems, or bill of materials. Recursive queries use a common table expression with UNION ALL to repeatedly join a table to itself until a termination condition is met.

• Advanced Joins: Deepen your understanding of JOIN operations by exploring more complex join conditions, multiple join conditions, and outer join variations. Also, learn about self-joins, where a table is joined with itself.

• Subquery Optimization Techniques: Learn about techniques for optimizing subqueries, such as using EXISTS, IN, or JOIN instead of subqueries where appropriate. Understand the performance implications of correlated vs. non-correlated subqueries.

• Indexes and Performance Tuning: Dive deeper into indexing strategies, including composite indexes, covering indexes, and partial indexes. Learn how to analyze query performance using EXPLAIN and identify opportunities for optimizing query execution plans.

• Database Security: Explore database security concepts such as role-based access control (RBAC), permissions management, encryption at rest and in transit, and best practices for securing sensitive data.

• Stored Procedures and Functions: Learn how to create and use stored procedures and user-defined functions (UDFs) to encapsulate business logic within the database. Understand the benefits of using stored procedures for performance, security, and code reuse.

• Transactions and Concurrency Control: Deepen your understanding of transaction management, isolation levels, and concurrency control mechanisms like locking and optimistic concurrency control. Learn how to handle transactional errors and ensure data consistency in multi-user environments.

• Temporal Data Handling: Explore techniques for handling temporal data, such as managing effective dating (start and end dates), versioning, and temporal querying using features like temporal tables or date range queries.

• Advanced Data Types: Familiarize yourself with advanced data types like arrays, JSON, XML, and spatial data types, and learn how to work with them effectively in SQL.

• Database Design Patterns: Study common database design patterns such as star schema, snowflake schema, and slowly changing dimensions (SCDs). Understand when and how to apply these patterns to model different types of data effectively.

Python Intermediate

Syllabus

Prerequisites:

• Completion of Python Basics

• Understanding of basic Python syntax and concepts (variables, loops, functions)

• Familiarity with basic data structures (lists, dictionaries)

• Data Structures : Indexing and Slicing

• Array : Visualizing an Array, Accessing Array Element, Manipulating Arrays

• Tuples : Accessing tuple elements, Immutability, Concatenating Tuples, Unpacking Tuples, Tuple Methods

• Lists : Accessing list items, Updating lists, List Manipulation

• Dictionaries : Creating and Accessing dictionaries

• Sets : Examples of various methods for Sets. Remove(),Discard(),issubset(),len(),clear(),intersection(),difference()

• Keywords : Python keywords: and,as,assert,break,class,continue,def,del,if,elif,else,try,except,finally,False,True,for,import,from,global etc

• Operators : Operators are used to perform a variety of operations

  • Arithmetic Operators : Addition(+), subtraction(-), Multiplication(*), division(/), Modulus (%),Floor division(//)

  • Comparison Operators : Equal to(==),Not equal to(!=), Greater(>),Lesser(<),Greater than or equal to(>=), Lesser than or equal to(<=)

  • Logical Operators : and,or, not

  • Bitwise Operators : To perform binary operations

  • Assignment Operators : (=) Used to assign values to variables. (+=) Adds, (-=) Subtracts. (*=) Multiplies. (/=) Divides

  • Membership Operators : To check value exists in sequence

  • Identity Operators : To check two values or objects belong to same memory locations

• Control Flow Statements : if, elif, else clause

• Loops : while, for, range(),looping through lists, strings, dictionaries

  • Loop Control Statements : Break, continue, pass

• Iterators and Generators : Iterables, enumerate(),zip(),Creating a custom iterator

• Functions in Python : Built in Functions

  • User Defined Functions : Functions with single, multiple, default and variable length arguments

  • Variable Namespace and Scope : Names in Python World, Namespace, scopes

  • Lambda functions : map(),filter(), zip()

Power BI Intermediate

Syllabus

Prerequisites:

• Completion of Power BI Basics

• Understanding of Power BI interface and basic functionalities

• Familiarity with basic data import and transformation

• Basic knowledge of data visualization principles

• Define DAX : Define Data Analysis Expressions (DAX)

• Members : Define calculated members

• Context : Differentiate between Row Context and Set Context

• Functions : Use Calculate Functions for Advanced Calculations

• Time Functions : Illustrate Time Functions

• Advanced DAX : Discuss Advanced DAX

• Hands On Practical : Explore DAX Expressions like Calculate, Evaluate, Error, IIF, Replace, Compute, Table Level Functions

• Data Visualization : Define Data Visualization. Why Data Visualization

• Best Practices : Describe Best Practices of Data Visualization

• PBI Desktop Visualization : Describe Power BI Desktop Visualization

• Custom Visuals : Illustrate Custom Visuals in Power BI Desktop

• Formatting Visuals : Discuss Formatting Visuals in Power BI Desktop

• Better Understanding : Understand Charts, Score Cards and other Visualization items in Power View

• Hands On Practical : Explore charts and various items in data Visualization.