Big Data and AI

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

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

Lesson 3.4 – Counting Functions (COUNT, COUNTA, COUNTBLANK) Counting data is one of the most common tasks in Excel. Whether you are analyzing sales, checking how many entries are missing, or counting how many values are valid, Excel provides simple functions to help you. In this lesson, you will learn three essential counting functions: COUNT , COUNTA , and COUNTBLANK . 1. COUNT – Count Numbers Only COUNT counts how many cells contain numeric values. Syntax: =COUNT(range) Example: =COUNT(A2:A10) COUNT ignores text, empty cells, and errors. Use it when you want to count numeric entries only . 2. COUNTA – Count Non‑Empty Cells COUNTA counts all non‑empty cells, regardless of content. Syntax: =COUNTA(range) Example: =COUNTA(B2:B10) COUNTA counts: Numbers Text Dates Logical values (TRUE/FALSE) Errors Use COUNTA when you want to know how many cells contain any type of data . 3. COUNTBLANK – Count Empty Ce...

Lesson 3.3 – Math Functions (SUM, AVERAGE, MIN, MAX) Excel includes built‑in functions that make calculations faster, easier, and more accurate. In this lesson, you will learn four essential math functions used worldwide in business, finance, and data analysis: SUM , AVERAGE , MIN , and MAX . 1. What Is a Function? A function is a predefined formula that performs a specific calculation. All functions follow this structure: =FUNCTION_NAME(arguments) Example: =SUM(A1:A10) Functions save time and reduce errors compared to writing formulas manually. 2. SUM – Add Values SUM adds numbers in a range of cells. Syntax: =SUM(range) Example: =SUM(B2:B10) SUM is ideal for totals, budgets, sales, and any numeric aggregation. 3. AVERAGE – Calculate the Mean AVERAGE calculates the arithmetic mean of a group of numbers. Syntax: =AVERAGE(range) Example: =AVERAGE(C2:C10) Use AVERAGE to analyze performance, scores, or trends. ...

  Part II: Retrieving Data Chapter 5: Aggregation and Grouping Summarizing data is essential when you need high-level insights from large tables. Aggregate functions let you collapse detailed rows into single metrics—like totals, averages, or counts. Grouping then partitions those rows into buckets for segmented analysis. In this chapter, we’ll explore: Core aggregate functions: COUNT , SUM , AVG , MIN , MAX Using GROUP BY to create logical buckets Filtering groups with HAVING Handling NULL values within aggregations Practical examples for generating charts and reports 1. Aggregation Functions Overview Aggregate functions process multiple rows to produce a single summary value. They ignore row-level granularity and calculate metrics across a set: COUNT(expr) returns the number of non- NULL values or * for all rows SUM(expr) adds numeric values across rows AVG(expr) computes the average of numeric values MIN(expr) finds the smallest value MAX(expr) finds the largest value E...

Lesson 3.2 – Relative vs Absolute References When you copy a formula in Excel, the cell references inside it may change automatically. This behavior is extremely powerful, but only if you understand how it works. In this lesson, you will learn the difference between relative , absolute , and mixed references. 1. What Are Cell References? A cell reference tells Excel where to find the data used in a formula. Examples: A1 → column A, row 1 B5 → column B, row 5 C10 → column C, row 10 When you copy a formula, Excel may adjust these references automatically. 2. Relative References (A1) Relative references change when a formula is copied to another cell. This is the default behavior in Excel. Example: =A1 + B1 If you copy this formula from row 1 to row 2, it becomes: =A2 + B2 Excel “moves” the references based on the direction of the copy. 3. Absolute References ($A$1) Absolute references do not change when copi...

Lesson 3.1 – How Formulas Work Formulas are the core of Excel. They allow you to perform calculations, analyze data, and automate tasks. Every formula in Excel follows a simple structure and always begins with an equal sign (=). Understanding how formulas work is essential before learning specific functions. 1. The Structure of a Formula All Excel formulas follow this basic pattern: =operand operator operand Example: =5 + 3 =A1 * B1 =SUM(A1:A10) Excel calculates the result and displays it in the cell, while the formula remains visible in the Formula Bar. 2. The Equal Sign (=) Every formula must start with = . Without it, Excel treats the entry as text. Examples: =10+5 → Excel calculates 10+5 → Excel shows “10+5” as text 3. Operators in Excel Operators tell Excel what type of calculation to perform. Operator Meaning Example + Addition =A1 + B1 - Subtract...

Lesson 2.5 – Basic Data Cleaning Clean data is essential for accurate calculations, sorting, filtering, and analysis. Even small issues—extra spaces, inconsistent capitalization, or unwanted characters— can cause formulas to fail or produce incorrect results. In this lesson, you will learn simple but powerful tools to clean data quickly using Excel functions. 1. Why Data Cleaning Matters Raw data often contains problems such as: Extra spaces before or after text Inconsistent capitalization Non-printable characters from imported files Mixed formats (text that looks like numbers) Cleaning data ensures consistency and prevents errors in formulas and analysis. 2. TRIM – Remove Extra Spaces TRIM(text) removes extra spaces from text, leaving only single spaces between words. Example: Original: “ Product A ” Formula: =TRIM(A1) Result: “Product A” TRIM is essential when working with imported or manually typed data. ...

Lesson 2.4 – AutoFill and Flash Fill AutoFill and Flash Fill are two powerful Excel tools that help you enter data faster and reduce repetitive work. AutoFill extends patterns and formulas, while Flash Fill recognizes patterns automatically and fills the remaining values for you. 1. What AutoFill Does AutoFill uses the small square at the bottom-right corner of the active cell (the Fill Handle ) to copy or extend data. You can use AutoFill to: Copy values Extend sequences (1, 2, 3…) Extend dates (Mon, Tue, Wed…) Copy formulas and adjust references 2. Using AutoFill Steps: Select a cell or a range. Drag the Fill Handle down, up, left, or right. Release the mouse to apply the pattern. Examples: Type January → drag → Excel fills February, March… Type 1 and 2 → select both → drag → Excel continues 3, 4, 5… Type 15/03/2024 → drag → Excel fills the following dates 3. AutoFill with Formulas When you drag a formu...

Lesson 2.3 – Number Format Number formats control how Excel displays numeric values such as currency, percentages, dates, and measurements. Formatting does not change the underlying value — it only changes how the value appears. Understanding number formats is essential for creating clear, professional spreadsheets. 1. What Number Formats Do Excel stores numbers as raw values (e.g., 1500), but you can display them in different ways: 1,500 → with thousand separators $1,500.00 → as currency 1500% → as a percentage 15/03/2024 → as a date The value stays the same — only the appearance changes. 2. Accessing Number Formats You can apply number formats from: Home → Number group Right-click → Format Cells The Format Cells window provides the most complete set of options. 3. Common Number Formats 3.1 General The default format. Excel displays the number in the simplest way possible. 3.2 Number Used for standard nu...

 Chapter 4: Advanced Filtering and Expressions In this chapter, we’ll deepen your SQL toolkit by mastering advanced filtering techniques and expressions in the WHERE clause. Precise filters let you extract exactly the rows you need, avoid noisy results, and improve query performance. We’ll cover: Comparison Operators: = , <> , > , < , >= , <= Logical Operators: AND , OR , NOT Pattern Matching: LIKE with % and _ wildcards Set Membership & Ranges: IN and BETWEEN Handling Missing Values: IS NULL and COALESCE By the end, you’ll be crafting tight, readable filters that yield clean, accurate datasets. 1. Comparison Operators Comparison operators form the bedrock of row-level filtering. They compare a column’s value to a constant or another column. Operator Description Example = Equal to WHERE status = 'active' <> Not equal to WHERE rating <> 5 > Greater than WHERE price > 100 < Less than WHERE quantity < 10 >= Greater than or equ...

Lesson 2.2 – Formatting Cells Formatting cells is essential for making your data clear, readable, and professional. Good formatting improves communication, helps prevent errors, and ensures your spreadsheet follows international standards used in business and data analysis. 1. Font Formatting You can change the appearance of text using the Font group on the Home tab. Bold – highlight important labels Italic – emphasize specific values Underline – titles or totals Font size – adjust readability Font color – use sparingly for clarity Avoid using too many colors or styles, as this reduces readability. 2. Cell Alignment Alignment controls how content is positioned inside a cell. Left – default for text Right – default for numbers and dates Center – useful for headers Vertical alignment – top, middle, bottom Wrap Text – keeps long text visible inside the cell Merge & Center – combine cells for titles ...

Lesson 2.1 – Entering Data In this lesson, you will learn how to enter clean and structured data in Excel. Correct data entry is essential for accurate calculations, sorting, filtering, and all types of data analysis. Excel recognizes different types of data such as text, numbers, dates, and times, and each behaves differently inside the worksheet. 1. Entering Text Text is used for labels, names, categories, and descriptions. Excel aligns text to the left by default. Examples: Product A Customer Name Category To enter text, click a cell and type. Press Enter to confirm. 2. Entering Numbers Numbers are used for quantities, prices, percentages, and calculations. Excel aligns numbers to the right by default. Examples: 150 3.75 -20 Avoid adding spaces or symbols (except decimal separators), otherwise Excel may treat the value as text. 3. Entering Dates and Times Dates and times must follow a valid format...

Lesson 1.4 – Data Types in Excel: Text, Numbers, and Dates Excel interprets data differently depending on its type. Understanding how Excel classifies text, numbers, and dates is essential for accurate calculations, sorting, filtering, and data analysis. This lesson explains how each data type works and how Excel behaves when processing them. 1. Text (Strings) Text is any content that Excel does not recognize as a number or date. Text is aligned to the left by default. It is commonly used for labels, names, categories, and descriptive information. Examples of text: Product A January Invoice #4521 ABC123 Even numeric-looking values may be treated as text if they contain leading zeros, spaces, or non-numeric characters. 2. Numbers Numbers are values Excel can use in calculations. They are aligned to the right by default. Numbers can represent quantities, prices, percentages, measurements, or any numeric data. Examples of ...

Lesson 1.3 – Workbooks, Worksheets, Cells, and Ranges This lesson introduces the fundamental structural elements of Excel: workbooks, worksheets, cells, and ranges. Understanding these components is essential before working with data, formulas, or analysis tools. The concepts explained here form the foundation of all Excel operations. 1. Workbooks A workbook is the Excel file itself. Each workbook can contain one or more worksheets. Workbooks are used to organize related data within a single file. For example, a financial model, a budget, or a project plan is typically stored in one workbook with multiple worksheets. Examples of workbook names: Budget_2024.xlsx Sales_Report_Q1.xlsx Inventory_Tracking.xlsx Workbooks can be saved in different formats, such as .xlsx (standard), .xlsm (with macros), or .csv (comma-separated values). 2. Worksheets A worksheet is a single page inside a workbook. Worksheets are used to separate d...