Oracle 9i SQL for Users

Objective:                    

Synopsis

This course gives a basic introduction to Oracle's version of SQL, introducing the concepts behind the SQL language and the basics of SQL*Plus.

Designed to introduce concepts and functionality in an accessible and friendly format, the course has been prepared to assist end-users understand how to retrieve and maintain information within a controlled Oracle9i database environment.

Course outline:

Oracle Overview

• The Elements of the Oracle System       

- Relational Concepts

SQL*Plus and SQL

• Logging on to SQL*Plus and Leaving SQL*Plus
• Choosing and Describing Tables    

- Elements of the SQL Query

• The SQL Buffer and Query Layout  

- Editing SQL Statements

• The System Dummy Table            

- Selecting Columns

• Duplicate Information (DISTINCT)   

- Sorting Information

SQL Functions ..  The Concatenation Operator

• Elements of the SQL Query: Arithmetic 

- Column Aliases

• String Functions                         

- Arithmetic Functions

• Date Functions                           

- Mixed Functions

Advanced SQL Functions

• Nesting Different Functions

- SQL Bar Charts

• Decode Crosstab              

- Decode with ">", "<" & "="

• Select with Minus Union and Intersect
• Connect By Prior

Filtering Data Using Where

• Where Operators                    

- Where with Keywords

• Where and Logical Operators   

- Where and Soundex

Retrieving Data from Multiple Tables

• Joining Tables (Equi-Joins)          

- Aliases for Table Names

• Thinking Behind Creating Joins     

- Joining Tables (Non-Equi-Joins)

• Joining Tables (Outer Joins)                   

- Joining Tables (Inner Joins)

Group By and Group By Functions

• Group Function Examples             

- Group Function with Having

Sub-Queries - Basic Subqueries

• Multiple Column Subqueries                   

- Subqueries with Having

• Correlated Subqueries                 

- Subquery Rules and Suggestions

Data Definition Language (DDL)

• Create, Drop Alter Keywords         - Tables
• Column                  

- Views        

- Sequences

Data Manipulation Language (DML)       

- nsert          

- Update 

- Delete

SQL*Plus Reporting

• Introducing SQL*Plus for Reporting
• Using SQL*Plus