Oracle® 10g SQL Programming

This course is an introduction to the SQL database language used with Oracle® 10g databases. After this course the student will be able to use SQL to define, manipulate and control access to their data. The course thoroughly covers SQL syntax and facilities.

The course begins with a general introduction to databases, including the principles of relational databases. The Oracle SQL*Plus query tool is covered and will be used throughout the course. Data retrieval is discussed in depth, covering all of the pertinent features of SQL, including joins. Scalar and aggregate functions are covered. Data Manipulation Language is covered, including inserting, updating and deleting data. Transaction control and locking is discussed. Data Definition and Control statements are covered, including creating, altering and dropping tables, and the use of integrity constraints. Views and indexes are covered. The course concludes with an introduction to the reporting facilities in SQL*Plus.

General familiarity with computers and data processing

On completion of this course, the student should be able to

• Gain an in-depth understanding of SQL concepts, syntax and uses
• Learn the use of Oracle 10g database features and tools
• Use SQL hands-on to access Oracle 10g databases

Application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle10g database.

3 Days

Instructor-led course, with practical computer-based exercises.

• 1. Course Introduction
  • Course Objectives
  • Course Overview
  • Using the Workbook
  • Suggested References

• 2. Relational Database and SQL Overview
  • Review of Relational Database Terminology
  • Introduction to SQL
  • Logical and Physical Storage Structures
  • Oracle Versioning and History
  • Connecting to a SQL Database
  • Oracle10g Datatypes

• 3. Using Oracle SQL*Plus
  • SQL*Plus
  • The SQL Buffer
  • Buffer Manipulation Commands
  • Tailoring Your SQL*Plus Environment
  • Interacting with the Host Environment
  • Viewing Table Characteristics
  • Running SQL*Plus Scripts
  • SQL*Plus Substitution Variables
  • Interactive SQL*Plus Scripts

• 4. SQL Queries: The SELECT Statement
  • The SELECT Statement
  • Choosing Rows with the WHERE Clause
  • NULL Values
  • Compound Expressions
  • The CASE...WHEN Statement
  • IN and BETWEEN
  • The LIKE Operator
  • Creating Some Order
  • How a Query is Processed

• 5. Scalar Functions
  • SQL Functions
  • String Functions
  • Numeric Functions
  • Date Functions
  • Conversion Functions

• 6. Aggregate Functions and Advanced Techniques
  • Correlated Subqueries
  • The EXISTS Operator
  • The Aggregate Functions
  • Rollup and Cube
  • Grouping Rows
  • Combining SELECT Statements
  • How a Query is Processed

• 7. SQL Queries Joins
  • Selecting from Multiple Tables
  • Joining Tables
  • Self Joins
  • Outer Joins
  • How a Query is Processed

• 8. Data Manipulation and Transactions
  • The INSERT Statement
  • The UPDATE Statement
  • The DELETE Statement
  • Transaction Management
  • Concurrency
  • Explicit Locking
  • Data Inconsistencies
  • Loading Tables From External Sources

• 9. Data Definition and Control Statements
  • Standard Datatypes
  • Defining Tables
  • DEFAULT and NOT NULL
  • Constraints
  • Column Constraints
  • Foreign Keys
  • Modifying Table Definitions
  • Deleting a Table Definition
  • Controlling Access to Your Tables

• 10. Indexes
  • B*Tree Indexes
  • Reverse Key and Unique Indexes
  • Bitmap Indexes
  • Function-Based Indexes
  • Index-Organized Tables (IOTs)
  • Managing Indexes

• 11. Other Database Objects
  • Views
  • Creating and Using Views
  • Sequences
  • Synonyms
  • Global Temporary Tables

• 12. Formatting Reports with SQL*Plus
  • Page Formatting
  • Computations
  • SQL*Plus Options for Formatting
  • Saving the Output

• Appendix A. The Data Dictionary
  • Introducing the Data Dictionary
  • DBA, ALL, and USER Data Dictionary Views
  • Some Useful Data Dictionary Views

Course exercises require Oracle 10g running on a Microsoft Windows, Linux, or multi-user UNIX system. See the appropriate course Setup Guide for details.

A good minimal hardware profile for this course would have a Pentium 500-MHz or equivalent CPU, 512 MB of RAM, and at least 4 GB of free disk space for the Oracle installation.