Hjem

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;
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;
Chapter 2: Sorting Query Results;
2.1 Returning Query Results in a Specified Order;
2.2 Sorting by Multiple Fields;
2.3 Sorting by Substrings;
2.4 Sorting Mixed Alphanumeric Data;
2.5 Dealing with Nulls when Sorting;
2.6 Sorting on a Data Dependent Key;
Chapter 3: 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;
Chapter 4: 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;
Chapter 5: Metadata Queries;
5.1 Listing Tables in a Schema;
5.2 Listing a Table''s Columns;
5.3 Listing Indexed Columns for a Table;
5.4 Listing Constraints on a Table;
5.5 Listing Foreign Keys Without Corresponding Indexes;
5.6 Using SQL to Generate SQL;
5.7 Describing the Data Dictionary Views in an Oracle Database;
Chapter 6: 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;
Chapter 7: 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;
Chapter 8: 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;
Chapter 9: 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;
Chapter 10: Working with Ranges;
10.1 Locating a Range of Consecutive Values;
10.2 Finding Differences Between Rows in the Same Group or Partition;
10.3 Locating the Beginning and End of a Range of Consecutive Values;
10.4 Filling in Missing Values in a Range of Values;
10.5 Generating Consecutive Numeric Values;
Chapter 11: 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;
Chapter 12: 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;
Chapter 13: Hierarchical Queries;
13.1 Expressing a Parent-Child Relationship;
13.2 Expressing a Child-Parent-Grandparent Relationship;
13.3 Creating a Hierarchical View of a Table;
13.4 Finding All Child Rows for a Given Parent Row;
13.5 Determining Which Rows Are Leaf, Branch, or Root Nodes;
Chapter 14: 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;Appendix A: Window Function Refresher; A.1 Grouping; A.2 Windowing;Appendix B: Rozenshtein Revisited; B.1 Rozenshtein''s Example Tables; B.2 Answering Questions Involving Negation; B.3 Answering Questions Involving "at Most"; B.4 Answering Questions Involving "at Least"; B.5 Answering Questions Involving "Exactly"; B.6 Answering Questions Involving "Any" or "All";Colophon;