| This book is divided into 14 chapters and 2 appendices: Chapter 1, Retrieving Records, introduces very simple queries. Examples include how to use a WHERE clause to restrict rows from your result set, providing aliases for columns in your result set, using an inline view to reference aliased columns, using simple conditional logic, limiting the number of rows returned by a query, returning random records, and finding NULL values. Most of the examples are very simple, but some of them appear in more complex recipes, so it's a good idea to read this chapter if you're relatively new to SQL or aren't familiar with any of the examples listed for this chapter. Chapter 2, Sorting Query Results, introduces recipes for sorting query results. The ORDER BY clause is introduced and is used to sort query results. Examples increase in complexity ranging from simple, single-column ordering, to ordering by substrings, to ordering based on conditional expressions. Chapter 3, Working with Multiple Tables, introduces recipes for combining data from multiple tables. If you are new to SQL or are a bit rusty on joins, I strongly recommend you read this chapter before reading Chapter 5 and later. Joining tables is what SQL is all about; you must understand joins to be successful. Examples in this chapter include performing both inner and outer joins, identifying Cartesian productions, basic set operations (set difference, union, intersection), and the effects of joins on aggregate functions. Chapter 4, Inserting, Updating, Deleting, introduces recipes for inserting, updating, and deleting data, respectively. Most of the examples are very straightforward (perhaps even pedestrian). Nevertheless, operations such as inserting rows into one table from another table, the use of correlated subqueries in updates, an understanding of the effects of NULLs, and knowledge of new features such as multi-table inserts and the MERGE command are extremely useful for your toolbox. Chapter 5, Metadata Queries, introduces recipes for getting at your database metadata. It's often very useful to find the indexes, constraints, and tables in your schema. The simple recipes here allow you to gain information about your schema. Additionally, "dynamic" SQL examples are shown here as well, i.e., SQL generated by SQL. Chapter 6, Working with Strings, introduces recipes for manipulating strings. SQL is not known for its string parsing capabilities, but with a little creativity (usually involving Cartesian products) along with the vast array of vendor-specific functions, you can accomplish quite a bit. This chapter is where the book begins to get interesting. Some of the more interesting examples include counting the occurrences of a character in a string, creating delimited lists from table rows, converting delimited lists and strings into rows, and separating numeric and character data from a string of alphanumeric characters. Chapter 7, Working with Numbers, introduces recipes for common number crunching. The recipes found here are extremely common and you'll learn how easily window functions solve problems involving moving calculations and aggregations. Examples include creating running totals; finding mean, median, and mode; calculating percentiles; and accounting for NULL while performing aggregations. Chapter 8, Date Arithmetic, is the first of two chapters dealing with dates. Being able to perform simple date arithmetic is crucial to everyday tasks. Examples include determining the number of business days between two dates, calculating the difference between two dates in different units of time (day, month, year, etc.), and counting occurrences of days in a month. Chapter 9, Date Manipulation, is the second of the two chapters dealing with dates. In this chapter you will find recipes for some of the most common date operations you will encounter in a typical work day. Examples include returning all days in a year, finding leap years, finding first and last days of a month, creating a calendar, and filling in missing dates for a range of dates. Chapter 10, Working with Ranges, introduces recipes for identifying values in ranges, and for creating ranges of values. Examples include automatically generating a sequence of rows, filling in missing numeric values for a range of values, locating the beginning and end of a range of values, and locating consecutive values. Chapter 11, Advanced Searching, introduces recipes that are crucial for everyday development and yet sometimes slip through the cracks. These recipes are not any more difficult than others, yet I see many developers making very inefficient attempts at solving the problems these recipes solve. Examples from this chapter include finding knight values, paginating through a result set, skipping rows from a table, finding reciprocals, selecting the top n records, and ranking results. Chapter 12, Reporting and Warehousing, introduces queries typically used in warehousing or generating complex reports. This chapter was meant to be the majority of the book as it existed in my original vision. Examples include converting rows into columns and vice versa (cross-tab reports), creating buckets or groups of data, creating histograms, calculating simple and complete subtotals, performing aggregations over a moving window of rows, and grouping rows based on given units of time. Chapter 13, Hierarchical Queries, introduces hierarchical recipes. Regardless of how your data is modeled, at some point you will be asked to format data such that it represents a tree or parent-child relationship. This chapter provides recipes accomplishing these tasks. Creating tree-structured result sets can be cumbersome with traditional SQL, so vendor-supplied functions are particularly useful in this chapter. Examples include expressing a parent-child relationship, traversing a hierarchy from root to leaf, and rolling up a hierarchy. Chapter 14, Odds 'n' Ends, is a collection of miscellaneous recipes that didn't seem to fit into any other problem domain, but that nevertheless are interesting and useful. This chapter is different from the rest in that it focuses on vendor-spe-cific solutions only. This is the only chapter of the book where each recipe highlights only one vendor. The reasons are twofold: first, this chapter was meant to serve as more of a fun, geeky chapter. Second, some recipes exist only to highlight a vendor-specific function that has no equivalent in the other RDBMSs (examples include SQL Server's PIVOT/UNPIVOT operators and Oracle's MODEL clause). In some cases, though, you'll be able to easily tweak a solution provided in this chapter to work for a platform not covered in the recipe. Appendix A, Window Function Refresher, is a window function refresher along with a solid discussion of groups in SQL. Window functions are new to most, so it is appropriate that this appendix serves as a brief tutorial. Additionally, in my experience I have noticed that the use of GROUP BY in queries is a source of confusion for many developers. This chapter defines exactly what a SQL group is, and then proceeds to use various queries as proofs to validate that definition. The chapter then goes into the effects of NULLs on groups, aggregates, and partitions. Lastly, you'll find discussion on the more obscure and yet extremely powerful syntax of the window function's OVER clause (i.e., the "framing" or "windowing" clause). Appendix B, Rozenshtein Revisited, is a tribute to David Rozenshtein, to whom I owe my success in SQL development. Rozenshtein's book, The Essence of SQL (Coriolis Group Books) was the first book I purchased on SQL that was not required by a class. It was from that book that I learned how to "think in SQL." To this day I attribute much of my understanding of how SQL works to David's book. It truly is different from any other SQL book I've read, and I'm grateful that it was the first one I picked up on my own volition. Appendix B focuses on some of the queries presented in The Essence of SQL, and provides alternative solutions using window functions (which weren't available when The Essence of SQL was written) for those queries.
|
