Learning SQL (Alan Beaulieu)（Z-Library）

【One-Line Pitch】 A practical, example-driven introduction to SQL that takes you from your first `SELECT` statement to advanced topics like set operations, subqueries, and querying nonrelational data—ideal for developers, analysts, and anyone who needs to turn raw data into answers. 【Book Arc】 - **Opening (~0%–9%)**: Sets the stage with the history of databases and the relational model, then introduces the three main SQL statement classes (schema, data, transaction) with simple `CREATE TABLE`, `INSERT`, and `SELECT` examples. This stage solves the "where do I start?" problem by grounding you in core concepts and the book's structure. - **Early (~9%–28%)**: Dives into creating and populating a database. You learn about data types (character sets, text types, temporal data), table design, and normalization—using a `person` table example to show how to refine a flawed design into a normalized schema. This stage builds the foundation for all later query work. - **Early–Middle (~28%–38%)**: Introduces the Sakila sample database (a DVD rental store schema) and the `SELECT` statement's core clauses: `select`, `from`, `where`. You see how to retrieve, filter, and sort data, with emphasis on using parentheses to clarify complex conditions. This is where you start writing real queries. - **Middle (~38%–53%)**: Expands into filtering techniques (equality, range, membership conditions) and grouping with `GROUP BY` and `HAVING` to find trends (e.g., customers with 40+ rentals). Also covers querying multiple tables via joins and working with sets using `UNION`, `INTERSECT`, and `EXCEPT`. This stage moves you from single-table queries to multi-table analysis. - **Late (~53%–end)**: Covers data generation, manipulation, and conversion (strings, numbers, dates), plus advanced topics like subqueries, conditional logic, and analytic functions. The book closes with a chapter on querying nonrelational databases (e.g., via Apache Drill), a rare topic for introductory texts. This stage prepares you for real-world reporting and big data scenarios. 【Key Takeaways】 - **SQL has three distinct statement classes** (Early): schema statements define structures (e.g., `CREATE TABLE`), data statements manipulate data (e.g., `INSERT`, `SELECT`), and transaction statements manage commits/rollbacks. Knowing which class you're using clarifies your intent and helps you choose the right syntax. - **Normalization prevents data redundancy** (Early): The `person` table example shows how a single table with compound columns (name, address) and lists (favorite foods) should be split into multiple tables with foreign keys. This ensures uniqueness and avoids update anomalies—a core skill for designing any database. - **Character sets and data types matter for internationalization** (Early): MySQL 8 defaults to `utf8mb4`, but you can override per column or database (e.g., `varchar(20) character set latin1`). For large text, use `TEXT` types beyond `varchar`'s 64 KB limit. This prevents data corruption and storage surprises. - **The `SELECT` statement is built from clauses** (Middle): `select`, `from`, `where`, `group by`, `having`, and `order by` each serve a distinct purpose. `GROUP BY` aggregates rows (e.g., counting rentals per customer), while `HAVING` filters those groups—similar to how `WHERE` filters raw rows. Master these to move from listing data to summarizing it. - **Use parentheses when mixing operators** (Middle): Combining `AND`/`OR` conditions without parentheses leads to ambiguous, buggy queries. Explicit grouping keeps you, the server, and future maintainers on the same page—a simple habit that prevents costly errors. - **Set operations treat query results as sets** (Middle): `UNION`, `INTERSECT`, and `EXCEPT` combine results from multiple queries, with rules for sorting and precedence. These are powerful for comparing datasets (e.g., customers who rented vs. those who didn't) and go beyond simple joins. - **Data conversion is a practical necessity** (Late): Functions like `str_to_date` let you parse non-standard date formats (e.g., 'DEC-21-1980' with `%b-%d-%Y`), and numeric functions control precision. Always specify format strings explicitly rather than relying on defaults to avoid locale surprises. - **SQL extends beyond relational databases** (Late): Tools like Apache Drill allow querying nonrelational data (JSON, CSV, Hadoop) using SQL syntax. This future-proofs your skills as data landscapes diversify beyond traditional tables. 【Reading Tips】 - **Skim Chapter 1** (history, SQL classes) if you're already familiar with databases; focus instead on the `CREATE TABLE` and `INSERT` examples to get a feel for syntax. - **Deep-read Chapter 2** (creating/populating a database) and the normalization walkthrough—this is where you build mental models for table design that pay off in every later chapter. - **Practice with the Sakila database** (introduced ~38%): Download and load it as instructed, then run the example queries yourself. The book's value comes from hands-on repetition, not passive reading. - **Pay extra attention to `GROUP BY`/`HAVING` and set operations** (~47%–53%): These are conceptually tricky but essential for real reporting. Work through the exercises at chapter ends to solidify. - **Use the appendixes** (schema diagram, data types) as quick references while reading; don't memorize—look up as needed. 【Coverage Limits】 This guide synthesizes the first ~53% of the book in detail (through filtering, grouping, and joins). The later chapters on subqueries, conditional logic, analytic functions, and nonrelational querying are mentioned but not deeply covered here, as the excerpts thin out beyond the midpoint.