Course Description

In this course, you will receive an introduction to Oracle’s relational database, and a hands-on introduction in how to add, update, extract, and organize information from the database. You will work with Oracle’s Structured Query Language (SQL), SQL*Plus, and SQL Developer.

Audience Profile

Anyone who needs to extract and organize information from an Oracle database, including formatting reports, adding, updating, and deleting data, and exporting data to ODBC files, including:

• Application designers
• Developers
• Database administrators
• End users

Prerequisites

An understanding of relational database design concepts

Learning Objectives

• Format reports using SQL*Plus commands.
• Extract and organize information from the database.
• Insert, update, and delete information in database tables.
• Understand Structured Query Language basics

Course Outline

Module 1: Introduction to the Oracle Database
Lessons
• Why Use an Entity Relationship Model?
• Physical Data Storage
• Relationships between Tables
• Domains and Data Integrity Constraints
• Data Model Review
Lab: RDBMS Principles

Module 2: SQL Basics and SQL*Plus
Lessons
• Understanding the Difference Between SQL, SQL*Plus and PL/SQL
• Invoking and Leaving SQL*Plus
• Entering and Executing Commands
• The SQL Buffer, SQL*Plus Default Output
• LOGIN.SQL / GLOGIN.SQL
• The SELECT Command & Mandatory Clauses
Lab: SQL*Plus Basics

Module 3: Using SQL Developer
Lessons
• Understanding SQL Developer
• SQL Developer Setup
• Advanced Functionality
Lab: SQL Developer

Module 4: The WHERE and ORDER BY Clause
Lessons
• WHERE, ORDER BY Clause
• Using the Data Dictionary
• Hierarchical Queries
• Pattern Matching
Lab: Insert, Update and Delete Data
Lab: SQL Queries

Module 5: Single Row Functions
Lessons
• Arithmetic Operators
• Operator Precedence
• Single Row vs. Group Functions
• Date and Numeric Format Models
• Data Conversion / Date Manipulation / Time Zones
• Control Statements
• IF THEN ELSE Logic
• Regular Expressions
Lab: Number Functions
Lab: Character Functions
Lab: Date Functions
Lab: Misc. Functions

Module 6: Joining Data from Multiple Tables in Queries
Lessons
• ANSI SQL 92 and SQL99 Joins
• The JOIN Condition / The Cartesian Product
• Outer JOINs
Lab: Joining Multiple Tables
Lab: ANSI SQL99 Joins

Module 7: Group Functions and the GROUP BY Clause
Lessons
• Group Functions
• Distinct Operator in Group Functions
• GROUP BY, WITH Clause, ROLLUP, CUBE
• GROUPING SETS
Lab: Group Clauses and Functions

Module 8: Using Subqueries
Lessons
• Single-Row, Multi-Row, Multi-Column Subqueries
• Subqueries in a WHERE / FROM / HAVING Clauses
• Subqueries In the SELECT Clause – Scalar Subqueries
• Correlated Subqueries / Hierarchical Queries
• EXISTS / NOT EXISTS / WITH / Recursive WITH
Lab: Subqueries

Module 9: Data Manipulation Language (DML) Commands
Lessons
• INSERT, UPDATE, DELETE Command

Module 10: Getting Data In and Out of Oracle
Lessons
• SQL*Loader Basics
• Importing Into Excel
• Direct Database Queries Using ODBC
Lab: SQL Loader