- Yugabyte
- Preface
- 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
- 2. Sorting Query Results
- 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
- 3.13. Summing Up
- 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
- 4.18. Summing Up
- 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
- 5.8. Summing Up
- 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
- 6.16. Comparing strings by sound
- 6.17. Finding Text Not Matching a Pattern
- 6.18. Summing Up
- 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. Smoothing a Series of Values
- 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
- 7.16. Finding outliers using the median absolute deviation
- 7.17. Finding anomalies using the Benford’s Law
- 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
- 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
- 9.14. Summing Up
- 10. Working with Ranges
- 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
- 11.13. Summing Up
- 12. Reporting and Reshaping
- 13. Hierarchical Queries
- 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. Testing for Existence of a Value Within a Group
- Window Function Refresher
- Appedix B: Common Table Expressions
- Index
Dedication
Anthony:
To my mom:
You’re the best! Thank you for everything.
Robert:
To Clare, Maya and Leda.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.