Oracle Database 12c SQL

Description

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