Oracle SQL, PL/SQL Developer

Oracle SQL PL/SQL Training Course

 Table of Contents:

PART I:

1.     Introduction to Oracle SQL.

2.     Installation of Oracle Database and SQL Developer.

3.     Role of SQL in RDBMS.

PART II: Structured Query Language – SQL

4.     Basic SQL construct

5.     Column naming conventions

6.     Importance of NULL

7.    Data Types

8.    Literals

9.    Format Models

10.  Database Objects

11.  Database Object Names and Qualifiers

12.  Arithmetic Operators

13.  Concatenation Operator

14.  Set Operators

PART III: Functions and Expressions

15.  Dual table

16.  Single-Row Functions

a.       Numeric Functions

b.      Character Functions

c.       Datetime Functions

d.      Conversion Functions

e.      Large Object Functions

17.  Aggregate Functions

18.  Analytic Functions

19.  Model Functions

20.  OLAP Functions

21.  Simple Expressions

22.  Compound Expressions

23.  CASE Expressions

24.  Column Expressions

25.  Datetime Expressions

26.  Function Expressions

PART IV: SQL Queries and Subqueries

27.  Creating Simple Queries

28.  Applying conditions

a.       Comparison Conditions

b.      Logical Conditions

c.       Multiset Conditions

d.      Null Conditions

e.      BETWEEN Condition

f.        EXISTS Condition

g.       IN Condition

h.      Compound Conditions

29.  Sorting Query Results

30.  Joins

31.  Using Subqueries

32.  Unnesting of Nested Subqueries

33.  Selecting from the DUAL Table

PART V: DDL, DML, DCL and TCL

34.  INSERT statement

35.  UPDATE statement

36.  DELETE statement

37.  Definition of Transaction

38.  COMMIT & ROLLBACK statements

39.  Using the SAVEPOINT statement

40.  MERGE statement

41.  Creating tables

42.  Modifying tables, adding constraints to a table, renaming a table and its columns.

43.  Dropping columns (set unused command) and dropping tables.

44.  Creating and replacing views

45.  Creating and dropping public synonyms

46.  Creating modifying and dropping sequences

47.  Grant and Revoke of object privileges between two Oracle database users

48.  Need for Indexes

49.  Types of Indexes

50.  Composite Indexes

51.  Function based Indexes

52.  Analyzing and rebuilding Indexes

53.  Materialized Views

PART VI: Procedural Language/ Structured Query Language – PLSQL

1.       PL/SQL Language Fundamentals

a.      The anonymous PLSQL block

b.      Variables

c.       Data types

d.      Bind variables

e.      PLSQL operators

f.        Nested blocks

g.      Implicit data conversion

h.      SELECT … INTO statement

i.        DBMS_OUTPUT.PUT_LINE

2.       PL/SQL Data Types

3.       PL/SQL Control Statements

a.       Conditional selection statements

b.      Loop statements

c.       Sequential control statements

4.       Composite data types

a.       Collections

b.      Records

5.       PL/SQL Static SQL

a.       Cursors

b.      Query Result Set Processing

c.       Cursor Variables

d.      CURSOR Expressions

e.      Autonomous Transactions

6.       PL/SQL Dynamic SQL

a.       When You Need Dynamic SQL

b.      Native Dynamic SQL

c.       DBMS_SQL Package

d.      SQL Injection

7.       PL/SQL Subprograms

a.       Reasons to Use Subprograms

b.      Subprogram Invocations

c.       Subprogram Parts

d.      Forward Declaration

e.      Subprogram Parameters

f.        Overloaded Subprograms

g.       Subprogram Side Effects

h.      PL/SQL Function Result Cache

i.         Procedure

j.        Functions

8.       PL/SQL Triggers

a.       Overview of Triggers

b.      Reasons to Use Triggers

c.       DML Triggers

d.      System Triggers

e.      Subprograms Invoked by Triggers

f.        Trigger Compilation, Invalidation, and Recompilation

g.       Exception Handling in Triggers

h.      Trigger Design Guidelines

i.         Trigger Restrictions

j.        Order in Which Triggers Fire

k.       Trigger Enabling and Disabling

l.         Trigger Changing and Debugging

9.       PL/SQL Packages

a.       What is a Package?

b.      Reasons to Use Packages

c.       Package Specification

d.      Package Body

e.      Package Instantiation and Initialization

f.        Package State

g.       Package Writing Guidelines

h.      Package Example

10.   PL/SQL Error Handling

a.       Compile-Time Warnings

b.      Overview of Exception Handling

c.       Internally Defined Exceptions

d.      Predefined Exceptions

e.      User-Defined Exceptions

f.        Redeclared Predefined Exceptions

g.       Raising Exceptions Explicitly

h.      Exception Propagation

i.         Unhandl