Develop the skills to effectively interact with an Oracle11g Database.

Course Description

Oracle11g SQL for Business or Data Analysts is a hands-on course, which gives you a basic knowledge of how to extract data from Oracle using SQL, SQL*Plus, and PL/SQL. This training covers topics that are necessary to query data for analysis from an Oracle11g database.

You can learn to ...

• Get data out of Oracle and into:

• Field delimited files

• CSV Files

• ODBC connections like: Excel, Access, Crystal Reports, and SAS

• Understand Structured Query Language (SQL) basics:

• WHERE Clause

• Data Types, Functions, Data Manipulation

• Ordering & Grouping, Indexing, Joining Tables

• Transactions (Commit & Rollback)

• Subqueries

• Format reports using SQL*Plus commands

• Extract and organize information from the database

• Insert, update & delete information in database tables

• Create and drop tables, views, synonyms and indexes

• Load tables using SQL*Loader

 Who Needs to Attend:

Anyone who need to extract data out of Oracle (e.g. Analysts, End Users, Executives, Programmers, etc.)

Prerequisites:

A general understanding of relational database design concepts.

Course Content:

1.        Intro to Structure Query Language (SQL) for Analyst

SQL the language

Data Manipulation Language (DML)

Data Definition Language (DDL)

SQL*Plus the Environment

Scripting using Oracle’s Procedural Language option (PL/SQL)

Overview of complete SQL SELECT syntax

2.        Intro to Oracle’s RDBMS objects

Objects, Tables, Columns, Pseudo columns, Rows

Data domains, Duplicate values, Null values

Indexes, Views, Sequences, Synonyms

Declarative Constraints

Primary, Foreign, Unique keys

Check, Not Null, Default

3.      Using SQL in different scenarios

a.        SQL using SQL*Plus

Starting SQL*Plus

Entering and executing SQL commands

The SQL command buffer within SQL*Plus

Editors, Editing commands in the buffer

SQL*Plus commands

Set, Show, Column, Others

Using SQL*Plus Worksheet

b.       SQL using iSQLPlus (SQL*Plus via web browsers)

Starting iSQLPlus

Entering and executing SQL commands

4.        More SQL SELECT

Arithmetic calculations

Character, Comparison, and Logical operators

WHERE clause, Pattern matching, Using SQL*Plus substitution variables

&, &&, &1

ORDER BY clause

How NULLS are processed

Multiple Columns

ASC vs. DESC

DISTINCT vs. UNIQUE

5.        Single Row FUNCTIONS

What are Functions?

1. Analytic

2. Character

3. Numeric

4. Date

5. Conversion

6. Miscellaneous

6.        GROUP BY Clause & Group FUNCTIONS

What are GROUP Functions?

Aggregate (Group)

Analytic functions

COUNT(*) vs. COUNT(column_name)

SUM(), AVG(), MIN(), MAX()

VARIANCE(), STDDEV()

GROUP BY and HAVING clause

ROLLUP(), CUBE(), GROUPING()

7.        Advanced topics:

Joining Tables

Subqueries

Correlated Subqueries

IF EXIST

8.        SQL*Plus Reporting

Building a SQL script

COLUMN command

Titles, Control Breaks, Sorting

TTILE vs. REPHEADER

BTITLE vs. REPFOOTER

BREAK command

COMPUTE command (Aggregates)

Using variables in SQL*Plus

     SQL*Plus substitution variables

&, && and &1

ACCEPT and PROMPT commands

DEFINE and BIND variables

9.        Getting data into Oracle

SQL*Loader

Field delimited files

CSV files

10.    Getting data out of Oracle

Field delimited files

CSV Files

ODBC connections like:

Excel, Access, Crystal Reports, and SAS

11.     Intro to PL/SQL scripting   

    What is this thing called PL/SQL

Features and Benefits

Where do you fit?

12.     PL/SQL Basics

An Anonymous block structure

Lexical Units

Variable Declarations, Expressions and operators

Branching

IF and CASE statements

Looping:  Simple and WHILE

FOR loops

13.     Working with database data

SELECT statement (single row)

Other DML

14.     Intro to CURSORs

Implicit and Declared CURSOR

SELECT statement (multiple rows)

Using a LOOP to FETCH a CURSOR

15.     Exception Handling

What are EXCEPTIONS

Oracle Pre-defined, User defined, and Other Oracle exceptions

RAISE_APPLICATION_ERROR function

Exception Propagation

Preventing un-handled exceptions

16.     Writing Scripts

How to automate routine re-occurring tasks

Course Labs

1.        SQL*Plus Basics

2.        Using iSQL*Plus

3.        Insert, Update and Delete Data

4.        SQL Queries

5.        Filtering And Sorting Data

6.        Single Row Functions

7.        Joining Multiple Tables

8.        ANSI SQL99 Joins

9.        Group Clauses and Functions

10.     Subqueries

11.     Analyzing Data

12.     SQL*Plus Reporting

13.     Using PL/SQL to create an Anonymous Block

14.     PL/SQL Basics

15.     Selecting and Updating Database Data

16.     Using Explicit Cursors

17.     Handling Exceptions

18.     Creating a Procedure