SQL Cookbook
A guide to SQL covers such topics as retrieving records, metadata queries, working with strings, data arithmetic, date manipulation, reporting and warehousing, and hierarchical queries.
Stats for nerds
7recommend
7mentions
7people
2012–2020years
100% recommend
Mentions per year
What Hacker News says
- Yeah but the functions and style used in these answers vary across implementations (date_trunc, etc.)
I highly recommend the SQL Cookbook for newer SQL dev as a quick reference comparison to see how often this is the case for even trivial problems in any RDBMS.
Also, for your language I wouldn't expect all mid tier SQL devs to be able to write a recursive CTE from memory (though its useful, you can just look it up again), but something like breaking apart a query plan on your platform of choice is way more important as that muscle allows you to tell if your CTE was crap or not.
- My introduction to window functions (and the best write-up I’ve seen) was through the SQL Cookbook (http://shop.oreilly.com/product/9780596009762.do). I highly recommend.
- Did you try SQL Cookbook?
I think that was the one I used to enjoy.
Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases.
From the table of contents:
...
Chapter 5 Metadata Queries Listing Tables in a Schema Listing a Table's Columns Listing Indexed Columns for a Table Listing Constraints on a Table Listing Foreign Keys Without Corresponding Indexes Using SQL to Generate SQL Describing the Data Dictionary Views in an Oracle Database Chapter 6 Working with Strings Walking a String Embedding Quotes Within String Literals Counting the Occurrences of a Character in a String Removing Unwanted Characters from a String Separating Numeric and Character Data Determining Whether a String Is Alphanumeric Extracting Initials from a Name Ordering by Parts of a String Ordering by a Number in a String Creating a Delimited List from Table Rows Converting Delimited Data into a Multi-Valued IN-List Alphabetizing a String Identifying Strings That Can Be Treated as Numbers Extracting the nth Delimited Substring Parsing an IP Address Chapter 7 Working with Numbers Computing an Average Finding the Min/Max Value in a Column Summing the Values in a Column Counting Rows in a Table Counting Values in a Column Generating a Running Total Generating a Running Product Calculating a Running Difference Calculating a Mode Calculating a Median Determining the Percentage of a Total Aggregating Nullable Columns Computing Averages Without High and Low Values Converting Alphanumeric Strings into Numbers Changing Values in a Running Total Chapter 8 Date Arithmetic Adding and Subtracting Days, Months, and Years Determining the Number of Days Between Two Dates Determining the Number of Business Days Between Two Dates Determining the Number of Months or Years Between Two Dates Determining the Number of Seconds, Minutes, or Hours Between Two Dates Counting the Occurrences of Weekdays in a Year Determining the Date Difference Between the Current Record and the Next Record Chapter 9 Date Manipulation Determining if a Year Is a Leap Year Determining the Number of Days in a Year Extracting Units of Time from a Date Determining the First and Last Day of a Month Determining All Dates for a Particular Weekday Throughout a Year Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month Creating a Calendar Listing Quarter Start and End Dates for the Year Determining Quarter Start and End Dates for a Given Quarter Filling in Missing Dates Searching on Specific Units of Time Comparing Records Using Specific Parts of a Date Identifying Overlapping Date Ranges Chapter 10 Working with Ranges Locating a Range of Consecutive Values Finding Differences Between Rows in the Same Group or Partition Locating the Beginning and End of a Range of Consecutive Values Filling in Missing Values in a Range of Values Generating Consecutive Numeric Values Chapter 11 Advanced Searching Paginating Through a Result Set Skipping n Rows from a Table Incorporating OR Logic when Using Outer Joins Determining Which Rows Are Reciprocals Selecting the Top n Records Finding Records with the Highest and Lowest Values Investigating Future Rows Shifting Row Values Ranking Results Suppressing Duplicates Finding Knight Values Generating Simple Forecasts Chapter 12 Reporting and Warehousing Pivoting a Result Set into One Row Pivoting a Result Set into Multiple Rows Reverse Pivoting a Result Set Reverse Pivoting a Result Set into One Column Suppressing Repeating Values from a Result Set Pivoting a Result Set to Facilitate Inter-Row Calculations Creating Buckets of Data, of a Fixed Size Creating a Predefined Number of Buckets Creating Horizontal Histograms Creating Vertical Histograms Returning Non-GROUP BY Columns Calculating Simple Subtotals Calculating Subtotals for All Possible Expression Combinations Identifying Rows That Are Not Subtotals Using Case Expressions to Flag Rows Creating a Sparse Matrix Grouping Rows by Units of Time Performing Aggregations over Different Groups/Partitions Simultaneously Performing Aggregations over a Moving Range of Values Pivoting a Result Set with Subtotals Chapter 13 Hierarchical Queries Expressing a Parent-Child Relationship Expressing a Child-Parent-Grandparent Relationship Creating a Hierarchical View of a Table Finding All Child Rows for a Given Parent Row Determining Which Rows Are Leaf, Branch, or Root Nodes Chapter 14 Odds 'n' Ends Creating Cross-Tab Reports Using SQL Server's PIVOT Operator Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator Transposing a Result Set Using Oracle's MODEL Clause Extracting Elements of a String from Unfixed Locations Finding the Number of Days in a Year (an Alternate Solution for Oracle) Searching for Mixed Alphanumeric Strings Converting Whole Numbers to Binary Using Oracle Pivoting a Ranked Result Set Adding a Column Header into a Double Pivoted Result Set Converting a Scalar Subquery to a Composite Subquery in Oracle Parsing Serialized Data into Rows Calculating Percent Relative to Total Creating CSV Output from Oracle Finding Text Not Matching a Pattern (Oracle) Transforming Data with an Inline View Testing for Existence of a Value Within a Group Appendix A Window Function Refresher Grouping Windowing Appendix B Rozenshtein Revisited Rozenshtein's Example Tables Answering Questions Involving Negation Answering Questions Involving "at Most" Answering Questions Involving "at Least" Answering Questions Involving "Exactly" Answering Questions Involving "Any" or "All" - Wonderful list! The C-Store papers are particularly fine reading.
For people who looked at this wanting to get going with using database systems (as opposed to creating them), I'd recommend:
The three of these will take you up to a pretty useful level of SQL knowledge/understanding. The first two are fairly easy reading to boot.Learning SQL by Alan Beaulieu SQL Performance Explained by Markus Winand SQL Cookbook by Anthony Molinaro - I swear by O'Reilly's SQL Cookbook (http://www.amazon.com/Cookbook-Cookbooks-OReilly-Anthony-Mol...). The examples start of easy and get more complicated as you go along, all with detailed explanations. The book is a bit out of date but it's still useful. IMHO, it will turn you from a novice to an intermediate in no time.
- I'm no fan of MongoDB, but this same advice goes for any NoSQL data store. I am an Apache Cassandra contributor and community MVP, but my advice stays the same: it's best just to start with a SQL database and go from there. Read some books and learn it well: the "SQL Cookbook" from O'Reilly is great, and so is "The Art of SQL." Premature optimization continues to be the root of all evil.
Related
Learning SQL
3 recommend · 4
SQL and relational theory
9 recommend · 17
An introduction to database systems
14 recommend · 35
SQL antipatterns
31 recommend · 43
Practical SQL, 2nd Edition
5 recommend · 7
Joe Celko's SQL for Smarties
10 recommend · 13
