Oracle 10g/11g Course

ORACLE

ORACLE – 10G/11G COURSE CONTENT

1. Oracle 10g/11g – SQL & PL/SQL The Oracle database environment
   •  Reviewing basic architecture concepts Oracle 10g Architecture Main features of 9i and 10g
2. Basic SQL*PLUS commands
   •  Using script files, START, @, GET, SAVE, LIST, PROMPT, PAUSE, ACCEPT, DEFINE, SPOOL
3. Basic SELECT Statement
   • Writing the statement in sql plus, Running the SELECT statement
4. Ordering the output
   • Single Column, Descending order, multiple column sorts
5. Conditional retrieval of data
   • Working with complex conditions, AND OR NOT LIKE BETWEEN
6. Working with variables
   • Creating and using variables, Command substitution
7. 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()
8. Using non-character function
   •  ROUND (), TRUNC (), SIGN (), Working with multiple tables, Different type of Joins, Writing Outer Joins
9. Using the SET operators
   •  Union, Intersect, minus
10. Aggregating data using group functions
    • GROUP BY, HAVING
11. Creating Sub queries
    • Single Row sub queries, Multiple row Sub queries
12. Enhancing groups function
    •  ROLLUP, CUBE
13. Transaction Control Language
    •  Rollback, Commit, save point
14. Processing hierarchies
    •  creating the Tree structure, LEVEL, CONNECT BY
15. Data Manipulation Language
    • INSERT, UPDATE, DELETE
16. Data Definition Language
    •  CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE
17. 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
18. Database Security
    •  Object Privileges, Granting access to objects
19. Other Concepts
    •  The Relational Database Model, The system Development Lifecycle, Overview of normalization
20. PL/SQL Blocks
    •  Structure, Writing Anonymous Blocks
21. Variables
    •  Oracle Data types, TYPE and ROWTYPE declarations, Value assignments
22. Control structures
    •  IF ELSE ENDIF statement, IF ELSIF ELSE ENDIF statement, LOOP END , LOOP statement, WHILE condition, FOR condition
23. Cursors
    •  Implicit and Explicit Cursors, Cursor Manipulation statements, OPEN FETCH CLOSE EXIT WHEN
24. Printing Values to Screen Procedures
    •  Creating and Calling Procedures
25. Functions
    •  Creating and Calling Functions
26. Packages
    •  Package Header, Package Body
27. Design Tips and Techniques
    • Format of standard packages in an application, Spec and Body, NOCOPY hint
28. Triggers
    •  Database triggers, calling procedures from triggers
29. Dynamic SQL
    •  EXECUTE IMMEDIATE, DBMS_SQL package
30. Oracle Supplied packages
    •  DBMS_OUTPUT, UTL_FILE
31. Collection data types
    •  Associative Arrays, Nested tables, VARRAYS