Practical Guide to using SQL in Oracle

Practical Guide to using SQL in Oracle

by Earp
Released October 2010
Publisher(s): Jones & Bartlett Learning
ISBN: 9781449613037

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


Structured Query Language has become the standard for generating, manipulating, and retrieving database information. The dramatic increase in the popularity of relational databases, coupled with Oracle’s having the largest market share, has created a demand for programmers who can write SQL code correctly and efficiently.

This book provides a systematic approach to learning SQL in Oracle. Each chapter is written in a step-by-step manner and includes examples that can be run using Oracle. Using the sample tables and data provided, readers will be able to perform the examples to gain hands-on experience with Oracle programming.

Gain an understanding of basic SQL principles.
Learn to generate, store, and edit SQL queries in Oracle.
Develop joins, subqueries, and correlated subqueries.
Work with XML and Oracle databases.
Test your SQL knowledge with the exercises at the end of each chapter!

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents
  6. Preface
  7. Acknowledgments
  8. Prologue - The Software Engineering Process and Relational Databases
    1. What is a Database?
    2. Database Models
      1. The Hierarchical Model
        1. Other Cardinalities
      2. The Network Model
      3. Contemporary Databases: The Relational Model
    3. The First, Second, and Third Normal Forms
      1. The First Normal Form
        1. Non-1NF to First Normal Form (1NF)
      2. The Second Normal Form
        1. Non-2NF to 2NF
      3. The Third Normal Form
        1. Non-3NF to 3NF
    4. What is the Software Engineering Process?
  9. Chapter 1 - Getting Started with Oracle
    1. Getting Started with Oracle in UNIX
      1. Signing on to Oracle in UNIX
    2. Setting your System Parameters
      1. Setting the PAUSE Parameter
      2. Setting the Prompt Parameter
      3. Showing Timing Statistics
      4. Viewing a List of System Parameters
      5. Oracle's Help Command in UNIX
    3. Using Oracle Commands
      1. Understanding SQL and its Sublanguage
    4. Using Select Statement Syntax
      1. Re-executing a Command
    5. Accessing Tables
    6. Adding Comments to Statements
      1. A few More Examples and Further Comments about Case
    7. Editing SQL Statements
      1. Option 1: Editing SQL Statements Using an Editor
        1. Defining an Editor
        2. Editing the Buffer
        3. Saving the Buffer
        4. Using Get
        5. Using a Script File to Save your Query
      2. Option 2: Editing SQL Statements or Queries Using SQLPLUS
        1. Using the Change Command
        2. Using the List Command
        3. Using the Append Command
        4. Using the Input Command
        5. Using Input to Insert a Line
        6. Using the Delete Command
    8. Displaying the Student-Course Database
      1. Displaying the Course Table (the Course Relation)
    9. Creating a Synonym for the Course Table
      1. Deleting a Synonym
    10. Introducing the Oracle Data Dictionary
      1. Using DESC
    11. Using a Convention for Writing SQL Statements
    12. Printing Query Results and Using Host
    13. Signing off from Oracle
    14. Exercises for Chapter
  10. Chapter 2 - More “Beginning” SQL Commands
    1. An Extended Select Statement
      1. Selecting Attributes (Columns)
      2. Using Order by
      3. Selecting Rows
      4. Using and
      5. Using Or
      6. Using Between
    2. A Simple Create Table Command
    3. Inserting Values into an Existing Table
      1. Insert Into..Values
      2. Insert Into..Select
    4. The Update Command
    5. The Delete Command
    6. Deleting a Table
    7. Rollback, Commit, and Savepoint
    8. The Alter Table Command
    9. Data Types
      1. Common Number Data Types
      2. Char Data Type
      3. Varchar2 Data Type
      4. Nchar and Nvarchar2 Data Types
      5. Long, Raw, Long Raw, and Boolean Data Types
      6. Large Object (LOB) Data Types
      7. Abstract Data Types
      8. The XML Data Type
      9. The Date Data Type and Type Conversion Functions
        1. Entering Four-Digit Years
    10. Exercises for Chapter
  11. Chapter 3 - Joins
    1. The Cartesian Product
    2. The Join
      1. Join Using Ansi Join Syntax
      2. Theta Joins
    3. Qualifiers
    4. Table Aliases and an Introduction to Multi-Table Joins
    5. More on Comments
    6. More on Multiple Table Joins and Join Conditions
    7. Column Aliases
    8. Scripting
    9. Count and Rownum
      1. Using Count
      2. Using Rownum
    10. Outer Joins
      1. Left Outer Join
      2. Right Outer Join
      3. Handling Full Outer Joins
      4. Outer Join with an and Condition
      5. Chaining Outer Joins
    11. Self Joins
      1. Self Join and Outer Join
    12. Exercises for Chapter
  12. Chapter 4 - Functions
    1. The Count Function
      1. Using Select and Count with Distinct
    2. More Basic Functions
    3. Aggregate Functions
    4. Row-Level Functions
      1. The Nvl Function
      2. String Functions
        1. The Substr And Instr Functions
        2. The Rpad and Lpad Functions
        3. The Ltrim and Rtrim Functions
        4. The Length Function
        5. Matching Substrings Using Like
        6. Like as an Existence Match
        7. Like with a Positioned Match and a Wildcard
        8. The Upper and Lower Functions
    5. The Data Dictionary Revisited
    6. Exercises for Chapter
  13. Chapter 5 - Query Development, Privileges, and Derived Structures
    1. Query Development
      1. Using Save and Edit
      2. Deleting a Query
    2. Parentheses in SQL Expressions
    3. Derived Structures
      1. Views
        1. Using the Create or Replace View Statements
        2. Adding Order by to Create or Replace View Statements
        3. Developing a Query Using Views
        4. Creating Special View Column Names
    4. Granting and Revoking Privileges on Tables and Views
    5. Query Development and Derived Structures
      1. Step 1: Developing a Query Step-by-Step
      2. Step 2: Using a Derived Structure
        1. Option 1: Make your Query a View
        2. Option 2: Create a Temporary Table
        3. Option 3: Use an Inline View
        4. Option 4: Use a Snapshot
    6. Exercises for Chapter
  14. Chapter 6 - Set Operations
    1. UNION Operations
    2. The in and Not..In Predicates
      1. Using in
      2. Using Not..in
    3. The Difference Operation
    4. Exercises for Chapter
  15. Chapter 7 - Subqueries Versus Joins
    1. The in Subquery
    2. The Subquery as a Join
    3. When the Join Cannot be Turned into a Subquery
    4. More Examples Involving Joins and in
      1. Example 1
      2. Example 2
      3. Example 3
    5. Subqueries with Operators
    6. Exercises for Chapter
  16. Chapter 8 - Group by and Having
    1. Aggregates/Column Functions
    2. The Group by Clause
      1. Group by and Order by
    3. The Having Clause
      1. Having and where
    4. Group by and Having: Aggregates of Aggregates
    5. Auditing in Subqueries
    6. Nulls Revisited
    7. Exercises for Chapter
  17. Chapter 9 - Correlated Subqueries
    1. Non-Correlated Subqueries
    2. Correlated Subqueries
    3. Existence Queries and Correlation
      1. Exists
      2. From in to Exists
      3. Not Exists
    4. SQL Universal and Existential Qualifiers — the “for all” Query
      1. Example 1
      2. Example 2
      3. Example 3
    5. Exercises for Chapter 9
  18. Chapter 10 - Create Table and Sqlloader
    1. The “Simple” Create Table
    2. The not Null Constraint
    3. Primary Key Constraints
      1. Creating the Primary Key Constraint
        1. At the Column Level
        2. At the Table Level
        3. Using the Alter Table Command
      2. Adding a Concatenated Primary Key
      3. Another Example of Adding a Concatenated Primary Key
    4. The Unique Constraint
    5. The Check Constraint
    6. Referential Integrity
      1. Defining the Referential Integrity Constraint
      2. Adding the Foreign Key After Tables are Created
      3. Using Delete and the Referential Constraint
        1. On Delete Restrict
        2. On Delete Cascade
        3. On Delete Set Null
    7. More on Constraint Names
    8. Sqlloader
      1. Sqlloader Example 1
      2. Another Sqlloader Example
    9. Exercises For Chapter
  19. Chapter 11 - Multiple Commands, Start Files, and Reports in Sqlplus
    1. Creating A File (a Start Table) and Starting it
    2. A Start File (Script) with Editing Features
    3. Using the Decode, Greatest, and Least Functions
      1. Decode
      2. Greatest and Least
    4. Adding Reporting Features to a Start File
    5. A New and Improved Script
    6. Using Start Files with Accept and Prompt
    7. Using Start Files with Positional Input
    8. Exercises for Chapter
  20. Chapter 12 - Beginning PL/SQL: Anonymous Blocks, Procedures, Functions, and Packages
    1. Anonymous Blocks
    2. Elementary Procedures with Sequence Structures
      1. A Simple Example of a Procedure
      2. Reusing a Procedure
      3. Deleting a Procedure
      4. Adding a Parameter List to a Procedure
      5. Performing More than One Action in a Procedure
      6. Procedures with Selection and Iteration Control Structures
        1. Example of a Procedure with Selection
        2. Example of a Procedure with Iteration
    3. Functions
      1. Example of a Function
      2. Deleting a Function
    4. Packages
      1. Creating a Package
      2. Another Approach to Creating this Package
      3. Deleting a Package
    5. Defining a PL/SQL Index by Table
      1. Using a PL/SQL Table
    6. Exercises for Chapter
  21. Chapter 13 - Introduction to Triggers
    1. What is a Trigger?
    2. A Simple Trigger Example
      1. How the Trigger Worked
    3. Row-Level Triggers Versus Statement-Level Triggers
    4. Enabling and Disabling Triggers
      1. Enabling all Triggers for a Table
    5. Deleting Triggers
    6. Values in the Trigger
    7. Using when
      1. Performance Issues Using when
    8. A Trigger where One Table Affects Another Trigger
    9. Mutating Tables
    10. Exercises for Chapter
  22. Chapter 14 - SQL and XML
    1. Overview of XML
    2. Oracle and XML
      1. Xmlforest
      2. Using XMLELEMENT
      3. Using XMLATTRIBUTES
    3. Creating a Table Using the XMLType Data Type
      1. Inserting Values into Tables with an XMLType Data Type
        1. Extracting Information Using XPATH
        2. Using Extractvalue
        3. Using Existsnode
    4. Exercises for Chapter
  23. Appendix A - Some UNIX Commands
    1. Commonly Used UNIX Commands
    2. Summary Table
    3. Other Miscellaneous Commands
    4. Editors
      1. Using vi as your Editor
        1. Other vi Commands
      2. Using joe as your Editor
  24. Appendix B - The Data Dictionary
    1. Beginning to Explore the Data Dictionary
    2. Choosing a View from the Dictionary
      1. Choosing the View you want to See
      2. Describing the View you want to See
      3. Finding the “Right” Columns
      4. Finding out how Many Rows are in the View
      5. Views of Tables
    3. Other Objects — Tablespaces and Constraints
      1. Views of Tablespaces
      2. Views of Constraints
    4. Exercises for Appendix B
  25. Appendix C - The Student Database and Other Tables Used in this Book
    1. The Student-Course Database
    2. Entity Relationship Diagram of the Student-Course Database
    3. Other Tables Used in this Book
  26. Appendix D - Glossary of Terms
  27. Appendix E - Important Commands and Functions
  28. Index

Product information

  • Title: Practical Guide to using SQL in Oracle
  • Author(s): Earp
  • Release date: October 2010
  • Publisher(s): Jones & Bartlett Learning
  • ISBN: 9781449613037