Big Data and AI

Posts

Lesson 4.1 – Creating and Formatting Tables

Lesson 4.1 – Creating and Formatting Tables Excel Tables transform a simple data range into a dynamic, structured, and easy-to-manage dataset. Tables automatically expand, apply formatting, update formulas, and provide powerful tools for sorting, filtering, and analysis. In questa lezione impari come creare e formattare una Tabella in modo corretto. 1. What Is an Excel Table? An Excel Table is a structured data container with built-in features such as: Automatic formatting Filter buttons on each column Dynamic expansion when adding new rows Structured references in formulas Optional Total Row for quick calculations Tables are essential for working with real datasets, reports, and dashboards. 2. How to Create a Table Method 1 – Using the Ribbon: Select your data range (e.g., A1:D20). Go to Insert → Table . Confirm the range and check “My table has headers”. Method 2 – Using the Shortcut: Ctrl + T This is the fastest and...

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