SQL Cookbook

SQL Cookbook

By Anthony Molinaro

...............................................

Publisher: O'Reilly

Pub Date: December 2005

Print ISBN-10: 0-596-00976-3

Print ISBN-13: 978-0-59-600976-2

Pages: 628

Table of Contents | Index

You know the rudiments of the SQL query language, yet you feel you aren't taking full advantage of

SQL's expressive power. You'd like to learn how to do more work with SQL inside the database

before pushing data across the network to your applications. You'd like to take your SQL skills to

the next level.

Let's face it, SQL is a deceptively simple language to learn, and many database developers never go

far beyond the simple statement: SELECT FROM WHERE . But there is so much more you can do

with the language. In the SQL Cookbook, experienced SQL developer Anthony Molinaro shares his

favorite SQL techniques and features. You'll learn about:

Window functions, arguably the most significant enhancement to SQL in the past decade. If

you're not using these, you're missing out

Powerful, database-specific features such as SQL Server's PIVOT and UNPIVOT operators,

Oracle's MODEL clause, and PostgreSQL's very useful GENERATE_SERIES function

Pivoting rows into columns, reverse-pivoting columns into rows, using pivoting to facilitate

inter-row calculations, and double-pivoting a result set

Bucketization, and why you should never use that term in Brooklyn.

How to create histograms, summarize data into buckets, perform aggregations over a moving

range of values, generate running-totals and subtotals, and other advanced, data warehousing

techniques

The technique of walking a string, which allows you to use SQL to parse through the

characters, words, or delimited elements of a string

Written in O'Reilly's popular Problem/Solution/Discussion style, theSQL Cookbook is sure to please.

Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all

want to go home at a reasonable time and enjoy what's still available of our days." TheSQL

Cookbook moves quickly from problem to solution, saving you time each step of the way.

SQL Cookbook

By Anthony Molinaro

...............................................

Publisher: O'Reilly

Pub Date: December 2005

Print ISBN-10: 0-596-00976-3

Print ISBN-13: 978-0-59-600976-2

Pages: 628

Table of Contents | Index

Copyright

Dedication

Preface

Why I Wrote This Book

Objectives of This Book

Audience for This Book

How to Use This Book

What's Missing from This Book

Structure of This Book

Platform and Version

Tables Used in This Book

Conventions Used in This Book

Using Code Examples

Comments and Questions

Safari? Enabled

Acknowledgments

Chapter 1. Retrieving Records

Recipe 1.1. Retrieving All Rows and Columns from a Table

Recipe 1.2. Retrieving a Subset of Rows from a Table

Recipe 1.3. Finding Rows That Satisfy Multiple Conditions

Recipe 1.4. Retrieving a Subset of Columns from a Table

Recipe 1.5. Providing Meaningful Names for Columns

Recipe 1.6. Referencing an Aliased Column in the WHERE Clause

Recipe 1.7. Concatenating Column Values

Recipe 1.8. Using Conditional Logic in a SELECT Statement

Recipe 1.9. Limiting the Number of Rows Returned

Recipe 1.10. Returning n Random Records from a Table

Recipe 1.11. Finding Null Values

Recipe 1.12. Transforming Nulls into Real Values

Recipe 1.13. Searching for Patterns

Chapter 2. Sorting Query Results

Recipe 2.1. Returning Query Results in a Specified Order

Recipe 2.2. Sorting by Multiple Fields

Recipe 2.3. Sorting by Substrings

Recipe 2.4. Sorting Mixed Alphanumeric Data

Recipe 2.5. Dealing with Nulls when Sorting

Recipe 2.6. Sorting on a Data Dependent Key

Chapter 3. Working with Multiple Tables

Recipe 3.1. Stacking One Rowset atop Another

Recipe 3.2. Combining Related Rows

Recipe 3.3. Finding Rows in Common Between Two Tables

Recipe 3.4. Retrieving Values from One Table That Do Not Exist in Another

Recipe 3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another

Recipe 3.6. Adding Joins to a Query Without Interfering with Other Joins

Recipe 3.7. Determining Whether Two Tables Have the Same Data

Recipe 3.8. Identifying and Avoiding Cartesian Products

Recipe 3.9. Performing Joins when Using Aggregates

Recipe 3.10. Performing Outer Joins when Using Aggregates

Recipe 3.11. Returning Missing Data from Multiple Tables

Recipe 3.12. Using NULLs in Operations and Comparisons

Chapter 4. Inserting, Updating, Deleting

Recipe 4.1. Inserting a New Record

Recipe 4.2. Inserting Default Values

Recipe 4.3. Overriding a Default Value with NULL

Recipe 4.4. Copying Rows from One Table into Another

Recipe 4.5. Copying a Table Definition

Recipe 4.6. Inserting into Multiple Tables at Once

Recipe 4.7. Blocking Inserts to Certain Columns

Recipe 4.8. Modifying Records in a Table

Recipe 4.9. Updating when Corresponding Rows Exist

Recipe 4.10. Updating with Values from Another Table

Recipe 4.11. Merging Records

Recipe 4.12. Deleting All Records from a Table

Recipe 4.13. Deleting Specific Records

Recipe 4.14. Deleting a Single Record

Recipe 4.15. Deleting Referential Integrity Violations

Recipe 4.16. Deleting Duplicate Records

Recipe 4.17. Deleting Records Referenced from Another Table

Chapter 5. Metadata Queries

Recipe 5.1. Listing Tables in a Schema

Recipe 5.2. Listing a Table's Columns

Recipe 5.3. Listing Indexed Columns for a Table

Recipe 5.4. Listing Constraints on a Table

Recipe 5.5. Listing Foreign Keys Without Corresponding Indexes

Recipe 5.6. Using SQL to Generate SQL

Recipe 5.7. Describing the Data Dictionary Views in an Oracle Database

Chapter 6. Working with Strings

Recipe 6.1. Walking a String

Recipe 6.2. Embedding Quotes Within String Literals

Recipe 6.3. Counting the Occurrences of a Character in a String

Recipe 6.4. Removing Unwanted Characters from a String

Recipe 6.5. Separating Numeric and Character Data

Recipe 6.6. Determining Whether a String Is Alphanumeric

Recipe 6.7. Extracting Initials from a Name

Recipe 6.8. Ordering by Parts of a String

Recipe 6.9. Ordering by a Number in a String

Recipe 6.10. Creating a Delimited List from Table Rows

Recipe 6.11. Converting Delimited Data into a Multi-Valued IN-List

Recipe 6.12. Alphabetizing a String

Recipe 6.13. Identifying Strings That Can Be Treated as Numbers

Recipe 6.14. Extracting the nth Delimited Substring

Recipe 6.15. Parsing an IP Address

Chapter 7. Working with Numbers

Recipe 7.1. Computing an Average

Recipe 7.2. Finding the Min/Max Value in a Column

Recipe 7.3. Summing the Values in a Column

Recipe 7.4. Counting Rows in a Table

Recipe 7.5. Counting Values in a Column

Recipe 7.6. Generating a Running Total

Recipe 7.7. Generating a Running Product

Recipe 7.8. Calculating a Running Difference

Recipe 7.9. Calculating a Mode

Recipe 7.10. Calculating a Median

Recipe 7.11. Determining the Percentage of a Total

Recipe 7.12. Aggregating Nullable Columns

Recipe 7.13. Computing Averages Without High and Low Values

Recipe 7.14. Converting Alphanumeric Strings into Numbers

Recipe 7.15. Changing Values in a Running Total

Chapter 8. Date Arithmetic

Recipe 8.1. Adding and Subtracting Days, Months, and Years

Recipe 8.2. Determining the Number of Days Between Two Dates

Recipe 8.3. Determining the Number of Business Days Between Two Dates

Recipe 8.4. Determining the Number of Months or Years Between Two Dates

Recipe 8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates

Recipe 8.6. Counting the Occurrences of Weekdays in a Year

Recipe 8.7. Determining the Date Difference Between the Current Record and the Next Record

Chapter 9. Date Manipulation

Recipe 9.1. Determining if a Year Is a Leap Year

Recipe 9.2. Determining the Number of Days in a Year

Recipe 9.3. Extracting Units of Time from a Date

Recipe 9.4. Determining the First and Last Day of a Month

Recipe 9.5. Determining All Dates for a Particular Weekday Throughout a Year

Recipe 9.6. Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month

Recipe 9.7. Creating a Calendar

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download