상세정보

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 columns FROM table WHERE conditions. 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 outPowerful, database-specific features such as SQL Server's PIVOT and UNPIVOT operators, Oracle's MODEL clause, and PostgreSQL's very useful GENERATE_SERIES functionPivoting rows into columns, reverse-pivoting columns into rows, using pivoting to facilitate inter-row calculations, and double-pivoting a result setBucketization, 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 techniquesThe technique of walking a string, which allows you to use SQL to parse through the characters, words, or delimited elements of a stringWritten in O'Reilly's popular Problem/Solution/Discussion style, the SQL 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." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

정보제공 :

Preface 

1. Retrieving Records

       Retrieving All Rows and Columns from a Table   

       Retrieving a Subset of Rows from a Table   

       Finding Rows That Satisfy Multiple Conditions   

       Retrieving a Subset of Columns from a Table   

       Providing Meaningful Names for Columns   

       Referencing an Aliased Column in the WHERE Clause   

       Concatenating Column Values   

       Using Conditional Logic in a SELECT Statement   

       Limiting the Number of Rows Returned   

       Returning n Random Records from a Table   

       Finding Null Values   

       Transforming Nulls into Real Values   

       Searching for Patterns   

2. Sorting Query Results

       Returning Query Results in a Specified Order   

       Sorting by Multiple Fields   

       Sorting by Substrings   

       Sorting Mixed Alphanumeric Data   

       Dealing with Nulls when Sorting   

       Sorting on a Data Dependent Key   

3. Working with Multiple Tables

       Stacking One Rowset atop Another   

       Combining Related Rows   

       Finding Rows in Common Between Two Tables   

       Retrieving Values from One Table That Do Not Exist in Another   

       Retrieving Rows from One Table That Do Not Correspondto Rows in Another   

       Adding Joins to a Query Without Interfering with Other Joins   

       Determining Whether Two Tables Have the Same Data   

       Identifying and Avoiding Cartesian Products   

       Performing Joins when Using Aggregates   

       Performing Outer Joins when Using Aggregates   

       Returning Missing Data from Multiple Tables   

       Using NULLs in Operations and Comparisons   

4. Inserting, Updating, Deleting

       Inserting a New Record   

       Inserting Default Values   

       Overriding a Default Value with NULL   

       Copying Rows from One Table into Another   

       Copying a Table Definition   

       Inserting into Multiple Tables at Once   

       Blocking Inserts to Certain Columns   

       Modifying Records in a Table   

       Updating when Corresponding Rows Exist   

       Updating with Values from Another Table   

       Merging Records   

       Deleting All Records from a Table   

       Deleting Specific Records   

       Deleting a Single Record   

       Deleting Referential Integrity Violations   

       Deleting Duplicate Records   

       Deleting Records Referenced from Another Table   

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   

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   

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   

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 HoursBetween Two Dates   

       Counting the Occurrences of Weekdays in a Year   

       Determining the Date Difference Between the Current Recordand the Next Record   

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 ofa 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   

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   

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   

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   

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   

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   

A. Window Function Refresher

B. Rozenshtein Revisited

Index

정보제공 :