---

Oracle10g SQL for Business or Data Analysts is a hands-on course, which givesyou 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 foranalysis from an Oracle10g database.

Highlights

• 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

Course Outline

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

• 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
• 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
• ORDER by clause
• How NULLS are processed
• Multiple Columns
• ASC versus DESC
• DISTINCT versus UNIQUE

5. Single Row FUNCTIONS

• What are functions?
• Analytic, character, numeric, date, conversion, 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 an 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

Space is limited. Register today to save your space!