Share
Create bookmark
SQL Cookbook
Query Solutions and Techniques for Database Developers
Notes
Please login to add notes
- Table of Contents
- + Preface
-
+
Retrieving Records
- + 1.1 Retrieving All Rows and Columns from a Table
- + 1.2 Retrieving a Subset of Rows from a Table
- + 1.3 Finding Rows That Satisfy Multiple Conditions
- + 1.4 Retrieving a Subset of Columns from a Table
- + 1.5 Providing Meaningful Names for Columns
- + 1.6 Referencing an Aliased Column in the WHERE Clause
- + 1.7 Concatenating Column Values
- + 1.8 Using Conditional Logic in a SELECT Statement
- + 1.9 Limiting the Number of Rows Returned
- + 1.10 Returning n Random Records from a Table
- + 1.11 Finding Null Values
- + 1.12 Transforming Nulls into Real Values
- + 1.13 Searching for Patterns
-
+
Sorting Query Results
-
+
Working with Multiple Tables
- + 3.1 Stacking One Rowset Atop Another
- + 3.2 Combining Related Rows
- + 3.3 Finding Rows in Common Between Two Tables
- + 3.4 Retrieving Values from One Table That Do Not Exist in Another
- + 3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another
- + 3.6 Adding Joins to a Query Without Interfering with Other Joins
- + 3.7 Determining Whether Two Tables Have the Same Data
- + 3.8 Identifying and Avoiding Cartesian Products
- + 3.9 Performing Joins When Using Aggregates
- + 3.10 Performing Outer Joins When Using Aggregates
- + 3.11 Returning Missing Data from Multiple Tables
- + 3.12 Using NULLs in Operations and Comparisons
-
+
Inserting, Updating, Deleting
- + 4.1 Inserting a New Record
- + 4.2 Inserting Default Values
- + 4.3 Overriding a Default Value with NULL
- + 4.4 Copying Rows from One Table into Another
- + 4.5 Copying a Table Definition
- + 4.6 Inserting into Multiple Tables at Once
- + 4.7 Blocking Inserts to Certain Columns
- + 4.8 Modifying Records in a Table
- + 4.9 Updating When Corresponding Rows Exist
- + 4.10 Updating with Values from Another Table
- + 4.11 Merging Records
- + 4.12 Deleting All Records from a Table
- + 4.13 Deleting Specific Records
- + 4.14 Deleting a Single Record
- + 4.15 Deleting Referential Integrity Violations
- + 4.16 Deleting Duplicate Records
- + 4.17 Deleting Records Referenced from Another Table
- + Metadata Queries
-
+
Working with Strings
- + 6.1 Walking a String
- + 6.2 Embedding Quotes Within String Literals
- + 6.3 Counting the Occurrences of a Character in a String
- + 6.4 Removing Unwanted Characters from a String
- + 6.5 Separating Numeric and Character Data
- + 6.6 Determining Whether a String Is Alphanumeric
- + 6.7 Extracting Initials from a Name
- + 6.8 Ordering by Parts of a String
- + 6.9 Ordering by a Number in a String
- + 6.10 Creating a Delimited List from Table Rows
- + 6.11 Converting Delimited Data into a Multi-Valued IN-List
- + 6.12 Alphabetizing a String
- + 6.13 Identifying Strings That Can Be Treated As Numbers
- + 6.14 Extracting the nth Delimited Substring
- + 6.15 Parsing an IP Address
-
+
Working with Numbers
- + 7.1 Computing an Average
- + 7.2 Finding the Min/Max Value in a Column
- + 7.3 Summing the Values in a Column
- + 7.4 Counting Rows in a Table
- + 7.5 Counting Values in a Column
- + 7.6 Generating a Running Total
- + 7.7 Generating a Running Product
- + 7.8 Calculating a Running Difference
- + 7.9 Calculating a Mode
- + 7.10 Calculating a Median
- + 7.11 Determining the Percentage of a Total
- + 7.12 Aggregating Nullable Columns
- + 7.13 Computing Averages Without High and Low Values
- + 7.14 Converting Alphanumeric Strings into Numbers
- + 7.15 Changing Values in a Running Total
-
+
Date Arithmetic
- + 8.1 Adding and Subtracting Days, Months, and Years
- + 8.2 Determining the Number of Days Between Two Dates
- + 8.3 Determining the Number of Business Days Between Two Dates
- + 8.4 Determining the Number of Months or Years Between Two Dates
- + 8.5 Determining the Number of Seconds, Minutes, or Hours Between Two Dates
- + 8.6 Counting the Occurrences of Weekdays in a Year
- + 8.7 Determining the Date Difference Between the Current Record and the Next Record
-
+
Date Manipulation
- + 9.1 Determining If a Year Is a Leap Year
- + 9.2 Determining the Number of Days in a Year
- + 9.3 Extracting Units of Time from a Date
- + 9.4 Determining the First and Last Day of a Month
- + 9.5 Determining All Dates for a Particular Weekday Throughout a Year
- + 9.6 Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
- + 9.7 Creating a Calendar
- + 9.8 Listing Quarter Start and End Dates for the Year
- + 9.9 Determining Quarter Start and End Dates for a Given Quarter
- + 9.10 Filling in Missing Dates
- + 9.11 Searching on Specific Units of Time
- + 9.12 Comparing Records Using Specific Parts of a Date
- + 9.13 Identifying Overlapping Date Ranges
- + Working with Ranges
-
+
Advanced Searching
- + 11.1 Paginating Through a Result Set
- + 11.2 Skipping n Rows from a Table
- + 11.3 Incorporating OR Logic When Using Outer Joins
- + 11.4 Determining Which Rows Are Reciprocals
- + 11.5 Selecting the Top n Records
- + 11.6 Finding Records with the Highest and Lowest Values
- + 11.7 Investigating Future Rows
- + 11.8 Shifting Row Values
- + 11.9 Ranking Results
- + 11.10 Suppressing Duplicates
- + 11.11 Finding Knight Values
- + 11.12 Generating Simple Forecasts
-
+
Reporting and Warehousing
- + 12.1 Pivoting a Result Set into One Row
- + 12.2 Pivoting a Result Set into Multiple Rows
- + 12.3 Reverse Pivoting a Result Set
- + 12.4 Reverse Pivoting a Result Set into One Column
- + 12.5 Suppressing Repeating Values from a Result Set
- + 12.6 Pivoting a Result Set to Facilitate Inter-Row Calculations
- + 12.7 Creating Buckets of Data, of a Fixed Size
- + 12.8 Creating a Predefined Number of Buckets
- + 12.9 Creating Horizontal Histograms
- + 12.10 Creating Vertical Histograms
- + 12.11 Returning Non-GROUP BY Columns
- + 12.12 Calculating Simple Subtotals
- + 12.13 Calculating Subtotals for All Possible Expression Combinations
- + 12.14 Identifying Rows That Are Not Subtotals
- + 12.15 Using Case Expressions to Flag Rows
- + 12.16 Creating a Sparse Matrix
- + 12.17 Grouping Rows by Units of Time
- + 12.18 Performing Aggregations over Different Groups/ Partitions Simultaneously
- + 12.19 Performing Aggregations over a Moving Range of Values
- + 12.20 Pivoting a Result Set with Subtotals
- + Hierarchical Queries
-
+
Odds ‘n’ Ends
- + 14.1 Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator
- + 14.2 Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator
- + 14.3 Transposing a Result Set Using Oracle’s MODEL Clause
- + 14.4 Extracting Elements of a String from Unfixed Locations
- + 14.5 Finding the Number of Days in a Year (an Alternate Solution for Oracle)
- + 14.6 Searching for Mixed Alphanumeric Strings
- + 14.7 Converting Whole Numbers to Binary Using Oracle
- + 14.8 Pivoting a Ranked Result Set
- + 14.9 Adding a Column Header into a Double Pivoted Result Set
- + 14.10 Converting a Scalar Subquery to a Composite Subquery in Oracle
- + 14.11 Parsing Serialized Data into Rows
- + 14.12 Calculating Percent Relative to Total
- + 14.13 Creating CSV Output from Oracle
- + 14.14 Finding Text Not Matching a Pattern (Oracle)
- + 14.15 Transforming Data with an Inline View
- + 14.16 Testing for Existence of a Value Within a Group
-
+
Window Function Refresher
- + Rozenshtein Revisited
- 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
Test the closed alpha on
paperc.com
Computers
>
Programming Languages
>
SQL
Publication year
: 2008
License: All rights reserved ©
Times read: 2,231 WHERE
Book Details
Authors
Categories
Publishers
My Annotations
Login
Follow us at
