ORACLE 10g/11g Course Content

 
Oracle 10g/11g - SQL & PL/SQL
 
The Oracle database environment
  • Reviewing basic architecture concepts
  • Oracle 10g Architecture
  • Main features of 9i and 10g
 
Basic SQL*PLUS commands
  • Using script files, START, @, GET, SAVE, LIST, PROMPT, PAUSE, ACCEPT, DEFINE, SPOOL
 
Basic SELECT Statement
  • Writing the statement in sql plus, Running the SELECT statement
 
Ordering the output
  • Single Column, Descending order, multiple column sorts
 
 
Conditional retrieval of data
  • Working with complex conditions, AND OR NOT LIKE BETWEEN
 
 
Working with variables
  • Creating and using variables, Command substitution
 
 
Pseudo columns and functions
  • Pseudo Columns, Row num, Sys date, User & UID, the Dual Table
  • Working with character functions
  • UPPER, LOWER, INITCAP, RPAD(), RTRIM(), SUBSTR(), INSTR(),
    TRANSLATE(), REPLACE(), GREATEST(), LEAST(), DECODE
  • Working with date functions
  • TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY()
 
Using non-character function
  • ROUND (), TRUNC (), SIGN (), Working with multiple tables, Different type of Joins, Writing Outer Joins
 
 
Using the SET operators
 
  • Union, Intersect, minus
 
 

Aggregating data using group functions

  • GROUP BY, HAVING
 
 
Creating Sub queries
  • Single Row sub queries, Multiple row Sub queries
 
 
Enhancing groups function
  • ROLLUP, CUBE
 
 
Transaction Control Language
  • Rollback, Commit, save point
 
 
Processing hierarchies
  • creating the Tree structure, LEVEL, CONNECT BY
 
 
Data Manipulation Language
  • INSERT, UPDATE, DELETE
 
 
Using Declarative Constraints
  • Views, Sequences, Synonyms, Indexes, USER_TABLES, USER_TAB_COLUMNS,
    USER_OBJECTS, USER_IND_COLUMNS, USER_UPDATEABLE_COLUMNS, Materialized
    Views, Other Data dictionary views
 
 
Database Security
  • Object Privileges, Granting access to objects
 
 
Improving query performance
  • Planning and managing the tuning process, Employing tuning tools,
    Running EXPLAIN PLAN and auto trace, SQL Trace and TKPROF output,
    Optimizer concepts, Fundamentals of access paths, Gathering object and system
    statistics with DBMS_STATS, Utilizing hints and optimizer mode,
    Specifying first-rows optimization, Determining the driving table
 
 
Other Concepts
  • The Relational Database Model, The system Development Lifecycle, Overview of normalization
 
 
PL/SQL
  • CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE
 
 
Data Definition Language
  • Structure, Writing Anonymous Blocks
 
 
Variables
  • Oracle Data types, TYPE and ROWTYPE declarations, Value assignments
 
 
Control structures
  • IF ELSE ENDIF statement, IF ELSIF ELSE ENDIF statement,
    LOOP END , LOOP statement, WHILE condition, FOR condition
 
 
Cursors
  • Implicit and Explicit Cursors, Cursor Manipulation statements,
    OPEN FETCH CLOSE EXIT WHEN
 
 
Printing Values to Screen
  • Creating and Calling Procedures
 
 
Functions
  • Creating and Calling Functions
 
 
Packages
  • Package Header, Package Body
 
 
Design Tips and Techniques
  • Format of standard packages in an application, Spec and Body, NOCOPY hint
 
 
Triggers
  • Database triggers, calling procedures from triggers
 
 
Dynamic SQL
  • EXECUTE IMMEDIATE, DBMS_SQL package
 
 
Oracle Supplied packages
  • DBMS_OUTPUT, UTL_FILE
 
 
Collection data types
  • Associative Arrays, Nested tables, VARRAYs
 
 
Printing Values to Screen
 
Procedures
  • Creating and Calling Procedures
 
 
Functions
  • Creating and Calling Functions
 
 
Packages
  • Package Header, Package Body
 
 
Design Tips and Techniques
  • Format of standard packages in an application, Spec and Body, NOCOPY hint
 
 
Triggers
  • Database triggers, calling procedures from triggers
 
 
Dynamic SQL
  • EXECUTE IMMEDIATE, DBMS_SQL package
 
 
Oracle Supplied packages
  • DBMS_OUTPUT, UTL_FILE
 
 
Collection data types
  • Associative Arrays, Nested tables, VARRAYs