Mastering Oracle SQL

Table of Contents......Page 9
 Why We Wrote This Book......Page 13
 What’s New in Oracle SQL?......Page 14
 Audience for This Book......Page 15
 Structure of This Book......Page 16
 Conventions Used in This Book......Page 18
 Comments and Questions......Page 19
  From Sanjay......Page 20
  From Alan......Page 21
 What Is SQL?......Page 23
  Oracle’s SQL Implementation......Page 25
 A Simple Database......Page 26
 DML Statements......Page 27
  The SELECT Statement......Page 28
   SELECT clause elements......Page 30
   Ordering your results......Page 32
   Single-table inserts......Page 33
   Multitable inserts......Page 34
  The DELETE Statement......Page 36
  The UPDATE Statement......Page 37
  The MERGE Statement......Page 38
 So Why Are There 17 More Chapters?......Page 39
 Life Without WHERE......Page 40
 WHERE to the Rescue......Page 41
 WHERE Clause Evaluation......Page 42
  Equality/Inequality Conditions......Page 45
  Membership Conditions......Page 46
  Matching Conditions......Page 47
  Regular Expressions......Page 48
  Handling NULL......Page 49
  Placement of Join Conditions......Page 51
 WHERE to Go from Here......Page 52
 What Is a Join Query?......Page 53
 Join Conditions......Page 54
  The USING Clause......Page 55
  Conditions Involving Multiple Columns......Page 56
  The Natural Join Clause......Page 57
 Types of Joins......Page 59
  Cross Joins/Cartesian Products......Page 60
  Inner Joins......Page 61
  Outer Joins......Page 63
   Right outer joins......Page 64
   Full outer joins......Page 65
  Self Joins......Page 66
   Self outer joins......Page 67
   Self non-equi-joins......Page 68
  Partition Outer Joins......Page 71
 DML Statements on a Join View......Page 75
  Key-Preserved Tables......Page 76
  INSERT Statements on a Join View......Page 78
  DELETE Statements on a Join View......Page 79
  Data Dictionary Views to Find Updatable Columns......Page 80
  Impact of WITH CHECK OPTION......Page 81
 Aggregate Functions......Page 84
  NULLs and Aggregate Functions......Page 86
  Use of DISTINCT and ALL......Page 87
  Correspondence Between SELECT and GROUP BY......Page 88
   Aggregate functions not allowed in GROUP BY clause......Page 89
   Scalar functions may be grouped by their underlying column......Page 90
   Concatenated columns may be grouped in either of two ways......Page 91
   You can sometimes exclude a nonaggregate expression from the GROUP BY clause......Page 92
   You are not required to show your GROUP BY columns......Page 93
  GROUP BY Clause and NULL Values......Page 94
 The HAVING Clause......Page 96
 Nested Group Operations......Page 99
 What Is a Subquery?......Page 102
  Single-Row, Single-Column Subqueries......Page 103
  Multiple-Row, Single-Column Subqueries......Page 105
  Multiple-Column Subqueries......Page 107
  The WITH Clause......Page 109
 Correlated Subqueries......Page 110
 Inline Views......Page 113
  Query Execution......Page 114
  Data Set Fabrication......Page 116
   Hierarchical queries......Page 120
   Aggregate queries......Page 122
  Inline Views in DML Statements......Page 124
  Restricting Access Using WITH CHECK OPTION......Page 125
  Global Inline Views......Page 126
  A Look at the Data......Page 128
  Your Assignment......Page 129
  Second Attempt......Page 130
  Final Answer......Page 131
 Time Zones......Page 134
  Database Time Zone......Page 135
 Temporal Data Types in Oracle......Page 136
   TIMESTAMP......Page 137
   TIMESTAMP WITH TIME ZONE......Page 138
  The INTERVAL Data Types......Page 139
   INTERVAL YEAR TO MONTH......Page 140
   INTERVAL DAY TO SECOND......Page 141
  TIMESTAMP Literals......Page 142
   YEAR TO MONTH interval literals......Page 145
   DAY TO SECOND interval literals......Page 146
 Getting Temporal Data In and Out of a Database......Page 148
   Using the default formats......Page 149
   Specifying a format......Page 151
   Converting to TIMESTAMP WITH LOCAL TIME ZONE......Page 152
  NUMTOYMINTERVAL and NUMTODSINTERVAL......Page 153
  TO_CHAR......Page 154
 Date and Time Formats......Page 156
  AD/BC Indicators......Page 159
  Case-Sensitivity of Formats......Page 160
  Two-Digit Years......Page 162
   ISO standard weeks......Page 165
   ISO standard year......Page 166
  Database Parameters......Page 167
  Using the Built-in Temporal Functions......Page 168
   Adding numbers to a DATE......Page 171
   Adding months to a DATE......Page 172
   Adding true INTERVAL values rather than numbers......Page 173
   Subtracting one DATE from another......Page 174
   Subtracting months from a DATE......Page 175
   Time interval between two DATEs......Page 176
   Subtracting an INTERVAL from a DATE or TIMESTAMP......Page 178
  Determining the First Day of the Month......Page 179
  Rounding and Truncating Dates......Page 180
  SELECTing Data Based on Date Ranges......Page 182
  Creating a Date Pivot Table......Page 184
  Summarizing by a Date/Time Element......Page 186
Set Operations......Page 188
 Set Operators......Page 189
  UNION ALL......Page 190
  UNION......Page 191
  MINUS......Page 192
 Precedence of Set Operators......Page 194
 Comparing Two Tables......Page 195
 Using NULLs in Compound Queries......Page 198
 Rules and Restrictions on Set Operations......Page 200
 Representing Hierarchical Information......Page 203
  Finding Root Nodes......Page 206
  Finding a Node’s Immediate Parent......Page 207
  Finding Leaf Nodes......Page 208
  Tree Traversal Using ANSI SQL......Page 209
  START WITH...CONNECT BY and PRIOR......Page 210
  The LEVEL Pseudocolumn......Page 214
  Listing Records in Hierarchical Order......Page 215
  Checking for Ascendancy......Page 218
  Listing Multiple Root Nodes......Page 219
  Aggregating a Hierarchy......Page 220
  Ordering Hierarchical Data......Page 222
  Finding the Path to a Node......Page 223
  Getting Data from the Root Row......Page 226
  Ignoring Cycles......Page 227
  Identifying Leaf Nodes......Page 229
 DECODE, NULLIF, NVL, and NVL2......Page 232
  DECODE......Page 233
  NULLIF......Page 234
  NVL and NVL2......Page 235
 The Case for CASE......Page 236
  Searched CASE Expressions......Page 237
 DECODE and CASE Examples......Page 239
  Result Set Transformations......Page 240
  Selective Function Execution......Page 241
  Conditional Update......Page 242
  Optional Update......Page 243
  Selective Aggregation......Page 245
  Checking for Existence......Page 247
  Division by Zero Errors......Page 248
  State Transitions......Page 249
 Partitioning Concepts......Page 252
 Partitioning Indexes......Page 253
  Range Partitioning......Page 254
  Hash Partitioning......Page 255
  Composite Range-Hash Partitioning......Page 256
  Composite Range-List Partitioning......Page 257
 Specifying Partitions......Page 260
 Partition Pruning......Page 261
 What Is PL/SQL?......Page 265
 Procedures, Functions, and Packages......Page 266
 Calling Stored Functions from Queries......Page 268
  Stored Functions and Views......Page 270
  Avoiding Table Joins......Page 271
  Deterministic Functions......Page 272
 Restrictions on Calling PL/SQL from SQL......Page 273
  Purity Level......Page 274
  Other Restrictions......Page 275
 Stored Functions in DML Statements......Page 276
 The SQL Inside Your PL/SQL......Page 278
 Object Types......Page 280
  Subtypes......Page 282
  Object Attributes......Page 283
  Object Tables......Page 285
  Object Parameters......Page 287
  Nested Tables......Page 289
 Collection Instantiation......Page 290
 Querying Collections......Page 291
 Collection Unnesting......Page 292
 Collection Functions......Page 293
 Comparing Collections......Page 295
 Manipulating Collections......Page 297
 Multilevel Collections......Page 298
  DML Operations on Multilevel Collections......Page 299
 Multiple Summary Levels......Page 301
  UNION......Page 302
  ROLLUP......Page 305
  Partial ROLLUPs......Page 309
  CUBE......Page 311
  Partial CUBE......Page 316
  The GROUPING Function......Page 317
  GROUPING SETS......Page 322
 Pushing the GROUPING Envelope......Page 323
  Repeated Column Names in the GROUP BY Clause......Page 324
  Grouping on Composite Columns......Page 325
  Concatenated Groupings......Page 327
   Concatenated groupings with GROUPING SETS......Page 329
   ROLLUP and CUBE as arguments to GROUPING SETS......Page 331
 The GROUPING_ID and GROUP_ID Functions......Page 333
  GROUPING_ID......Page 334
  GROUPING and GROUPING_ID in ORDER BY......Page 338
  GROUP_ID......Page 339
 Analytic SQL Overview......Page 342
  RANK, DENSE_RANK, and ROW_NUMBER......Page 348
   Top/bottom N queries......Page 352
   FIRST/LAST......Page 353
  NTILE......Page 354
  WIDTH_BUCKET......Page 356
  CUME_DIST and PERCENT_RANK......Page 358
  Hypothetical Functions......Page 360
 Windowing Functions......Page 361
  Working with Ranges......Page 364
  FIRST_VALUE and LAST_VALUE......Page 366
  LAG/LEAD Functions......Page 367
 Reporting Functions......Page 368
  Report Partitions......Page 370
  RATIO_TO_REPORT......Page 371
 Summary......Page 373
  EXISTS Is Preferable to DISTINCT......Page 374
  WHERE Versus HAVING......Page 375
  UNION Versus UNION ALL......Page 376
  LEFT Versus RIGHT OUTER JOIN......Page 378
 Avoid Unnecessary Parsing......Page 380
  Using Bind Variables......Page 381
  Using Table Aliases......Page 382
 Consider Literal SQL for Decision-Support Systems......Page 385
 What Is XML?......Page 387
 Storing XML Data......Page 388
  Storing XML as a CLOB......Page 389
   XPath......Page 390
   The extract() member function......Page 391
   The extractValue() member function......Page 392
   The existsNode() member function......Page 393
   Moving data to relational tables......Page 394
  Storing XML as a Set of Objects......Page 397
   Assigning the schema to a column......Page 398
   Inserting data......Page 399
   XMLType validity functions......Page 401
   Updating document content......Page 402
 Generating XML Documents......Page 404
  XMLAgg()......Page 405
  Putting It All Together......Page 407
 Summary......Page 409
  Matching a Single Character......Page 411
  Matching Any of a Set of Characters......Page 413
  Defining Alternate Possibilities......Page 416
  Subexpressions......Page 419
  Anchoring an Expression......Page 420
  Understanding Greediness......Page 422
 Advanced Function Options......Page 423
  Using Backreferences......Page 427
  Using Named Character Classes......Page 433
  Specifying Collation Elements......Page 435
  Defining Equivalence Classes......Page 437
 Basic Elements of a Model Query......Page 439
  Symbolic Cell References......Page 441
  Combined Positional and Symbolic References......Page 442
  NULL Measures and Missing Cells......Page 443
  UNIQUE DIMENSION/UNIQUE SINGLE REFERENCE......Page 444
  Returning Rows......Page 448
   CV()......Page 449
   ANY......Page 450
   FOR loops......Page 451
   IS ANY......Page 454
   IS PRESENT......Page 455
   PRESENTV......Page 456
  Range References on the Righthand Side......Page 457
   SEQUENTIAL ORDER......Page 458
   AUTOMATIC ORDER......Page 460
  Creating and Updating Cells......Page 462
 Iterative Models......Page 464
  Knowing how many iterations have occurred......Page 465
  Referencing values from the previous iteration......Page 466
 Reference Models......Page 467
 Old Inner Join Syntax......Page 471
  Restrictions on Old Outer Join Syntax......Page 472
  Full Outer Join Using the Old Syntax......Page 475
 Advantages of the New Join Syntax......Page 476
Index......Page 477