Oracle 9I Advanced SQL Course

Essential advanced features within the Oracle SQL language are considered within this course. Building indexes, specifying declarative constraints, managing security privileges for tables and using SQL to satisfy some of the most complex query requests are all among the topics considered within this course. This course largely focuses on SQL features that are unique to the Oracle database and as such is applicable even for SQL professionals experienced in other databases.

This course is designed for All Oracle professionals including developers, database administrators, designers and analysts.

Upon completion of this course, the participant will be able to:
> Complete a schema definition with indexes, synonyms, sequences and tablespace placement
> Protect data integrity using declarative constraints
> Protect table access using database object security
> Complete complex SQL requests using ROLLUP, CUBE and hierarchy pro

Defining Database Objects
Creating An Application Schema
Truncate
Difference From Delete  And Drop Table 
Create Index / Drop Index
User_Ind_Columns 
Alter View 
User_Updatable_Columns
Defining Sequences 
Create Sequen
Nextval, Currval
Alter, Drop Sequence 
Create Synonym / Drop Synonym

Database Security 
User Schema Pass_A
About Object Security 
Grant, Revoke
SQL Functions (Character) 
Character Functions
Upper(), Lower(), Initcap() 
Chr(), Ascii()  
Rpad(), Rtrim()  
Decode(), Substr()  
Instr(), Translate() 
Replace()  
Greatest(), Least() 
Soundex()  

SQL Functions (Non-Character) 
Greatest(), Abs()  
Round(), Trunc(), Sign()  
To_Char()  
Format Elements: Yyyy, Ddth, Fm, Rr
To_Date() Exam
Months_Between() 
Add_Months(), Last_Day() 
Last_Day(), Next_Day()
Trunc(), Round() Dates 
Null Value Functi_

Advanced SQL Language Constructs 
Case Expression
Coalesce() Func_Sce

Protecting Tables With Declarative Constraints 
Not Null Constraint 
Check Constraint
Unique Constraint
Primary Key Constraint
References Constraint 
On Delete Cascade 
On Delete Set Null 

Maintaining Constraint Definitions
Drop Table  Cascade
Enabling & Disabling Constraints
Validate New Transactions Only
Deferred Enforc_D
Set Constraints  Deferred
Managing Constraint Exceptions
Constraints
Data Dictionary Storage 
User_Constraints 
Constraint_Type Code Explanation 

Enhancing Groups With Rollup & Cube 
Using Rollup 
Grouping()
Cube

Advanced SQL*Plus Reports 
Break, Compute 
Column, Ttitle / Btitle

Building SQL*Plus Scripts
XSQL File  
Define / Und
Accept 
Start Or @
Prompt, Pause

Processing Hierarchies
:N Hierarchy (Supervision) 
M:N Hierarchy (Bill-Of-Materials)

Advanced SQL & SQL*Plus Features

Oracle9i SQL (RT 262) or equivalent experience