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