Oracle10g 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 Oracle10g database.

What You'll Learn

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

Follow-On Courses

There are no follow-ons for this course.

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

Labs

Lab 1: SQL*Plus Basics

Lab 2: Using iSQL*Plus

Lab 3: Insert, Update and Delete Data

Lab 4: SQL Queries

Lab 5: Filtering and Sorting Data

Lab 6: Single Row Functions

Lab 7: Joining Multiple Tables

Lab 8: ANSI SQL99 Joins

Lab 9: Group Clauses and Functions

Lab 10: Subqueries

Lab 11: Analyzing Data

Lab 12: SQL*Plus Reporting

Lab 13: Using PL/SQL to create an Anonymous Block

Lab 14: PL/SQL Basics

Lab 15: Selecting and Updating Database Data

Lab 16: Using Explicit Cursors

Lab 17: Handling Exceptions

Lab 18: Creating a Procedure