Oracle Database 12c SQL

Title Page......Page 4
Copyright Page......Page 5
About the Author......Page 8
Contents at a Glance......Page 9
Contents......Page 10
Introduction......Page 31
What Is a Relational Database?......Page 36
Introducing Structured Query Language (SQL)......Page 37
Starting SQL*Plus......Page 39
Starting SQL*Plus from the Command Line......Page 40
Performing a SELECT Statement Using SQL*Plus......Page 41
Using SQL Developer......Page 42
Examining the Script......Page 44
Running the Script......Page 46
Examining the Store Data Definition Language Statements......Page 47
Adding a Row to a Table......Page 56
Removing a Row from a Table......Page 58
Introducing Oracle PL/SQL......Page 59
Summary......Page 60
Performing Single Table SELECT Statements......Page 62
Retrieving All Columns from a Table......Page 63
Row Identifiers......Page 64
Performing Arithmetic......Page 65
Performing Date Arithmetic......Page 66
Using Columns in Arithmetic......Page 67
Using Column Aliases......Page 68
Combining Column Output Using Concatenation......Page 69
Null Values......Page 70
Displaying Distinct Rows......Page 71
Using the Greater Than Operator......Page 72
Using the ALL Operator......Page 73
Using the LIKE Operator......Page 74
Using the BETWEEN Operator......Page 76
Using the AND Operator......Page 77
Sorting Rows Using the ORDER BY Clause......Page 78
Performing SELECT Statements That Use Two Tables......Page 80
Cartesian Products......Page 82
Performing SELECT Statements That Use More than Two Tables......Page 83
Join Conditions and Join Types......Page 84
Non-equijoins......Page 85
Outer Joins......Page 86
Self Joins......Page 89
Performing Inner Joins on Two Tables Using SQL/92......Page 90
Simplifying Joins with the USING Keyword......Page 91
Performing Inner Joins on Multiple Columns Using SQL/92......Page 92
Performing Outer Joins Using SQL/92......Page 93
Performing Cross Joins Using SQL/92......Page 94
Summary......Page 95
Viewing the Structure of a Table......Page 96
Editing SQL Statements......Page 97
Saving, Retrieving, and Running Files......Page 99
Formatting Columns......Page 103
Setting the Page Size......Page 105
Setting the Line Size......Page 106
Temporary Variables......Page 107
Defined Variables......Page 110
Using Temporary Variables in a Script......Page 113
Passing a Value to a Variable in a Script......Page 114
Adding a Header and Footer......Page 115
Computing Subtotals......Page 117
Getting Help from SQL*Plus......Page 118
Summary......Page 119
Using Single-Row Functions......Page 121
Character Functions......Page 122
Numeric Functions......Page 131
Conversion Functions......Page 137
Regular Expression Functions......Page 147
Using Aggregate Functions......Page 153
COUNT()......Page 154
MAX() and MIN()......Page 155
Grouping Rows......Page 156
Using the GROUP BY Clause to Group Rows......Page 157
Incorrect Usage of Aggregate Function Calls......Page 160
Using the WHERE and GROUP BY Clauses Together......Page 161
Using the WHERE, GROUP BY, and HAVING Clauses Together......Page 162
Summary......Page 163
Simple Examples of Storing and Retrieving Dates......Page 164
Converting Datetimes Using TO_CHAR() and TO_DATE()......Page 165
Using TO_CHAR() to Convert a Datetime to a String......Page 166
Using TO_DATE() to Convert a String to a Datetime......Page 170
Setting the Default Date Format......Page 173
Using the YY Format......Page 174
Using the RR Format......Page 175
Using Datetime Functions......Page 176
ADD_MONTHS()......Page 177
MONTHS_BETWEEN()......Page 178
ROUND()......Page 179
TRUNC()......Page 180
Types of Subqueries......Page 201
Using Other Single-Row Operators......Page 202
Subqueries in a HAVING Clause......Page 203
Subqueries in a FROM Clause (Inline Views)......Page 204
Errors You Might Encounter......Page 205
Writing Multiple-Row Subqueries......Page 206
Using ANY with a Multiple-Row Subquery......Page 207
Writing Correlated Subqueries......Page 208
Using EXISTS and NOT EXISTS with a Correlated Subquery......Page 209
Writing Nested Subqueries......Page 212
Writing an UPDATE Statement Containing a Subquery......Page 213
Using Subquery Factoring......Page 214
Summary......Page 215
Using the Set Operators......Page 217
The Example Tables......Page 218
Using the UNION ALL Operator......Page 219
Using the INTERSECT Operator......Page 220
Combining Set Operators......Page 221
Using the TRANSLATE() Function......Page 223
Using the DECODE() Function......Page 224
Using Analytic Functions......Page 253
The Example Table......Page 254
Using the Ranking Functions......Page 255
Using the Window Functions......Page 261
Using the Reporting Functions......Page 267
Using the LAG() and LEAD() Functions......Page 270
Using the Linear Regression Functions......Page 271
Using the Hypothetical Rank and Distribution Functions......Page 273
An Example of the MODEL Clause......Page 274
Accessing All Cells Using ANY and IS ANY......Page 276
Getting the Current Value of a Dimension Using CURRENTV()......Page 277
Accessing Cells Using a FOR Loop......Page 278
Handling Null and Missing Values......Page 279
Updating Existing Cells......Page 281
A Simple Example of the PIVOT Clause......Page 282
Pivoting on Multiple Columns......Page 284
Using the UNPIVOT Clause......Page 285
Performing Top-N Queries......Page 286
Using the FETCH FIRST Clause......Page 287
Using the PERCENT Clause......Page 288
Finding Patterns in Data......Page 289
Finding V-Shaped Data Patterns in the all_sales2 Table......Page 290
Finding W-Shaped Data Patterns in the all_sales3 Table......Page 293
Finding V-Shaped Data Patterns in the all_sales3 Table......Page 294
Summary......Page 296
Adding Rows Using the INSERT Statement......Page 297
Omitting the Column List......Page 298
Copying Rows from One Table to Another......Page 299
Modifying Rows Using the UPDATE Statement......Page 300
Returning an Aggregate Function Value Using the RETURNING Clause......Page 301
Enforcement of Primary Key Constraints......Page 302
Enforcement of Foreign Key Constraints......Page 303
Using Default Values......Page 304
Merging Rows Using MERGE......Page 305
Database Transactions......Page 307
Committing and Rolling Back a Transaction......Page 308
Savepoints......Page 309
ACID Transaction Properties......Page 311
Concurrent Transactions......Page 312
Transaction Locking......Page 313
Transaction Isolation Levels......Page 314
A SERIALIZABLE Transaction Example......Page 315
Granting the Privilege for Using Flashbacks......Page 316
Time Query Flashbacks......Page 317
System Change Number Query Flashbacks......Page 318
Summary......Page 319
Chapter 10: Users, Privileges, and Roles......Page 321
Creating a User......Page 322
Changing a User’s Password......Page 323
System Privileges......Page 324
Granting System Privileges to a User......Page 325
Making Use of System Privileges......Page 326
Object Privileges......Page 327
Granting Object Privileges to a User......Page 328
Checking Object Privileges Made......Page 329
Checking Object Privileges Received......Page 330
Creating Synonyms......Page 331
Creating Public Synonyms......Page 332
Roles......Page 333
Granting Privileges to Roles......Page 334
Checking Roles Granted to a User......Page 335
Checking System Privileges Granted to a Role......Page 336
Checking Object Privileges Granted to a Role......Page 337
Making Use of Privileges Granted to a Role......Page 338
Enabling and Disabling Roles......Page 339
Auditing......Page 340
Auditing Examples......Page 341
Audit Trail Views......Page 343
Summary......Page 344
Creating a Table......Page 345
Getting Information on Tables......Page 347
Altering a Table......Page 348
Adding a Comment to a Table......Page 358
Dropping a Table......Page 359
Using the BINARY_FLOAT and BINARY_DOUBLE Types......Page 360
Using DEFAULT ON NULL Columns......Page 362
Using Visible and Invisible Columns in a Table......Page 363
Creating a Sequence......Page 365
Retrieving Information on Sequences......Page 367
Using a Sequence......Page 368
Populating a Primary Key Using a Sequence......Page 369
Specifying a Default Column Value Using a Sequence......Page 370
Using Identity Columns......Page 371
Dropping a Sequence......Page 372
Creating a B-tree Index......Page 373
Retrieving Information on Indexes......Page 375
Retrieving Information on the Indexes on a Column......Page 376
Creating a Bitmap Index......Page 377
Views......Page 378
Creating and Using a View......Page 379
Modifying a View......Page 386
Using Visible and Invisible Columns in a View......Page 387
Flashback Data Archives......Page 388
Summary......Page 391
Chapter 12: Introducing PL/SQL Programming......Page 392
Block Structure......Page 393
Conditional Logic......Page 395
Simple Loops......Page 396
WHILE Loops......Page 397
FOR Loops......Page 398
Step 2: Declare the Cursor......Page 399
Step 4: Fetch the Rows from the Cursor......Page 400
Complete Example: product_cursor.sql......Page 401
Cursors and FOR Loops......Page 402
OPEN-FOR Statement......Page 403
Unconstrained Cursors......Page 405
Exceptions......Page 406
ZERO_DIVIDE Exception......Page 408
OTHERS Exception......Page 409
Creating a Procedure......Page 410
Calling a Procedure......Page 413
Getting Information on Procedures......Page 414
Functions......Page 415
Creating a Function......Page 416
Getting Information on Functions......Page 417
Creating a Package Specification......Page 418
Creating a Package Body......Page 419
Calling Functions and Procedures in a Package......Page 420
Triggers......Page 421
Creating a Trigger......Page 422
Getting Information on Triggers......Page 425
Dropping a Trigger......Page 427
SIMPLE_INTEGER Type......Page 428
Sequences in PL/SQL......Page 429
PL/SQL Native Machine Code Generation......Page 430
Summary......Page 431
Introducing Objects......Page 433
Creating Object Types......Page 434
Using DESCRIBE to Get Information on Object Types......Page 436
Column Objects......Page 437
Object Tables......Page 440
Object Identifiers and Object References......Page 443
Comparing Object Values......Page 445
Using Objects in PL/SQL......Page 447
The get_products() Function......Page 448
The display_product() Procedure......Page 449
The update_product_price() Procedure......Page 450
The get_product() Function......Page 451
The get_product_ref() Function......Page 452
The product_lifecycle() Procedure......Page 453
The product_lifecycle2() Procedure......Page 454
Type Inheritance......Page 455
Running the Script to Create the Second Object Schema......Page 456
Inheriting Attributes......Page 457
SQL Examples......Page 458
PL/SQL Examples......Page 459
NOT SUBSTITUTABLE Objects......Page 460
IS OF()......Page 461
TREAT()......Page 464
NOT INSTANTIABLE Object Types......Page 468
User-Defined Constructors......Page 470
Overriding Methods......Page 473
Inheriting Attributes......Page 475
Summary......Page 477
Introducing Collections......Page 478
Creating a Varray Type......Page 479
Using a Collection Type to Define a Column in a Table......Page 480
Getting Information on a Varray......Page 481
Getting Information on a Nested Table......Page 482
Populating a Nested Table with Elements......Page 484
Retrieving Elements from a Varray......Page 485
Using TABLE() with a Varray......Page 486
Introducing Large Objects (LOBs)......Page 523
Large Object Types......Page 524
Creating Tables Containing Large Objects......Page 525
Using CLOBs and BLOBs......Page 527
Using BFILEs......Page 529
Using Large Objects in PL/SQL......Page 531
APPEND()......Page 533
COMPARE()......Page 534
CREATETEMPORARY()......Page 536
ERASE()......Page 537
FILECLOSEALL()......Page 538
FILEGETNAME()......Page 539
FILEOPEN()......Page 540
GETCHUNKSIZE()......Page 541
GETLENGTH()......Page 542
INSTR()......Page 543
ISOPEN()......Page 544
LOADFROMFILE()......Page 545
LOADBLOBFROMFILE()......Page 546
LOADCLOBFROMFILE()......Page 547
OPEN()......Page 548
READ()......Page 549
SUBSTR()......Page 550
WRITE()......Page 551
WRITEAPPEND()......Page 552
Example PL/SQL Procedures......Page 553
The Example Tables......Page 567
Converting LONG and LONG RAW Columns to LOBs......Page 568
Implicit Conversion Between CLOB and NCLOB Objects......Page 569
Encrypting LOB Data......Page 571
Compressing LOB Data......Page 576
Oracle Database 12c Enhancement to Large Objects......Page 577
Summary......Page 579
Introducing SQL Tuning......Page 580
Use Table Joins Rather than Multiple Queries......Page 581
Use Fully Qualified Column References When Performing Joins......Page 582
Use CASE Expressions Rather than Multiple Queries......Page 583
Add Indexes to Tables......Page 584
Use WHERE Rather than HAVING......Page 585
Use UNION ALL Rather than UNION......Page 586
Use EXISTS Rather than IN......Page 587
Use EXISTS Rather than DISTINCT......Page 588
Non-Identical SQL Statements......Page 589
Identical SQL Statements That Use Bind Variables......Page 590
Using a Bind Variable to Store Rows from a REFCURSOR......Page 591
Examining Execution Plans......Page 592
Comparing Execution Plans......Page 598
Passing Hints to the Optimizer......Page 599
Automatic Database Diagnostic Monitor......Page 601
Real-Time SQL Monitoring......Page 602
Summary......Page 603
Introducing XML......Page 604
XMLELEMENT()......Page 605
XMLATTRIBUTES()......Page 608
XMLFOREST()......Page 609
XMLAGG()......Page 610
XMLCONCAT()......Page 612
XMLPARSE()......Page 613
XMLSEQUENCE()......Page 614
XMLSERIALIZE()......Page 615
A PL/SQL Example That Writes XML Data to a File......Page 616
XMLQUERY()......Page 618
Creating the Example XML Schema......Page 622
Retrieving Information from the Example XML Schema......Page 624
Updating Information in the Example XML Schema......Page 628
Summary......Page 632
Oracle SQL Types......Page 633
Oracle PL/SQL Types......Page 634
Index......Page 636
Time Zone Functions......Page 181
The Database Time Zone and Session Time Zone......Page 182
Obtaining Time Zone Offsets......Page 183
Converting a Datetime from One Time Zone to Another......Page 184
Using the Timestamp Types......Page 185
Timestamp Functions......Page 189
Using Time Intervals......Page 194
Using the INTERVAL YEAR TO MONTH Type......Page 195
Using the INTERVAL DAY TO SECOND Type......Page 197
Time Interval Functions......Page 198
Summary......Page 200
Using the CASE Expression......Page 225
Using Searched CASE Expressions......Page 226
The Example Data......Page 228
Using the LEVEL Pseudo Column......Page 230
Formatting the Results from a Hierarchical Query......Page 231
Using a Subquery in a START WITH Clause......Page 232
Eliminating Nodes and Branches from a Hierarchical Query......Page 233
Using Recursive Subquery Factoring to Query Hierarchical Data......Page 234
The Example Tables......Page 238
Using the ROLLUP Clause......Page 240
Using the CUBE Clause......Page 242
Using the GROUPING() Function......Page 243
Using the GROUPING SETS Clause......Page 245
Using the GROUPING_ID() Function......Page 246
Using a Column Multiple Times in a GROUP BY Clause......Page 247
Using the GROUP_ID() Function......Page 248
Using CROSS APPLY and OUTER APPLY......Page 249
OUTER APPLY......Page 250
Summary......Page 251
Using TABLE() with a Nested Table......Page 487
Modifying Elements of a Varray......Page 488
Using a Map Method to Compare the Contents of Nested Tables......Page 489
Using CAST() to Convert a Varray to a Nested Table......Page 492
Using Collections in PL/SQL......Page 493
Manipulating a Varray......Page 494
Manipulating a Nested Table......Page 495
Using PL/SQL Collection Methods......Page 497
Creating and Using Multilevel Collections......Page 507
Using Multilevel Collections......Page 508
Oracle Database 10g Enhancements to Collections......Page 510
Creating Associative Arrays......Page 511
Changing the Size of an Element Type......Page 512
Using a Different Tablespace for a Nested Table’s Storage Table......Page 513
ANSI Support for Nested Tables......Page 514
Summary......Page 522