PL/SQL course content

Anonymous Block

                Block structure

                Describing the Significance of Variables in PL/SQL

                Distinguishing Between PL/SQL and Non-PL/SQL Variables

                Declaring Variables and Constants

                Executing a PL/SQL Block

                %type & %rowtype attribute

Cursors in PL/SQL

                Cursor basics

                Using a cursor for a multi-row SQL query

Exception handling

    Using the when others clause

                Ensuring complete error checking

                Passing error messages to calling routine

                Boolean logic in PL/SQL

Condition and Looping statements

                Constructing an IF Statement

                Constructing and Identifying Different Loop Statements

                Controlling Block Flow Using Nested Loops and Labels

                Using Logic Tables

                If-then-else structure

Stored Procedure, Functions and Packages

                Basics of stored procedures

                Basics of functions

                Basics of packages

                Defining stored procedures & functions

                Function and stored procedures prototypes

                Passing arguments to functions and stored procedures

                Recompiling functions and stored procedures

                Pinning packages in the SGA with dbms_shared_pool.keep

                Package forward declaration

                Package dependency

                Package overloading

                Listing package information

Triggers

                Triggers and database events

                Defining a trigger

                Timing a trigger

                Enabling and disabling a trigger

Bulk collection

Advances Cursor

Sql course content | Topics

Basic Database concept and sql      

                   Basic history of database concept: DBMS, RDBMS.

                   Advantage of ORACLE database and version information

                   Interface tools usage: Sqlplus, isqlplus, Sql developer, Toad

                   SQL Language overview: DQL, DML, DDL, DCL, TCL

                   SELECT Command - Column Alias Rules, String data,

                   Concatenations with various data

                   Null Value handling with number and characters,

                   Arithmetic Operator

                   Concatenation Operator,

                   Eliminating Duplicate Rows

Restricting and Sorting Data (Where clause)

                   WHERE Clause - Character Strings and Dates, number

                   General Comparison Conditions = > >= < <= <>

                   Other Comparison BETWEEN , IN , LIKE , NULL

                   Logical Conditions AND OR NOT

                  ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

Single-Row Functions

                Character Functions

                Number Functions

    Dates Functions

    Conversion Functions

    General Functions

                CASE Expression , DECODE

Multi-Row Functions

                Group Functions Rules

                SUM, MIN, MAX, COUNT, AVG

                Creating Groups of Data: GROUP BY Clause

                Filtering Group Results: The HAVING Clause

Sub query

    Sub Queries types

    Single row, multiple row and co-related sub query

                Single-Row Sub queries- Rules, Operators: = > >= < <= <>

                Null Values in a Sub query

                Multi-Row Sub queries- Rules, Operators: IN, ANY, ALL

Joins

               

                EQUI JOIN/SIMPLE JOIN/NORMAL JOIN

                LEFT OUTER, RIGHT OUTER, FULL OUTER

                INNER JOIN

                JOIN ... USING clause

                Oracle standard outer join

                CROSS JOIN

Data Definition Language - DDL

               

                DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE

                DEFAULT Option

                Creating table by using sub query

                              With data

                              Without data

Data Manipulation Language DML and Transaction Control Language TCL

               

                DML :  INSERT, UPDATE, DELETE, MERGE

                TCL   :  COMMIT, ROLLBACK, SAVEPOINT

Constraints

                NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

                Adding a Constraint, Dropping a Constraint,

                Disabling Constraints, Enabling Constraints

View & Materialize View

Other Database Objects

                Sequence- NEXTVAL and CURRVAL

                Index - When to Create an Index, When Not to Create an Index.

                Synonyms

Sqlplus commands

Pseudo columns

                Sysdate, Systimestamp, Rownumn, Rowid.

Set operators

                UNION, UNION ALL, INTERSECT, MINUS.

Metadata - Dictionary Tables

                Tables, Views, Synonyms, Index, Sequence, Constrains.

Explain Plan

Data Loader - Sqlldr

Multi table insert

                With condition

                Without condition