Oracle Database Essentials - SQL & PL/SQL

Course details

The course starts at introduction basic to intermediate database concepts and progresses to teach the world's most popular query language for databases SQL. Finally participants are given a good working experience on PL/SQL and introduced to advanced concepts of how Packages, Triggers, Cursors are written.

Who Should Take This Course:

Databases are used to store data of all systems and applications. We use applications on a daily basis from our mobile phones to ATMs to online portals. All of these use database heavily.

Most programming, support, application oriented, database developer jobs and some system administration jobs need knowledge of database.

It has become quite important to learn database in today's times and most employers expect knowledge of database basics and SQL / PLSQL.

Course Content:

Database Basics

• What is a Database?
• The RDBMS concept and organization.
• Primary keys, foreign keys
• Other database concepts

Basics of PL/SQL

• PL/SQL architecture
• PL/SQL and SQL * Plus
• PL/SQL Basics, Variables, Constants, data-types and error handling
• PL/SQL wrapper utility

PL/SQL structures

• Simple blocks
• Control structures
• PL/SQL records
• Recognizing the Basic PL/SQL Blocks and Its Section
• Describing the Significance of Variables in PL/SQL
• Distinguishing Between PL/SQL and non-PL/SQL variable
• Declaring Variables and Constants
• Executing a PL/SQL Block

Day 2: Boolean logic in PL/SQL

• Identifying the Uses and Types of
• Control Structures
• 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
• Testing for numbers characters and Booleans
• for & while loop
• Nested blocks in PL/SQL
• Creating nested blocks
• Understanding scope in nested Blocks

Day 3: PL/SQL tables

• Defining PL/SQL tables
• Reasons to use PL/SQL tables
• Populating a PL/SQL table
• Retrieving from a PL/SQL table

Nested blocks in PL/SQL

• Creating nested blocks
• Understanding scope in nested Blocks

Error checking – exception handling

• Defining exceptions
• Using the when others clause
• Ensuring complete error checking
• Passing error messages to calling Routine

Cursors in PL/SQL

• Cursor basics
• Using a cursor for a multi-row SQL query

Day 4: Triggers in PL/SQL

• Triggers and database events
• Defining a trigger
• Timing a trigger
• Enabling and disabling a trigger

Stored procedures, 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