This course introduces delegates to the concepts of relational databases and provides an in-depth look at SQL in order to retrieve and manipulate data from an Oracle database.
This course provides the perfect prerequisite knowledge to embark on training for developer components such as Application Express or administrative roles such as Oracle Database Administration.
On completion of the course, delegates will be able to:
Utilise the SQL language to retrieve data from an Oracle database
Use the SQL Developer tool
Edit, store and recall queries, format results, and create standard queries and reports
Write PL/SQL Control Structures
Edit, store & recall PL/SQL blocks
Create Procedures, Functions and Packages
This course is perfect for those who wish to gain a comprehensive understanding of how the Oracle Structured Query Language (SQL) is used for data retrieval and reporting. It goes on to provide an in-depth look at PL/SQL in order to write Stored Procedures, Stored Functions and Stored Packages.
This course is suitable for all recent versions of Oracle, up to and including 12c.
Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform.
Introduction to RDBMS and Oracle
Relational database theory
Overview of oracle
ISO (incorporating ANSI and BSI) standards
Oracle background processes
Filtering by rows and columns
Filtering for distinct or unique values
Use of the Between, Like & In operators
Handling Null values
Filtering with a compound Where clause
Perform a calculation
Use of column alias
Inner joins (Pre and Post SQL99)
Outer joins (Pre and Post SQL99)
Combining inner and outer joins
Aggregate Summary functions
Group By clause
Case statement examples
Introduction to Data Management
Insert via a Select clause
Use of Oracle Data Dictionary
Introduction To Analytic Functions
Cube, rollup & grouping
Partitioned outer join
Analytic Summary Functions
Introduction To Basic SQL Tuning
Overview Of Explain Plan Facility
Overview of Indexes
Database derived variables User defined records
User defined variables
Conditional structures (IF and CASE)
Iteration structures (FOR LOOP and WHILE LOOP)
Cursor basics (OPEN FETCH CLOSE)
Cursor FOR LOOP
Cursor data modifications
Cursor variables (REF CURSOR)
Stored Procedures & Functions
Local procedures and functions
Package creation, definition and body
Single event triggers
Multiple events triggers
Database level triggers
Built-In Oracle Packages
Review of Oracle package philosophy
Use of an Oracle package within PLSQL
Common application development packages
Native Dynamic SQL (NDS)
DBMS_SQL versus NDS
OPEN FOR (CURSOR VARIABLES)
Using BIND variables with NDS
Thinking about Onsite?
If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.
Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.