Sumário Itens Encontrados: 288PART 1 ? ? ? Foundation of Data Manipulation 1?Chapter 1: The Basics 3?Chapter 2: Summarizing and Aggregating Data 23?Chapter 3: Querying from Multiple Tables 59?Chapter 4: Creating and Deriving Data . 83?Chapter 5: Common Query Patterns 109PART 2 ? ? ? Data Types and Their Problems . 141?Chapter 6: Working with Date and Time Values 143?Chapter 7: Strings . 167?Chapter 8: Working with Numbers 187PART3 ? ? ? Your Development Environment . 207?Chapter 9: Managing Transactions 209?Chapter 10: Data Dictionary . 229PART 4 ? ? ? Data Dictionary 261?Chapter 11: Common Reporting Problems 263?Chapter 12: Cleansing Data . 287?Chapter 13: Tree-Structured Data 313?Chapter 14: Working with XML Data 335?Chapter 15: Partitioning 351?Chapter 16: LOBs 383PART 5 ? ? ? Administration 401?Chapter 17: Database Administration . 403?Chapter 18: Object Mamagement . 425?Chapter 19: SQL Monitoring and Tuning 457?Chapter 20: Database Troubleshooting497?Index 519PART 1 Foundation of Data Manipulation 1Chapter 1: The Basics 31-1. Retrieving Data from a Table 31-2. Selecting All Columns from a Table 51-3. Sorting Your Results 61-4. Adding Rows to a Table . 71-5. Copying Rows from One Table to Another 91-6. Copying Data in Bulk from One Table to Another 101-7. Changing Values in a Row . 101-8. Updating Multiple Fields with One Statement . 111-9. Removing Unwanted Rows from a Table 121-10. Removing All Rows from a Table 131-11. Selecting from the Results of Another Query 141-12. Basing a Where Condition on a Query . 151-13. Finding and Eliminating NULLs in Queries . 161-14. Sorting as a Person Expects . 181-15. Enabling Other Sorting and Comparison Options 201-16. Conditional Inserting or Updating Based on Existence . 21Chapter 2: Summarizing and Aggregating Data 232-1. Summarizing the Values in a Column 232-2. Summarizing Data for Different Groups . 262-3. Grouping Data by Multiple Fields 272-4. Ignoring Groups in Aggregate Data Sets 282-5. Aggregating Data at Multiple Levels 302-6. Using Aggregate Results in Other Queries 322-7. Counting Members in Groups and Sets 332-8. Finding Duplicates and Unique Values in a Table 352-9. Calculating Totals and Subtotals 372-10. Building Your Own Aggregate Function 392-11. Accessing Values from Subsequent or Preceding Rows 422-12. Assigning Ranking Values to Rows in a Query Result . 452-13. Finding First and Last Values within a Group 472-14. Performing Aggregations over Moving Windows 492-15. Removing Duplicate Rows Based on a Subset of Columns 512-16. Finding Sequence Gaps in a Table 55Chapter 3: Querying from Multiple Tables 593-1. Joining Corresponding Rows from Two or More Tables . 603-2. Stacking Query Results Vertically 623-3. Writing an Optional Join . 643-4. Making a Join Optional in Both Directions 653-5. Removing Rows Based on Data in Other Tables 673-6. Finding Matched Data Across Tables 683-7. Joining on Aggregates . 703-8. Finding Missing Rows . 713-9. Finding Rows that Tables Do Not Have in Common . 733-10. Generating Test Data 763-11. Updating Rows Based on Data in Other Tables 783-12. Manipulating and Comparing NULLs in Join Conditions . 80Chapter 4: Creating and Deriving Data . 834-1. Deriving New Columns 834-2. Returning Nonexistent Rows 874-3. Changing Rows into Columns 894-4. Pivoting on Multiple Columns 924-5. Changing Columns into Rows 954-6. Concatenating Data for Readability 974-7. Translating Strings to Numeric Equivalents 1004-8. Generating Random Data 1024-9. Creating a Comma-Separated Values File 105Chapter 5: Common Query Patterns 1095-1. Changing Nulls into Real Values 1095-2. Sorting on Null Values 1125-3. Paginating Query Results 1135-4. Testing for the Existence of Data 1175-5. Conditional Branching In One SQL Statement 1195-6. Conditional Sorting and Sorting By Function . 1205-7. Overcoming Issues and Errors when SubselectsReturn Unexpected Multiple Values 1225-8. Converting Numbers Between Different Bases 1245-9. Searching for a String Without Knowing the Column or Table . 1275-10. Predicting Data Values and Trends Beyond a Series End 1305-11. Explicitly (Pessimistically) Locking Rows for an Update . 1335-12. Synchronizing the Contents of Two Tables  138PART 2 Data Types and Their Problems 141Chapter 6: Working with Date and Time Values 1436-1. Converting Datetime Values into Readable Strings 1436-2. Converting Strings to Datetime Values . 1456-3. Detecting Overlapping Date Ranges 1466-4. Automatically Tracking Date and Time for Data Changes 1486-5. Generating a Gapless Time Series from Data with Gaps . 1506-6. Converting Dates and Times Between Time Zones 1526-7. Detecting Leap Years 1546-8. Computing the Last Date in a Month . 1556-9. Determining the First Date or Day in a Month 1566-10. Calculating the Day of the Week . 1576-11. Grouping and Aggregating by Time Periods 1596-12. Finding the Difference Between Two Dates or Date Parts . 1606-13. Determining the Dates of Easter for Any Year 1626-14. Calculating "X Day Active" Users for a Web Site . 164Chapter 7: Strings . 1677-1. Searching for a Substring 1677-2. Extracting a Substring 1707-3. Single-Character String Substitutions 1727-4. Searching for a Pattern 1747-5. Extracting a Pattern 1787-6. Counting Patterns 1797-7. Replacing Text in a String 1827-8. Speeding Up String Searches 184Chapter 8: Working with Numbers 1878-1. Converting Between String and Numeric Data Types 1878-2. Converting Between Numeric Data Types 1888-3. Choosing Data Type Precision and Scale 1908-4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers 1928-5. Validating Numbers in Strings 1948-6. Generating Consecutive Numbers 1968-7. Generating Numbers to a Formula or Pattern 1988-8. Handling Nulls in Numeric Calculations 2008-9. Automatically Rounding Numbers 2028-10. Automatically Generating Lists of Numbers 204PART 3 Your Development Environment 207Chapter 9: Managing Transactions 2099-1. Partially Rolling Back a Transaction 2099-2. Identifying Blocking Transactions 2139-3. Optimizing Row and Table Locking 2149-4. Avoiding Deadlock Scenarios 2169-5. Deferring Constraint Validation 2189-6. Ensuring Read-Consistency Across a Transaction . 2259-7. Managing Transaction Isolation Levels 226Chapter 10: Data Dictionary 229Graphical Tools vs. SQL . 229Data Dictionary Architecture 23010-1. Displaying User Information 23310-2. Determining the Tables You Can Access 23510-3. Displaying a Table's Disk Space Usage 23710-4. Displaying Table Row Counts . 24010-5. Displaying Indexes for a Table 24110-6. Showing Foreign Key Columns Not Indexed 24210-7. Displaying Constraints 24410-8. Showing Primary Key and Foreign Key Relationships 24610-9. Displaying Object Dependencies 24710-10. Displaying Synonym Metadata 25010-11. Displaying View Text 25110-12. Displaying Database Code 25310-13. Displaying Granted Roles . 25410-14. Displaying Object Privileges 25610-15. Displaying System Privileges . 257PART 4 Data Dictionary . 261Chapter 11: Common Reporting Problems 26311-1. Avoiding Repeating Rows in Reports . 26311-2. Parameterizing a SQL Report . 26611-3. Returning Detail Columns in Grouped Results 26911-4. Sorting Results into Equal-Size Buckets 27111-5. Creating Report Histograms 27311-6. Filtering Results by Relative Rank 27511-7. Comparing Hypotheses on Sets of Data 27711-8. Graphically Representing Data Distribution with Text 27911-9. Producing Web-Page Reports Directly from the Database . 280Chapter 12: Cleansing Data . 28712-1. Detecting Duplicate Rows 28712-2. Removing Duplicate Rows 28912-3. Determining if Data Can Be Loaded as Numeric 29012-4. Determining if Data Can Be Loaded as a Date 29112-5. Performing Case-Insensitive Queries . 29312-6. Obfuscating Values . 29412-7. Dropping All Indexes 29712-8. Disabling Constraints . 29912-9. Disabling Triggers 30412-10. Removing Data from a Table 30512-11. Showing Differences in Schemas 307Chapter 13: Tree-Structured Data 31313-1. Traversing Hierarchical Data from Top to Bottom 31513-2. Sorting Nodes Within a Hierarchical Level 31813-3. Generating Pathnames from Hierarchical Tables 32113-4. Identifying Leaf Data in a Hierarchical Table . 32413-5. Detecting Cycles in Hierarchical Data 32913-6. Generating a Fixed Number of Sequential Primary Keys . 330Chapter 14: Working with XML Data 33514-1. Translating SQL to XML . 33514-2. Storing XML in Native Form 33914-3. Shredding XML for Relational Use 34114-4. Extracting Key XML Elements from an XML Document . 34314-5. Generating Complex XML Documents 34414-6. Validating XML Schema 34614-7. Changing XML in Place . 349Chapter 15: Partitioning 35115-1. Determining if a Table Should be Partitioned 35315-2. Partitioning by Range . 35415-3. Partitioning by List 35515-4. Partitioning by Hash 35615-5. Partitioning a Table in Multiple Ways . 35715-6. Creating Partitions on Demand . 35915-7. Partitioning by Referential Constraints . 36015-8. Partitioning on a Virtual Column 36215-9. Application-Controlled Partitioning . 36315-10. Configuring Partitions with Tablespaces . 36415-11. Automatically Moving Updated Rows . 36515-12. Partitioning an Existing Table 36615-13. Adding a Partition to a Partitioned Table . 36815-14. Exchanging a Partition with an Existing Table 36915-15. Renaming a Partition 37115-16. Splitting a Partition . 37215-17. Merging Partitions 37315-18. Dropping a Partition 37515-19. Removing Rows from a Partition . 37615-20. Generating Statistics for a Partition 37715-21. Creating an Index that Maps to a Partition (Local Index) . 37715-22. Creating an Index with Its Own Partitioning Scheme (Global Index) 380Chapter 16: LOBs 38316-1. Loading Large Documents into CLOB Columns . 38416-2. Loading Image Data into BLOB Columns 38716-3. Using SQL*Loader to Bulk-Load Large Objects . 38916-4. Accessing Large Objects Using HTTP . 39116-5. Making External Large Objects (BFILEs) Available to the Database 39616-6. Deleting or Updating LOBs in a Database Table 398PART 5 Administration 401Chapter 17: Database Administration . 40317-1. Creating a Database 40417-2. Dropping a Database 40617-3. Verifying Connection Information . 40717-4. Creating Tablespaces . 40917-5. Dropping a Tablespace . 41117-6. Adjusting Tablespace Size 41217-7. Limiting Database Resources per Session 41317-8. Associating a Group of Privileges 41617-9. Creating Users 41917-10. Dropping Users . 42017-11. Modifying Passwords . 42117-12. Enforcing Password Complexity 422Chapter 18: Object Mamagement . 42518-1. Creating a Table 42518-2. Storing Data Temporarily . 42718-3. Moving a Table 42918-4. Renaming Objects 43018-5. Dropping a Table 43318-6. Undropping a Table . 43418-7. Creating an Index . 43518-8. Creating a Function-Based Index . 43818-9. Creating a Bitmap Index 43918-10. Creating an Index-Organized Table . 44018-11. Creating a View . 44118-12. Creating an Alternate Name for an Object 44318-13. Enforcing Unique Rows in a Table 44518-14. Ensuring Lookup Values Exist 44818-15. Checking Data for a Condition 44918-16. Creating a Connection Between Databases 45118-17. Creating an Auto-incrementing Value 453Chapter 19: SQL Monitoring and Tuning 45719-1. Monitoring Real-Time SQL Execution Statistics 45719-2. Displaying a Query's Progress in the Execution Plan . 45919-3. Determining How Much SQL Work Is Left 46219-4. Identifying Resource-Intensive SQL Statements 46319-5. Using Oracle Performance Reports to Identify Resource-Intensive SQL 46519-6. Using the Operating System to Identify Resource-Intensive Queries 46919-7. Displaying an Execution Plan Using AUTOTRACE . 47119-8. Generating an Execution Plan Using DBMS_XPLAN 47419-9. Tracing All SQL Statements for a Session 47619-10. Interpreting an Execution Plan 48319-11. Obtaining SQL Tuning Advice . 48819-12. Forcing Your Own Execution Plan on a Query 49019-13. Viewing Optimizer Statistics 49219-14. Generating Statistics 494Chapter 20: Database Troubleshooting . 49720-1. Determining Causes of Database Problems 49720-2. Displaying Open Cursors 50120-3. Determining If Online Redo Logs Are Sized Properly . 50320-4. Determining If Undo Is Sized Properly 50520-5. Determining If Temporary Tablespace Is Sized Correctly 50720-6. Displaying Tablespace Fullness 50920-7. Showing Object Sizes 51120-8. Monitoring Index Usage 51320-9. Auditing Object Usage 51420-10. Auditing at a Granular Level 516Index 519