|
||||||||
Oracle 11g SQL-PL/SQL (Duration: 40 Hrs)
SQL
The Oracle database environment
- Reviewing basic architecture concepts, Oracle 10g Architecture, Main features of 9i and 10g
Basic SELECT Statement
- Writing the statement in sql plus, Running the SELECT statement
Ordering the output
- Single Column, Descending order, Multiple column sort
Conditional retrieval of data
- Working with complex conditions, AND OR NOT ,,,, LIKE BETWEEN
Pseudo columns and functions
- Pseudo Columns, Rownum, Sysdate, User & UID, the Dual Table
Working with character functions
- UPPER, LOWER, INITCAP, RPAD(), TRIM(), SUBSTR(), INSTR(), TRANSLATE(), REPLACE(), GREATEST(), LEAST(),
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, Savepoint
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
- Not Null Constraint, Check Constraint, Unique Constraint, Primary Key Constraint, References Constraint, On Delete Cascade, On Delete Set Null
Other Database Objects
- Views, Sequences, Synonyms, Indexes USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS
Database Security
- Object Privileges, Granting access to objects
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
Error Handling
- Predefined Exceptions, Non-Predefined Exceptions, User Defined Exceptions
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 ------------- theory
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