174.  Introduction to Oracle10g SQL Programming

Rev. 4.3.2

 

In this class, students will learn how to create, retrieve, and manipulate objects in Oracle10g Structured Query Language (SQL). Students will also be introduced to Oracle10g database features and tools

 

LEARNING OBJECTIVES

 

·         Gain an 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

 

Course Duration: 2 days

 

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

 

Prerequisite: General familiarity with computers and data processing

 

1.      Course Introduction

Course Objectives

Course Overview

Using the Workbook

Suggested References

 

2.      Relational Database and SQL Overview

Review of Relational Database Terminology

Relational Database Management Systems

Introduction to SQL

Oracle Versioning and History

Logical and Physical Storage Structures

Connecting to a SQL Database

Datatypes

Sample Database

 

3.      Using Oracle SQL*Plus

SQL*Plus

The SQL Buffer

Buffer Manipulation Commands

Running SQL*Plus Scripts

Tailoring Your SQL*Plus Environment

Viewing Table Characteristics

SQL*Plus Substitution Variables

Interactive SQL*Plus Scripts

Using iSQL*Plus

 

4.      SQL Queries: The SELECT Statement

The SELECT Statement

The CASE...WHEN Statement

Choosing Rows with the WHERE Clause

NULL Values

Compound Expressions

IN and BETWEEN

The LIKE Operator

Creating Some Order

 

5.      Scalar Functions

SQL Functions

Using SQL Functions

String Functions

Numeric Functions

Date Functions

Conversion Functions

Date Formats

Oracle Pseudocolumns

 

6.      SQL Queries – Joins

Selecting from Multiple Tables

Joining Tables

Self Joins

Outer Joins

Types of Outer Joins

 

7.      Aggregate Functions and Advanced Techniques

Subqueries

Correlated Subqueries

The EXISTS Operator

The Aggregate Functions

Grouping Rows

Combining SELECT Statements

 

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

Constraints

Inline Constraints

Modifying Table Definitions

Deleting a Table Definition

Controlling Access to Your Tables

 

10. Other Database Objects

Views

Creating Views

Updateable Views

Sequences

Synonyms

 

Appendix A. The Data Dictionary

 

Introducing the Data Dictionary

DBA, ALL, and USER Data Dictionary Views

Some Useful Data Dictionary Queries

 

System Requirements

 

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,.