Oracle 10g/11g

  • 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
  • Data Definition Language
    • CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE
  • 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
  • Other Concepts
    • The Relational Database Model, The system Development Lifecycle, Overview of normalization
  • PL/SQL Blocks
    • 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 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

QUICK ENQUIRY