Chapter_12_Practical SQL: Advanced Query Techniques.

Practical SQL: Chapter 12 – Advanced Query Techniques Chapter Overview Chapter 12 moves beyond basic SQL queries to cover powerful techniques for more complex data analysis. The chapter uses U.S. Census data, meat/poultry inspection records, and a new temperature readings dataset to demonstrate each concept in practice. Key Topics Covered 1. Subqueries A subquery is a query nested inside another query, enclosed in parentheses. The chapter covers four main uses: WHERE clause filtering – Using a subquery to dynamically generate a comparison value (e.g., finding counties at or above the 90th percentile population) instead of hardcoding it. DELETE with subqueries – Removing rows based on a dynamically calculated threshold. Derived tables (FROM clause) – Treating a subquery's results as a temporary table, allowing multi-step calculations (e.g., comparing average vs. median county population) in a single query. Derived tables can also be joined together. Column generation (SELECT list) – Adding a subquery directly in the column list to compute a per-row value, such as how far each county's population deviates from the national median. The chapter also covers subquery expressions — IN (subquery), EXISTS (subquery), and NOT EXISTS — for filtering rows based on whether matching values appear in another table. 2. Common Table Expressions (CTEs) CTEs, written using the WITH ... AS syntax, let you define one or more named temporary tables before the main query. Advantages over subqueries include: Reusability – A CTE can be referenced multiple times in the main query without repeating code. Readability – Complex multi-step logic is easier to follow than deeply nested subqueries. Modularity – Multiple CTEs can be chained in a single WITH block, each building on the last. Practical SQL: Chapter 12 — Advanced Query Techniques Chapter 12 of Practical SQL introduces advanced SQL methods used to solve more complex data analysis problems. The chapter focuses on techniques that allow SQL queries to become more dynamic, reusable, and analytical beyond simple SELECT statements and joins. The chapter begins by exploring subqueries, which are queries nested inside other queries. Subqueries can be used to generate values for filtering data, create temporary derived tables, calculate statistics such as percentiles and medians, and assist with operations like DELETE, UPDATE, and conditional filtering. The chapter explains both correlated and uncorrelated subqueries, demonstrating how queries can reference data from outer queries to perform sophisticated comparisons and calculations. Next, the chapter introduces derived tables and Common Table Expressions (CTEs) using the WITH clause. These techniques help organize complex queries into readable and reusable sections. Readers learn how to preprocess data, join temporary result sets, reduce redundant code, and improve query clarity when working with large analytical tasks. The chapter also covers cross tabulations (crosstabs) using PostgreSQL’s crosstab() function from the tablefunc extension. Through practical examples such as ice cream survey data and city temperature records, readers learn how to transform raw row-based data into matrix-style summaries that make comparisons easier to interpret. Another major concept introduced is the CASE statement, which allows SQL queries to apply conditional logic similar to “if-then” programming structures. The chapter demonstrates how to classify numerical values into descriptive categories, such as grouping temperatures into ranges like Hot, Warm, or Freezing. These classifications are then combined with CTEs to perform higher-level analysis. Throughout the chapter, practical datasets such as U.S. census county populations, food inspection records, survey responses, and weather readings are used to demonstrate real-world analytical scenarios. Readers gain experience calculating medians, percentiles, rates per million, grouped statistics, and comparative summaries. The chapter rewrites earlier derived-table joins and repeated subquery expressions as CTEs to demonstrate the clarity gained. 3. Cross Tabulations (Crosstabs) Cross tabulations display data in a matrix format — rows represent one variable, columns represent another, and each cell holds an aggregate value (like a count or median). PostgreSQL's crosstab() function (from the tablefunc module) enables this. Two examples are worked through: Ice cream survey – Counting flavor preferences by office location, revealing which flavors are popular where. Temperature readings – Calculating the median monthly high temperature for three U.S. cities (Chicago, Seattle, Waikiki), transforming 1,000+ rows of daily readings into a clean 3-row, 12-column summary table. 4. The CASE Statement CASE adds conditional logic to queries, allowing numeric values to be reclassified into descriptive categories. The syntax evaluates conditions in order and returns a result for the first match in it.