Oracle Database 12c SQL

Oracle Database 12c SQL

by Jason Price
Released June 2013
Publisher(s): McGraw Hill Computing
ISBN: 9780071799362

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Write Powerful SQL Statements and PL/SQL Programs

Learn how to access Oracle databases through SQL statements and construct PL/SQL programs. Oracle Database 12c SQL offers complete coverage of the latest database features and techniques. Find out how to write SQL statements to retrieve and modify database information, use SQL*Plus and SQL Developer, work with database objects, write PL/SQL programs, use performance optimization techniques, incorporate XML, and more. This Oracle Press guide contains everything you need to know to master SQL.

  • Use SQL statements to access an Oracle database
  • Work with SQL*Plus and SQL Developer
  • Write PL/SQL programs
  • Create tables, sequences, indexes, views, and triggers
  • Design advanced queries containing complex calculations
  • Create database objects to handle abstract data
  • Use date, time stamp, and time interval data types
  • Establish user roles and privileges
  • Handle multimedia files using large objects
  • Tune SQL statements to make them execute faster
  • Generate, process, and store XML data
  • Master the very latest Oracle Database 12c features

Code examples in the book are available for download.

Table of contents

  1. Cover 
  2. Title Page
  3. Copyright Page
  4. About the Author
  5. Contents at a Glance
  6. Contents 
  7. Introduction
  8. Chapter 1: Introduction
    1. What Is a Relational Database?
    2. Introducing Structured Query Language (SQL)
    3. Using SQL*Plus
      1. Starting SQL*Plus
      2. Starting SQL*Plus from the Command Line
      3. Performing a SELECT Statement Using SQL*Plus
    4. Using SQL Developer
    5. Creating the Store Schema
      1. Examining the Script
      2. Running the Script
      3. Examining the Store Data Definition Language Statements
    6. Adding, Modifying, and Removing Rows
      1. Adding a Row to a Table
      2. Modifying an Existing Row in a Table
      3. Removing a Row from a Table
    7. Connecting to and Disconnecting from a Database
    8. Quitting SQL*Plus
    9. Introducing Oracle PL/SQL
    10. Summary
  9. Chapter 2: Retrieving Information from Database Tables
    1. Performing Single Table SELECT Statements
    2. Retrieving All Columns from a Table
    3. Limiting Rows to Retrieve Using the WHERE Clause
    4. Row Identifiers
    5. Row Numbers
    6. Performing Arithmetic
      1. Performing Date Arithmetic
      2. Using Columns in Arithmetic
      3. Arithmetic Operator Precedence
    7. Using Column Aliases
    8. Combining Column Output Using Concatenation
    9. Null Values
    10. Displaying Distinct Rows
    11. Comparing Values
      1. Using the Not Equal Operator
      2. Using the Greater Than Operator
      3. Using the Less Than Or Equal To Operator
      4. Using the ANY Operator
      5. Using the ALL Operator
    12. Using the SQL Operators
      1. Using the LIKE Operator
      2. Using the IN Operator
      3. Using the BETWEEN Operator
    13. Using the Logical Operators
      1. Using the AND Operator
      2. Using the OR Operator
      3. Logical Operator Precedence
    14. Sorting Rows Using the ORDER BY Clause
    15. Performing SELECT Statements That Use Two Tables
    16. Using Table Aliases
    17. Cartesian Products
    18. Performing SELECT Statements That Use More than Two Tables
    19. Join Conditions and Join Types
      1. Non-equijoins
      2. Outer Joins
      3. Self Joins
    20. Performing Joins Using the SQL/92 Syntax
      1. Performing Inner Joins on Two Tables Using SQL/92
      2. Simplifying Joins with the USING Keyword
      3. Performing Inner Joins on More than Two Tables Using SQL/92
      4. Performing Inner Joins on Multiple Columns Using SQL/92
      5. Performing Outer Joins Using SQL/92
      6. Performing Self Joins Using SQL/92
      7. Performing Cross Joins Using SQL/92
    21. Summary
  10. Chapter 3: Using SQL*Plus
    1. Viewing the Structure of a Table
    2. Editing SQL Statements
    3. Saving, Retrieving, and Running Files
    4. Formatting Columns
    5. Setting the Page Size
    6. Setting the Line Size
    7. Clearing Column Formatting
    8. Using Variables
      1. Temporary Variables
      2. Defined Variables
    9. Creating Simple Reports
      1. Using Temporary Variables in a Script
      2. Using Defined Variables in a Script
      3. Passing a Value to a Variable in a Script
      4. Adding a Header and Footer
      5. Computing Subtotals
    10. Getting Help from SQL*Plus
    11. Automatically Generating SQL Statements
    12. Disconnecting from the Database and Exiting SQL*Plus
    13. Summary
  11. Chapter 4: Using Simple Functions
    1. Types of Functions
    2. Using Single-Row Functions
      1. Character Functions
      2. Numeric Functions
      3. Conversion Functions
      4. Regular Expression Functions
    3. Using Aggregate Functions
      1. AVG()
      2. COUNT()
      3. MAX() and MIN()
      4. STDDEV()
      5. SUM()
      6. VARIANCE()
    4. Grouping Rows
      1. Using the GROUP BY Clause to Group Rows
      2. Incorrect Usage of Aggregate Function Calls
      3. Using the HAVING Clause to Filter Groups of Rows
      4. Using the WHERE and GROUP BY Clauses Together
      5. Using the WHERE, GROUP BY, and HAVING Clauses Together
    5. Summary
  12. Chapter 5: Storing and Processing Dates and Times
    1. Simple Examples of Storing and Retrieving Dates
    2. Converting Datetimes Using TO_CHAR() and TO_DATE()
      1. Using TO_CHAR() to Convert a Datetime to a String
      2. Using TO_DATE() to Convert a String to a Datetime
    3. Setting the Default Date Format
    4. How Oracle Interprets Two-Digit Years
      1. Using the YY Format
      2. Using the RR Format
    5. Using Datetime Functions
      1. ADD_MONTHS()
      2. LAST_DAY()
      3. MONTHS_BETWEEN()
      4. NEXT_DAY()
      5. ROUND()
      6. SYSDATE
      7. TRUNC()
    6. Using Time Zones
      1. Time Zone Functions
      2. The Database Time Zone and Session Time Zone
      3. Obtaining Time Zone Offsets
      4. Obtaining Time Zone Names
      5. Converting a Datetime from One Time Zone to Another
    7. Using Timestamps
      1. Using the Timestamp Types
      2. Timestamp Functions
    8. Using Time Intervals
      1. Using the INTERVAL YEAR TO MONTH Type
      2. Using the INTERVAL DAY TO SECOND Type
      3. Time Interval Functions
    9. Summary
  13. Chapter 6: Subqueries
    1. Types of Subqueries
    2. Writing Single-Row Subqueries
      1. Subqueries in a WHERE Clause
      2. Using Other Single-Row Operators
      3. Subqueries in a HAVING Clause
      4. Subqueries in a FROM Clause (Inline Views)
      5. Errors You Might Encounter
    3. Writing Multiple-Row Subqueries
      1. Using IN with a Multiple-Row Subquery
      2. Using ANY with a Multiple-Row Subquery
      3. Using ALL with a Multiple-Row Subquery
    4. Writing Multiple-Column Subqueries
    5. Writing Correlated Subqueries
      1. A Correlated Subquery Example
      2. Using EXISTS and NOT EXISTS with a Correlated Subquery
    6. Writing Nested Subqueries
    7. Writing UPDATE and DELETE Statements Containing Subqueries
      1. Writing an UPDATE Statement Containing a Subquery
      2. Writing a DELETE Statement Containing a Subquery
    8. Using Subquery Factoring
    9. Summary
  14. Chapter 7: Advanced Queries
    1. Using the Set Operators
      1. The Example Tables
      2. Using the UNION ALL Operator
      3. Using the UNION Operator
      4. Using the INTERSECT Operator
      5. Using the MINUS Operator
      6. Combining Set Operators
    2. Using the TRANSLATE() Function
    3. Using the DECODE() Function
    4. Using the CASE Expression
      1. Using Simple CASE Expressions
      2. Using Searched CASE Expressions
    5. Hierarchical Queries
      1. The Example Data
      2. Using the CONNECT BY and START WITH Clauses
      3. Using the LEVEL Pseudo Column
      4. Formatting the Results from a Hierarchical Query
      5. Starting at a Node Other than the Root
      6. Using a Subquery in a START WITH Clause
      7. Traversing Upward Through the Tree
      8. Eliminating Nodes and Branches from a Hierarchical Query
      9. Including Other Conditions in a Hierarchical Query
      10. Using Recursive Subquery Factoring to Query Hierarchical Data
    6. Using the ROLLUP and CUBE Clauses
      1. The Example Tables
      2. Using the ROLLUP Clause
      3. Using the CUBE Clause
      4. Using the GROUPING() Function
      5. Using the GROUPING SETS Clause
      6. Using the GROUPING_ID() Function
      7. Using a Column Multiple Times in a GROUP BY Clause
      8. Using the GROUP_ID() Function
    7. Using CROSS APPLY and OUTER APPLY
      1. CROSS APPLY
      2. OUTER APPLY
    8. Using LATERAL
    9. Summary
  15. Chapter 8: Analyzing Data
    1. Using Analytic Functions
      1. The Example Table
      2. Using the Ranking Functions
      3. Using the Inverse Percentile Functions
      4. Using the Window Functions
      5. Using the Reporting Functions
      6. Using the LAG() and LEAD() Functions
      7. Using the FIRST and LAST Functions
      8. Using the Linear Regression Functions
      9. Using the Hypothetical Rank and Distribution Functions
    2. Using the MODEL Clause
      1. An Example of the MODEL Clause
      2. Using Positional and Symbolic Notation to Access Cells
      3. Accessing a Range of Cells Using BETWEEN and AND
      4. Accessing All Cells Using ANY and IS ANY
      5. Getting the Current Value of a Dimension Using CURRENTV()
      6. Accessing Cells Using a FOR Loop
      7. Handling Null and Missing Values
      8. Updating Existing Cells
    3. Using the PIVOT and UNPIVOT Clauses
      1. A Simple Example of the PIVOT Clause
      2. Pivoting on Multiple Columns
      3. Using Multiple Aggregate Functions in a Pivot
      4. Using the UNPIVOT Clause
    4. Performing Top-N Queries
      1. Using the FETCH FIRST Clause
      2. Using the OFFSET Clause
      3. Using the PERCENT Clause
      4. Using the WITH TIES Clause
    5. Finding Patterns in Data
      1. Finding V-Shaped Data Patterns in the all_sales2 Table
      2. Finding W-Shaped Data Patterns in the all_sales3 Table
      3. Finding V-Shaped Data Patterns in the all_sales3 Table
    6. Summary
  16. Chapter 9: Changing Table Contents
    1. Adding Rows Using the INSERT Statement
      1. Omitting the Column List
      2. Specifying a Null Value for a Column
      3. Including Quote Marks in a Column Value
      4. Copying Rows from One Table to Another
    2. Modifying Rows Using the UPDATE Statement
    3. Returning an Aggregate Function Value Using the RETURNING Clause
    4. Removing Rows Using the DELETE Statement
    5. Database Integrity
      1. Enforcement of Primary Key Constraints
      2. Enforcement of Foreign Key Constraints
    6. Using Default Values
    7. Merging Rows Using MERGE
    8. Database Transactions
      1. Committing and Rolling Back a Transaction
      2. Starting and Ending a Transaction
      3. Savepoints
      4. ACID Transaction Properties
      5. Concurrent Transactions
      6. Transaction Locking
      7. Transaction Isolation Levels
      8. A SERIALIZABLE Transaction Example
    9. Query Flashbacks
      1. Granting the Privilege for Using Flashbacks
      2. Time Query Flashbacks
      3. System Change Number Query Flashbacks
    10. Summary
  17. Chapter 10: Users, Privileges, and Roles
    1. A Very Short Introduction to Database Storage
    2. Users
      1. Creating a User
      2. Changing a User’s Password
      3. Deleting a User
    3. System Privileges
      1. Granting System Privileges to a User
      2. Checking System Privileges Granted to a User
      3. Making Use of System Privileges
      4. Revoking System Privileges from a User
    4. Object Privileges
      1. Granting Object Privileges to a User
      2. Checking Object Privileges Made
      3. Checking Object Privileges Received
      4. Making Use of Object Privileges
      5. Creating Synonyms
      6. Creating Public Synonyms
      7. Revoking Object Privileges
    5. Roles
      1. Creating Roles
      2. Granting Privileges to Roles
      3. Granting Roles to a User
      4. Checking Roles Granted to a User
      5. Checking System Privileges Granted to a Role
      6. Checking Object Privileges Granted to a Role
      7. Making Use of Privileges Granted to a Role
      8. Enabling and Disabling Roles
      9. Revoking a Role
      10. Revoking Privileges from a Role
      11. Dropping a Role
    6. Auditing
      1. Privileges Required to Perform Auditing
      2. Auditing Examples
      3. Audit Trail Views
    7. Summary
  18. Chapter 11: Creating Tables, Sequences, Indexes, and Views
    1. Tables
      1. Creating a Table
      2. Getting Information on Tables
      3. Getting Information on Columns in Tables
      4. Altering a Table
      5. Renaming a Table
      6. Adding a Comment to a Table
      7. Truncating a Table
      8. Dropping a Table
      9. Using the BINARY_FLOAT and BINARY_DOUBLE Types
      10. Using DEFAULT ON NULL Columns
      11. Using Visible and Invisible Columns in a Table
    2. Sequences
      1. Creating a Sequence
      2. Retrieving Information on Sequences
      3. Using a Sequence
      4. Populating a Primary Key Using a Sequence
      5. Specifying a Default Column Value Using a Sequence
      6. Using Identity Columns
      7. Modifying a Sequence
      8. Dropping a Sequence
    3. Indexes
      1. Creating a B-tree Index
      2. Creating a Function-Based Index
      3. Retrieving Information on Indexes
      4. Retrieving Information on the Indexes on a Column
      5. Modifying an Index
      6. Dropping an Index
      7. Creating a Bitmap Index
    4. Views
      1. Creating and Using a View
      2. Modifying a View
      3. Dropping a View
      4. Using Visible and Invisible Columns in a View
    5. Flashback Data Archives
    6. Summary
  19. Chapter 12: Introducing PL/SQL Programming
    1. Block Structure
    2. Variables and Types
    3. Conditional Logic
    4. Loops
      1. Simple Loops
      2. WHILE Loops
      3. FOR Loops
    5. Cursors
      1. Step 1: Declare the Variables to Store the Column Values
      2. Step 2: Declare the Cursor
      3. Step 3: Open the Cursor
      4. Step 4: Fetch the Rows from the Cursor
      5. Step 5: Close the Cursor
      6. Complete Example: product_cursor.sql
      7. Cursors and FOR Loops
      8. OPEN-FOR Statement
      9. Unconstrained Cursors
    6. Exceptions
      1. ZERO_DIVIDE Exception
      2. DUP_VAL_ON_INDEX Exception
      3. INVALID_NUMBER Exception
      4. OTHERS Exception
    7. Procedures
      1. Creating a Procedure
      2. Calling a Procedure
      3. Getting Information on Procedures
      4. Dropping a Procedure
      5. Viewing Errors in a Procedure
    8. Functions
      1. Creating a Function
      2. Calling a Function
      3. Getting Information on Functions
      4. Dropping a Function
    9. Packages
      1. Creating a Package Specification
      2. Creating a Package Body
      3. Calling Functions and Procedures in a Package
      4. Getting Information on Functions and Procedures in a Package
      5. Dropping a Package
    10. Triggers
      1. When a Trigger Fires
      2. Setting up the Example Trigger
      3. Creating a Trigger
      4. Firing a Trigger
      5. Getting Information on Triggers
      6. Disabling and Enabling a Trigger
      7. Dropping a Trigger
    11. Additional PL/SQL Features
      1. SIMPLE_INTEGER Type
      2. Sequences in PL/SQL
      3. PL/SQL Native Machine Code Generation
      4. WITH Clause
    12. Summary
  20. Chapter 13: Database Objects
    1. Introducing Objects
    2. Running the Script to Create the Object Schema
    3. Creating Object Types
    4. Using DESCRIBE to Get Information on Object Types
    5. Using Object Types in Database Tables
      1. Column Objects
      2. Object Tables
      3. Object Identifiers and Object References
      4. Comparing Object Values
    6. Using Objects in PL/SQL
      1. The get_products() Function
      2. The display_product() Procedure
      3. The insert_product() Procedure
      4. The update_product_price() Procedure
      5. The get_product() Function
      6. The update_product() Procedure
      7. The get_product_ref() Function
      8. The delete_product() Procedure
      9. The product_lifecycle() Procedure
      10. The product_lifecycle2() Procedure
    7. Type Inheritance
      1. Running the Script to Create the Second Object Schema
      2. Inheriting Attributes
    8. Using a Subtype Object in Place of a Supertype Object
      1. SQL Examples
      2. PL/SQL Examples
      3. NOT SUBSTITUTABLE Objects
    9. Other Useful Object Functions
      1. IS OF()
      2. TREAT()
      3. SYS_TYPEID()
    10. NOT INSTANTIABLE Object Types
    11. User-Defined Constructors
    12. Overriding Methods
    13. Generalized Invocation
      1. Running the Script to Create the Third Object Schema
      2. Inheriting Attributes
    14. Summary
  21. Chapter 14: Collections
    1. Introducing Collections
    2. Running the Script to Create the Collection Schema
    3. Creating Collection Types
      1. Creating a Varray Type
      2. Creating a Nested Table Type
    4. Using a Collection Type to Define a Column in a Table
      1. Using a Varray Type to Define a Column in a Table
      2. Using a Nested Table Type to Define a Column in a Table
    5. Getting Information on Collections
      1. Getting Information on a Varray
      2. Getting Information on a Nested Table
    6. Populating a Collection with Elements
      1. Populating a Varray with Elements
      2. Populating a Nested Table with Elements
    7. Retrieving Elements from Collections
      1. Retrieving Elements from a Varray
      2. Retrieving Elements from a Nested Table
    8. Using TABLE() to Treat a Collection as a Series of Rows
      1. Using TABLE() with a Varray
      2. Using TABLE() with a Nested Table
    9. Modifying Elements of Collections
      1. Modifying Elements of a Varray
      2. Modifying Elements of a Nested Table
    10. Using a Map Method to Compare the Contents of Nested Tables
    11. Using CAST() to Convert Collections from One Type to Another
      1. Using CAST() to Convert a Varray to a Nested Table
      2. Using CAST() to Convert a Nested Table to a Varray
    12. Using Collections in PL/SQL
      1. Manipulating a Varray
      2. Manipulating a Nested Table
      3. Using PL/SQL Collection Methods
    13. Creating and Using Multilevel Collections
      1. Running the Script to Create the Second Collection Schema
      2. Using Multilevel Collections
    14. Oracle Database 10g Enhancements to Collections
      1. Running the Script to Create the Third Collection Schema
      2. Creating Associative Arrays
      3. Changing the Size of an Element Type
      4. Increasing the Number of Elements in a Varray
      5. Using Varrays in Temporary Tables
      6. Using a Different Tablespace for a Nested Table’s Storage Table
      7. ANSI Support for Nested Tables
    15. Summary
  22. Chapter 15: Large Objects
    1. Introducing Large Objects (LOBs)
    2. The Example Files
    3. Large Object Types
    4. Creating Tables Containing Large Objects
    5. Using Large Objects in SQL
      1. Using CLOBs and BLOBs
      2. Using BFILEs
    6. Using Large Objects in PL/SQL
      1. APPEND()
      2. CLOSE()
      3. COMPARE()
      4. COPY()
      5. CREATETEMPORARY()
      6. ERASE()
      7. FILECLOSE()
      8. FILECLOSEALL()
      9. FILEEXISTS()
      10. FILEGETNAME()
      11. FILEISOPEN()
      12. FILEOPEN()
      13. FREETEMPORARY()
      14. GETCHUNKSIZE()
      15. GETLENGTH()
      16. GET_STORAGE_LIMIT()
      17. INSTR()
      18. ISOPEN()
      19. ISTEMPORARY()
      20. LOADFROMFILE()
      21. LOADBLOBFROMFILE()
      22. LOADCLOBFROMFILE()
      23. OPEN()
      24. READ()
      25. SUBSTR()
      26. TRIM()
      27. WRITE()
      28. WRITEAPPEND()
      29. Example PL/SQL Procedures
    7. LONG and LONG RAW Types
      1. The Example Tables
      2. Adding Data to LONG and LONG RAW Columns
      3. Converting LONG and LONG RAW Columns to LOBs
    8. Oracle Database 10g Enhancements to Large Objects
      1. Implicit Conversion Between CLOB and NCLOB Objects
      2. Use of the :new Attribute When Using LOBs in a Trigger
    9. Oracle Database 11g Enhancements to Large Objects
      1. Encrypting LOB Data
      2. Compressing LOB Data
      3. Removing Duplicate LOB Data
    10. Oracle Database 12c Enhancement to Large Objects
    11. Summary
  23. Chapter 16: SQL Tuning
    1. Introducing SQL Tuning
    2. Use a WHERE Clause to Filter Rows
    3. Use Table Joins Rather than Multiple Queries
    4. Use Fully Qualified Column References When Performing Joins
    5. Use CASE Expressions Rather than Multiple Queries
    6. Add Indexes to Tables
      1. When to Create a B-Tree Index
      2. When to Create a Bitmap Index
    7. Use WHERE Rather than HAVING
    8. Use UNION ALL Rather than UNION
    9. Use EXISTS Rather than IN
    10. Use EXISTS Rather than DISTINCT
    11. Use GROUPING SETS Rather than CUBE
    12. Use Bind Variables
      1. Non-Identical SQL Statements
      2. Identical SQL Statements That Use Bind Variables
      3. Listing and Printing Bind Variables
      4. Using a Bind Variable to Store a Value Returned by a PL/SQL Function
      5. Using a Bind Variable to Store Rows from a REFCURSOR
    13. Comparing the Cost of Performing Queries
      1. Examining Execution Plans
      2. Comparing Execution Plans
    14. Passing Hints to the Optimizer
    15. Additional Tuning Tools
      1. Oracle Enterprise Manager
      2. Automatic Database Diagnostic Monitor
      3. SQL Tuning Advisor
      4. SQL Access Advisor
      5. SQL Performance Analyzer
      6. Database Replay
      7. Real-Time SQL Monitoring
      8. SQL Plan Management
    16. Summary
  24. Chapter 17: XML and the Oracle Database
    1. Introducing XML
    2. Generating XML from Relational Data
      1. XMLELEMENT()
      2. XMLATTRIBUTES()
      3. XMLFOREST()
      4. XMLAGG()
      5. XMLCOLATTVAL()
      6. XMLCONCAT()
      7. XMLPARSE()
      8. XMLPI()
      9. XMLCOMMENT()
      10. XMLSEQUENCE()
      11. XMLSERIALIZE()
      12. A PL/SQL Example That Writes XML Data to a File
      13. XMLQUERY()
    3. Saving XML in the Database
      1. The Example XML File
      2. Creating the Example XML Schema
      3. Retrieving Information from the Example XML Schema
      4. Updating Information in the Example XML Schema
    4. Summary
  25. Appendix: Oracle Data Types
    1. Oracle SQL Types
    2. Oracle PL/SQL Types
  26. Index

Product information

  • Title: Oracle Database 12c SQL
  • Author(s): Jason Price
  • Release date: June 2013
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071799362