Intermediate Courses
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.