This course introduces students to PL/SQL, Oracle’s procedural extension language for SQL and the Oracle relational database. Participants explore the differences between SQL and PL/SQL. They also examine the characteristics of PL/SQL and how it is used to extend and automate SQL to administer the Oracle database. This course culminates with a project that challenges students to program, implement, and demonstrate a database solution for a business or organization.

Course content

SQL

Introduction to Oracle Database (SQL):

• What is Database
• Why Oracle
• Introduction to SQL and SQL *Plus, More SQL*Plus Commands
• The Data Dictionary

Data Query Language (DQL):

• Categories of SQL Statements
• Oracle Data types
• The SELECT Statement
• Practical examples

 Data Definition Language (DDL):

• DDL Statements
  The CREATE Statement
• The DROP Command
• The ALTER Command
• Practical examples

 Data   Manipulation Language  (DML):                             

• DML Statements,
• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement

Transaction Control Language (TCL):

• TCL Statements
• COMMIT Statement
• ROLLBACK Statement
• SAVEPOINT Statement

 Data Control Language (DCL):

• DCL Statements
• GRANT Statement
• REVOKE Statement

 Integrity Constraints:

• Introduction to Integrity Constraints
• Categories of Integrity Constraints
• NOT NULL Constraints
• UNIQUE KEY Constraints
• PRIMARY KEY Constraints
• FOREIGN KEY or Referential Integrity Constraints
• CHECK Constraints, Practical examples

 SQL Operators:

• Simple Selects
• Comparison Operators
• IN and NOT IN Operators
• BETWEEN Operator
• The LIKE Operator
• Logical Operators
• IS NULL and IS NOT NULL
• ANY
• ALL
• EXISTS

 Set Operators:

• Introduction
• Selection Criteria
• Union
• Union All
• Intersect
• Minus

 Joining Tables:

• Joins
• Table Aliases
• Cartesian Product
• Inner Joins
• Equi- Join
• Non-Equi Join
• Non-Key Join
• Reflexive / Self Join
• Natural Join
• Outer Joins
• Right Outer Join
• Left Outer Join
• Full Outer Join

 SQL Sub queries:

• Introduction
• Using a Sub query with a DML Statement
• Typical Sub queries
• Sub query Operators
• Standard vs. Correlated Sub queries
• Correlated Sub query

 Groups:

• SQL Statements
• GROUP BY Clause
• HAVING Clause
• Order of a SELECT Statement

 SQL BUILT-IN FUNCTIONS:

• Introduction, Pseudo Columns
• GROUP Functions
• MATHEMATICAL / NUMERIC Functions
• STRING / CHARACTER Functions
• DATE / TIME Functions
• CONVERSION Functions
• MISCELLANEOUS Functions

More Database Objects:

• More Database Objects
• VIEWS
• SEQUENCE
• SYNONYMS

PLSQL

Fundamentals

• Introduction to PL/SQL
• Benefits of PL/SQL
• Creating PL/SQL Blocks

Defining Variables and Datatypes

• Using Variables in PL/SQL
• Recognizing PL/SQL Lexical Units
• Recognizing Data Types
• Using Scalar Data Types
• Writing PL/SQL Executable Statements
• Nested Blocks and Variable Scope
• Good Programming Practices

 Using SQL in PL/SQL

• Review of SQL DML
• Retrieving Data in PL/SQL
• Manipulating Data in PL/SQL
• Using Transaction Control Statements

Program Structures to Control Execution Flow

• Conditional Control: IF Statements
• Conditional Control: CASE Statements
• Iterative Control: Basic Loops
• Iterative Control: WHILE and FOR Loops
• Iterative Control: Nested Loops

Using Composite Datatypes

• User-Defined Records
• Indexing Tables of Records

Using Cursors and Parameters

• Introduction to Explicit Cursors
• Using Explicit Cursor Attributes
• Cursor FOR Loops
• Cursors with Parameters
• Using Cursors for UPDATE
• Using Multiple Cursors

Exception Handling

• Handling Exceptions
• Trapping Oracle Server Exceptions
• Trapping User-Defined Exceptions
• Recognizing the Scope of Exceptions

Using and Managing Procedures

• Creating Procedures
• Using Parameters in Procedures
• Passing Parameters

Using and Managing Functions

• Creating Functions
• Using Functions in SQL Statements
• Review of the Data Dictionary
• Managing Procedures and Functions
• Review of Object Privileges
• Using Invoker’s Rights and Autonomous Transactions

Using and Managing Packages

• Creating Packages
• Managing Package Concepts
• Advanced Package Concepts

Getting the Best out of Packages

• Persistent State of Package Variables
• Using Oracle-Supplied Packages

Improving PL/SQL Performance

• Using Dynamic SQL
• Improving PL/SQL Performance

Using and Managing Triggers

• Introduction To Triggers
• Creating DML Triggers, Part I
• Creating DML Triggers, Part II
• Creating DDL and Database Event Triggers
• Managing Triggers

Recognizing and Managing Dependencies

• Introduction to Dependencies
• Understanding Remote Dependencies

Using the PL/SQL Compiler

• Using PL/SQL Initialization Parameters
• Displaying Compiler Warning Messages
• Using Conditional Compilation
• Hiding Your Source Code

To see the full course content Download now