Big Data and AI

Posts

Module 4 – Working with Tables

Module 4 – Working with Tables Excel Tables are one of the most powerful tools for organizing, analyzing, and updating data. Unlike normal ranges, Tables automatically expand, apply formatting, update formulas, and provide structured references that make your spreadsheets more reliable and easier to maintain. In this module, you will learn how to: Convert a normal range into an Excel Table Use Table features such as sorting, filtering, and banded rows Apply and customize Table styles Use structured references in formulas Add and remove columns dynamically Use the Total Row for automatic calculations Excel Tables are essential for anyone working with real datasets, reports, dashboards, business analysis, or automation. Once you understand how they work, they become an indispensable tool in your workflow. Lessons in This Module Lesson 4.1 – Creating and Formatting Tables Lesson 4.2 – Sorting and Filtering in Tables Lesson 4.3...

Part III: Combining Data Across Tables in SQL

In real-world databases, data is rarely confined to a single table. To unlock deeper insights, you need to learn how to merge and compare datasets stored in different tables. In this third installment of our SQL tutorial series on Data Analyst BI, we’ll explore: JOIN Operations: Linking rows from multiple tables based on related columns Set Operations: Merging result sets via UNION, INTERSECT, and EXCEPT Mastering these techniques will enable you to build richer reports, enforce data integrity, and answer complex business questions. JOIN Operations JOINs are the cornerstone of combining data across tables . By matching rows on key columns, you can assemble comprehensive views of your data. 1. INNER JOIN Returns only rows that have matching keys in both tables. sql SELECT o.order_id, c.customer_name, o.order_date FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id; Use INNER JOIN when you need records that exist in both tables—e.g., orders with va...

Lesson 3.6 – Logical Functions (IF, AND, OR)

Lesson 3.6 – Logical Functions (IF, AND, OR) Logical functions allow Excel to make decisions based on conditions. They are essential for building dynamic spreadsheets, validating data, and creating automated calculations. In questa lezione impari tre funzioni fondamentali: IF , AND e OR . 1. IF – Make Decisions IF checks a condition and returns one value if the condition is true and another value if it is false. Syntax: =IF(condition, value_if_true, value_if_false) Example: =IF(A2 >= 50, "Pass", "Fail") IF is used for grading, approvals, thresholds, flags, and conditional labels. 2. AND – Check Multiple Conditions AND returns TRUE only if all conditions are true. Syntax: =AND(condition1, condition2, ...) Example: =AND(A2 >= 50, B2 >= 50) AND is useful for combined requirements, such as minimum scores or multi-step validations. 3. OR – Check Alternative Conditions OR returns TRUE if at le...

Part II: Retrieving Data Chapter 6: Subqueries and Derived Tables

Chapter 6: Subqueries and Derived Tables Complex business questions often require breaking problems into smaller, composable parts. SQL subqueries and derived tables—sometimes called inline views—offer a clean way to nest queries, test membership, and encapsulate logic. In this chapter, you’ll learn how to: Write scalar and correlated subqueries Use EXISTS and NOT EXISTS for efficient membership tests Create derived tables for readability and modularity Nest queries to filter data based on other query results By the end, you’ll tackle multi-step analyses with maintainable, self-documenting SQL. 1. Understanding Subqueries A subquery is a query enclosed in parentheses that returns data to the outer (parent) query. There are two main flavors: Scalar subqueries return a single value. Correlated subqueries reference columns from the outer query and re-execute per row. 1.1 Scalar Subqueries Use scalar subqueries when you need a single aggregated or computed value: sql SELECT o....

Lesson 3.5 – Text Functions (LEFT, RIGHT, MID, LEN, CONCAT)

Lesson 3.5 – Text Functions (LEFT, RIGHT, MID, LEN, CONCAT) Text functions help you extract, combine, and analyze text values in Excel. They are essential for cleaning data, preparing reports, and working with names, codes, product IDs, and imported datasets. In questa lezione impari cinque funzioni fondamentali: LEFT , RIGHT , MID , LEN e CONCAT . 1. LEFT – Extract Characters from the Left Syntax: =LEFT(text, number_of_characters) Example: =LEFT("ProductA", 7) Result: Product LEFT is useful for extracting prefixes, codes, or the first part of a string. 2. RIGHT – Extract Characters from the Right Syntax: =RIGHT(text, number_of_characters) Example: =RIGHT("AB12345", 5) Result: 12345 RIGHT is ideal for extracting numeric codes, suffixes, or final characters. 3. MID – Extract Characters from the Middle Syntax: =MID(text, start_position, number_of_characters) Example: =MID("AB-2024-IT", 4, 4) R...