Oracle SQL-PL/SQL Fast

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

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