Oracle Fundamentals

Databases & SQL - Oracle Fundamentals

Book Course Now

Course Overview

The Oracle Fundamentals course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL.

The Oracle Fundamentals course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions, SQL*Plus parameters and the basic table and view handling statements are introduced.

The course also provides practical experience in using Oracle's PL/SQL programming language to implement conditional execution, loop control, cursor handling and exception handling.

This course will benefit anyone who needs to use and understand Oracle SQL to query and update an Oracle database; and who also needs an introduction to the PL/SQL programming language.

This is a hands-on, tutor-led course running from 10 - 5.
Lunch and refreshments are provided.

This course can be run as a company specific event at your premises.

  • Course Content

    Course Introduction

    Administration and Course Materials
    Course Structure and Agenda
    Delegate and Trainer Introductions

    Session 1: RELATIONAL DATABASE CONCEPTS

    What is an Oracle Database
    Relational Database Structures
    Tables, Rows and Columns
    Indexes, Primary Keys and Foreign Keys
    Supported Datatypes
    The Data Dictionary

    Session 2:USING SQL*PLUS

    What is SQL*Plus
    Getting Started
    Entering and Executing SQL Statements
    Editing SQL Statements
    Creating, Editing and Executing SQL Files

    Session 3: USING SQL DEVELOPER

    What is Oracle SQL Developer
    Starting SQL Developer
    Configure a Connection
    Navigation Tabs
    SQL Worksheet

    Session 4: RETRIEVING DATA WITH THE SELECT STATEMENT

    The SELECT Statement
    The SELECT and FROM Clauses
    Conditions and the WHERE Clause
    Other Conditional Operators
    Logical Operators
    The ORDER BY Clause
    Column Aliases
    Arithmetic Expressions
    Precedence of Operators

    Session 5: AGGREGATE FUNCTIONS

    Overview of Built In Aggregate Functions
    The GROUP BY Clause
    The HAVING Clause

    Session 6: JOINING TABLES

    Overview of Table Joins
    Inner Joins
    Table Aliases
    Outer Joins
    Self Joins
    ANSI Standard Joins
    Set Operators

    Session 7: NUMERIC, CHARACTER AND DATE FUNCTIONS

    Function Types
    Using the Table dual to try out Functions
    Numeric Functions
    Character Functions
    String Concatenation
    Date Arithmetic and Date Functions

    Session 8: CONVERSION AND MISCELLANEOUS FUNCTIONS

    Conversion Functions
    The NVL and NVL2 Functions
    The DECODE Function
    CASE Expressions
    The COALESCE and NULLIF Functions

    Session 9: SQL*PLUS PARAMETERS

    Command Line Substitution Parameters
    The Accept Command
    The Define and Undefine Commands

    Session 10: USING SUBQUERIES

    Overview of Subqueries
    Use a Subquery as an Alternative to Join
    Handle Multiple Records in Subqueries
    Subquery in a Having Clause
    Anti-Join
    In-Line Views
    Top-N Queries
    Complex Subqueries
    Multi Column Subqueries
    Correlated Subqueries
    Subquery Rules
    Combining Unrelated Aggregates
    Using the ANY, ALL and SOME Operators

    Session 11: MANAGING DATA

    Inserting Rows
    Updating Rows
    Deleting Rows
    Verifying Updates
    Transaction Control
    Commit and Rollback
    Savepoints
    Commits and Constraints
    Amending Data in SQL Developer

    Session 12: MANAGING TABLES

    Creating Tables
    Specifying Constraints
    Altering Tables, Columns and Constraints
    Dropping Tables, Columns and Constraints
    Copying Tables

    Session 13: MANAGING INDEXES AND VIEWS

    Creating Indexes
    Dropping Indexes
    Listing Indexes
    Creating and Using Views
    Dropping Views
    Listing Views

    Session 14: MANAGING SEQUENCES AND SYNONYMS

    Create a Sequence
    View Sequence Details
    Create a Synonym
    List Synonyms

    Session 15: PL/SQL FUNDAMENTALS

    What is PL/SQL?
    Basic Elements
    Variables and Constants
    Data Types
    Initialising Variables and Assigning Values
    Using SQL Statements in Code
    Generating Output to SQL or SQL Developer

    Session 16: PROGRAM LOGIC

    IF THEN ELSIF ELSE Statements
    CASE Statements
    The Basic Loop Construct
    WHILE and FOR Loops
    Nested and Labelled Loops
    The GOTO Statement
    The CONTINUE Statement

    Session 17: USING CURSORS

    What is a Cursor?
    Implicit and Explicit Cursors
    Cursor Operations
    Declaring, Opening and Closing Cursors
    Fetching Rows
    Status Checking
    Using Cursors FOR UPDATE
    The Cursor FOR Loop
    Parameterised Cursors

    Session 18: EXCEPTIONS AND NESTED BLOCKS

    The EXCEPTION Section
    Types of Exception
    Handling Named System-Raised Exceptions
    Handling Un-named System-Raised Exceptions
    User-Declared Exceptions and Application Errors
    WHEN OTHERS THEN NULL
    Nested and Labelled Blocks
    Propagation of Exceptions
    Scope of Variables and Cursors
    Scope of Goto Statements

    Session 19: PL/SQL RECORDS AND INDEX-BY TABLES

    Declaring Record Types
    Handling PL/SQL Records
    Nested Records
    Declaring PL/SQL Index-By Tables or Associative Arrays
    PL/SQL Table Built-in Functions
    Manipulating PL/SQL Tables or Associative Arrays

  • Prerequisites

    An understanding of databases and exposure to information technology in general would be useful.

  • Qualification

    A certificate of achievement will be provided.

  • Follow on courses include:
    Oracle PL/SQL Stored Program Units
    Oracle Reports
    Oracle Database 10g DBA - Part I
    Oracle Database 11g Release 2 DBA - Part I
    Oracle Forms - Part I

Booking Options Available for Oracle Fundamentals

Please select the location you want to book the course

Download Booking Form

Alternatively you can download our booking form, fill in all the relevant information and mail it back to us.

Tailored Training

Tailored Training

Take advantage of a customised training programme to suit your needs at a location of your choice.

Learn More

Funded Training

Funded Training

Funding might be available for training depending on your circumstances.

Learn More

Funded Redundancy Training

Funded Redundancy Training

Are you about to be made redundant or have you been made redundant in the last three months? 100% funded training is available.

Learn More

Email Updates

Email Updates - Join Our Mailing List

Join our mailing list to keep updated on exclusive offers and latest news on our training courses.

  • ECDL
  • ECDL Advanced
  • Approved Test Centre

  • Computeraid Ltd, 4th Floor, 11 Wind Street, Swansea, SA1 1DP

  • Telephone: 01792 610550 / 029 2066 4285
  • [email protected]
Join Computeraid Ltd on Facebook
Follow Computeraid Ltd on Twitter
Tweets by @ComputeraidLtd
View Dizzee Designs on LinkedIn

Computeraid Ltd

Computeraid Ltd – LinkedIn

Computeraid is the largest supplier independent computer training and consultancy company in South Wales, training around 20,000
people days annually.

Swansea. South Wales