Oracle9i 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 Oracle9i 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

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

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