SQL Pocket Guide, 4th Edition

Chapter 8. Advanced Querying Concepts

This chapter covers a few advanced ways of wrangling data using SQL queries, beyond the six main clauses covered in Chapter 4, “Querying Basics”, and the common keywords covered in Chapter 7, “Operators and Functions”.

Table 8-1 includes descriptions and code examples of the four concepts covered in this chapter.

Table 8-1. Advanced querying concepts

Concept	Description	Code Example
Case Statements	If a condition is met, return a particular value. Otherwise, return another value.	SELECT house_id, CASE WHEN flg = 1 THEN 'for sale' ELSE 'sold' END FROM houses;
Grouping and Summarizing	Split data into groups, aggregate the data within each group, and return a value for each group.	SELECT zip, AVG(ft) FROM houses GROUP BY zip;
Window Functions	Split data into groups, aggregate or order the data within each group, and return a value for each row.	SELECT zip, ROW_NUMBER() OVER (PARTITION BY zip ORDER BY price) FROM houses;
Pivoting and Unpivoting	Turn values in a column into multiple columns or consolidate multiple columns into a single column. Supported by Oracle and SQL Server.	-- Oracle syntax SELECT * FROM listing_info PIVOT (COUNT(*) FOR room IN ('bd','br'));

This chapter describes each of the concepts in Table 8-1 in detail, along with common use cases.

Case Statements

A CASE statement is used to apply if-else logic within a query. For example, you could use a CASE statement to spell out values. If a 1 is seen, display ...