Introduction : Types of set ups under which corporate / multi user systems work, Introduction to Oracle as database system, Other popular DB systems in market, Oracle corporation products which work around Oracle DB System.
RDBMS Theory : Meaning of the term DBMS, Relational model, Universal relation, Why relational model, Concepts of redundancy / dependency /decomposition, Normalization steps & basis, RDBMS aspects as per Codd’s rules – Declarative access / NULL treatment / Data dictionary / Relational Algebra
Oracle Architecture : Parts of Oracle system – Storage / Memory / Processes, Logical & Physical storage, Meaning & purpose of units of logical & physical storage, Memory structures – SGA & PGA, Important parts of SGA, Function & behavior of each part of SGA, Background processes classification – Mandatory /Optional / Miscellaneous, brief account of each BG process, Overall flow of SQL query through architecture.
Select Statement : Basic SELECT statement structure – Verb / Projection / Source / Predicate, Use of logical operators – AND / OR / NOT, Rule of precedence of operators, pattern matching operator LIKE / DISTINCT / IN / BETWEEN use & important limitations, DECODE, CASE.
Sub-Queries : Sub-queries concept, use with examples, operators ALL/ANY, Correlated sub-queries examples, EXISTS, Dealing with multiple / NULL values sent by sub-query.
Joins : Join operation, Equijoin / non-equijoin / self join / outer join types / natural join, Use of In-Line views in joins, complex problem solutions using joins
Aggregate & Group Functions : MAX / MIN / AVG / COUNT / SUM, complex problems using group functions and based on Product operation of tables (nth highest, running sum, maximum average etc.), Group function extensions – ROLLUP, CUBE, Use of operators UNION / MINUS / INTERSECT
Single Row Functions : Conversion functions – TO_NUMBER / TO_DATE / TO_CHAR, Numeric functions – SQRT / POWER / MOD / SIGN / ROUND / TRUNC / CEIL / FLOOR, String functions – UPPER / LOWER / INITCAP / SUBSTR / INSTR / LPAD / RPAD / LTRIM / RTRIM / TRIM / LENGTH / REVERSE / REPLACE / TRANSLATE, Date functions – MONTHS_BETWEEN / ADD_MONTHS / NEXT_DAY / LAST_DAY, Techniques of solving complex problems by using combination / nesting of functions with examples.
DML & Transactions : Concept of Transaction, ACID property of transaction, various forms of INSERT, UPDATE, DELETE with complex problem examples, Optimistic & Pessimistic locking, Transaction concurrency & consistent read, READ ONLY TRANSACTION, Use of SAVEPOINT.
CREATE TABLE : Data types : CHAR / VARCHAR / VARCHAR2 / NUMBER / DATE / RAW / LONG / LONG RAW / BLOB / CLOB / Bfile, CREATE TABLE syntax, Changing datatypes, Changing precision, Adding / Dropping Columns, Setting columns to UNUSED, RENAME Table/Column, Creating comments on Table/column.
Constraints : Data Integrity – ENTITY / REFERENTIAL / DOMAIN, Constraints – Primary Key / References / Check / Not Null / Unique, Constraint meta data, Enabling / Disabling constraint, Dealing with invalid data, Invalid data detection method, NOVALIDATE clause, Enabling / Disabling Primary Key limitations when references exist, CASCADE, ON DELETE CASCADE, ON DELETE SET NULL, CASCADE CONSTRAINTS, Deferrable constraint and cases when used examples.
PL/SQL Introduction : PL/SQL as back-end language need & advantage, Block structure – Declaration / Executable / Exception handling sections, PL/SQL Special Data Types – %TYPE / %ROWTYPE / RECORD / BOOLEAN, Block nesting, Control Structures – Conditional branching / Looping, Basic PL/SQL block examples
Exception Handling : Types of Exceptions – In-Built / User Defined / Pragma Exception, Exception handling rules, Exception handling in nested blocks, Raising custom errors.
Cursors : Explicit & Implicit Cursor, Stages of execution, Exceptions associated with implicit SELECT, CURSOR FOR loop structure, Parameterized cursor, Cursors with FOR UPDATE clause, WHERE CURRENT OF clause
Procedures & Functions : Difference between procedure & function properties, Modes of Parameters – IN / OUT / IN OUT, Guidelines when to use Procedure or Function
Packages : Why use package, Package structure – Package Header / Package Body, Properties of Package Header / Body, Function or Procedure Overloading in Package, Package Initialization, Applications of Package initialization
Triggers : Classification – ROW level / STATEMENT level, Timing – BEFORE / AFTER, Suitability guidelines, Mutating Table error work around, WHEN clause, Trigger predicates, Table VIEWS, Views behavior, WITH CHECK OPTION / WITH READ ONLY Views, INSTEAD OF triggers
Object Types : Object oriented concept, Defining Object Type – TYPE HEADER / TYPE BODY, Method overriding, Object Table, Column Object, Constructor, Manipulation of data in column object
Collections : Collection types – INDEX BY TABLES / NESTED TABLES / VARRAYS, THE / TABLE Operator