Oracle SQL and PL SQL Fast-Track

Duration: 
5 days
Codes: 
SQL,PL

Overview

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

Audience

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.

Prerequisites

Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform.

Course Outline

  • Introduction to RDBMS and Oracle
  • Relational database theory
  • Overview of oracle
  • ISO (incorporating ANSI and BSI) standards
  • Oracle architecture
  • Oracle background processes
  • SQL Developer
  • Basic SQL
  • 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
  • Sorting results
  • Perform a calculation
  • Use of column alias
  • Deriving values
  • Data types
  • Substitution variables
  • Joins
  • Inner joins (Pre and Post SQL99)
  • Outer joins (Pre and Post SQL99)
  • Combining inner and outer joins
  • Functions
  • Conversion functions
  • Date functions
  • Character functions
  • Numeric functions
  • Aggregate Summary functions
  • Group By clause
  • Having.clause
  • Sub-Queries
  • Nested sub-queries
  • Multi-column sub-queries
  • Correlated sub-queries
  • Exists clause
  • Inline views
  • Case Statements
  • Case statement examples
  • Set Operators
  • Union
  • Intersect
  • Minus
  • Introduction to Data Management
  • Insert records
  • Insert via a Select clause
  • Multi-table inserts
  • Update records
  • Delete records
  • Transactions
  • Locking
  • Use of Oracle Data Dictionary
  • Introduction To Analytic Functions
  • Cube, rollup & grouping
  • Partitioned outer join
  • Analytic Summary Functions
  • Hierarchical queries
  • Introduction To Basic SQL Tuning
  • Overview Of Explain Plan Facility
  • Overview of Indexes
  • Programming Overview
  • Local variables
  • Database derived variables User defined records
  • User defined variables
  • Variable assignment
  • Conditional structures (IF and CASE)
  • Iteration structures (FOR LOOP and WHILE LOOP)
  • Exception Handling
  • User defined
  • Oracle predefined
  • Oracle defined
  • Nested blocks
  • Cursors
  • Cursor basics (OPEN FETCH CLOSE)
  • Cursor FOR LOOP
  • Cursor data modifications
  • Implicit cursors
  • Cursor variables (REF CURSOR)
  • Stored Procedures & Functions
  • Advantages
  • Structure
  • Input parameters
  • Output parameters
  • I/O parameters
  • Local procedures and functions
  • Compilation
  • Packages
  • Package structure
  • Package creation, definition and body
  • Triggers
  • Trigger roles
  • Trigger restrictions
  • Trigger execution
  • Trigger creation
  • Single event triggers
  • Multiple events triggers
  • View triggers
  • DDL 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
  • NDS Commands
  • EXECUTE IMMEDIATE
  • 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.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Somerset Bath5 1750 £1750 2019-10-07
Somerset Bath5 1750 £1750 2019-12-02