Big Data and AI

Posts

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

Lesson 3.4 – Counting Functions (COUNT, COUNTA, COUNTBLANK)

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