Course Description:

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 cover how to format reports, and export data to ODBC files. You will work with Oracle's Structured Query Language (SQL), SQL*Plus.

You can learn to…

Who Needs to Attend:

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. I.e. Application designers, developers, database administrators, and End Users

Prerequisites:

An understanding of relational database design concepts

Course Content:

Database Management System Overview

• What is a Database?

• Types of DBMSs

Introduction to Entity Relationship Modeling

• What is a Data Model?

• Why Use an Entity Relationship Model?

Fundamentals of Relational Database Management Systems

• What is an RDBMS?

• Physical Data Storage

• Relationships between Tables

• Domains - Data Integrity

SQL Basics and SQL*Plus

• Understanding the Difference Between SQL, SQL*Plus and PL/SQL

• Invoking and Leaving SQL*Plus

• Entering and Executing Commands

• The SELECT Command & Mandatory Clauses

Using iSQLPlus

• Understanding the iSQL*Plus Environment and Commands

The WHERE and ORDER BY Clause

• WHERE, ORDER BY Clause

• Using the Data Dictionary

Single Row Functions

• Functions, Date and Numeric Format Models/Data Conversion

• Control Statements

Joining Data from Multiple Tables in Queries

• The JOIN Condition / The Cartesian Product

• Outer JOINs

Group Functions and the GROUP BY Clause

• Group Functions

• Distinct Operator in Group Functions

Using Subqueries and CASE Clauses

• Single-Row, Multi-Row, Multi-Column Subqueries

• Subqueries in a WHERE Clause, the FROM Clause Correlated Subqueries, CASE Clause

Analytic Functions (Optional)

• Single Row and Aggregating Analytic Functions

SQL*Plus Reporting

• SQL*Plus Report Writing Commands

• COLUMN Command / Titles, System Variables

• Master-Detail Report with TITLE and COLUMN Commands

• Control Breaks, Computing Aggregate Amounts

Data Manipulation Language (DML) Commands

• INSERT, UPDATE, DELETE, MERGE Command

• Use of Subqueries

• Transaction Control / Read Consistency / Locking

Getting Data In And Out Of Oracle

• SQL*Loader Basics

• Importing Into Excel

• Direct Database Queries Using ODBC

Course Labs:

Lab 1: RDBMS Principles

Lab 2: SQL*Plus Basics

Lab 3: Using iSQL*Plus

Lab 4: Insert, Update and Delete Data

Lab 5: SQL Queries

Lab 6: Filtering And Sorting Data

Lab 7: Single Row Functions

Lab 8: Joining Multiple Tables

Lab 9: ANSI SQL99 Joins

Lab 10:Group Clauses and Functions

Lab 11: Subqueries

Lab 12: Analyzing Data (Optional)

Lab 13: SQL*Plus Reporting