Oracle 10g SQL for Business and Data Analysts 培訓
Oracle 10g SQL for Business and Data Analysts 培訓
培訓大綱：
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
?
?
?

?

?

?
?

?

?