We use cookies to offer you a better experience and to analyse how visitors use our site, you may consent to our cookies by continuing to use our site, or you may view more information and choose to reject cookies by clicking here. Thank you

Contact us online or call us on 0191 377 8377

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields

PDF
Course Oracle SQL
  • Public Scheduled Price£1350 +VAT
  • Course Duration3 Days
  • Course Options
    Bespoke
    Bespoke
    Bespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.
    Private
    Private
    Flexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
    Public Scheduled
    Public Scheduled
    Fixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!

Overview
The Oracle SQL 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 and the basic table and view handling statements are introduced.
Prerequisites
    • There are no formal pre-requisites for the Oracle SQL course, although an understanding of databases and exposure to information technology in general would be useful.
Objectives
    • Creating SQL statements to query database tables
      Using standard aggregate functions and related SELECT statement clauses
      Joining Tables
      Using basic subqueries and the set operators
      Using numeric, character and date functions
      Using conversion and other miscellaneous functions
      Using SQL parameters
      Using complex subqueries
      Creating and altering tables and views
      Inserting, updating and deleting rows in database tables
      Managing sequences and synonyms
Certification
Together with the Oracle SQL Advanced course, the Oracle SQL course also covers the major topics required for the following Oracle examination:

Oracle Database SQL Fundamentals 1Z0-071
Before taking this exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

This exam is not included as part of the course.

Target Audience
    • Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.
Course Content
    • DAY 1
      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
      The SQL*Plus Command Line History
      Editing SQL Statements
      The login.sql File
      Creating, Editing and Executing SQL Files
      The SQLcl Utility


      Session 3: USING SQL DEVELOPER
      What is Oracle SQL Developer
      Starting SQL Developer
      Configure a Connection
      Navigation Tabs
      SQL Worksheet
      Query Builder


      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
      Markup CSV


      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


      Session 7: BASIC SUBQUERIES AND SET OPERATORS
      Overview of Subqueries
      Basic Subqueries
      Set Operators
      The Union, Intersect and Minus Operators
    • DAY 2
      Session 8: 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 9: CONVERSION AND MISCELLANEOUS FUNCTIONS
      Conversion Functions
      The NVL and NVL2 Functions
      The DECODE Function
      CASE Expressions
      The COALESCE and NULLIF Functions


      Session 10: SQL PARAMETERS
      Command Line Substitution Parameters
      The Accept Command
      The Define and Undefine Commands


      Session 11: COMPLEX SUBQUERIES
      Subqueries Usage
      In-Line Views
      Top-N Queries
      Subqueries with Joins
      Multi Column Subqueries
      Correlated Subqueries
      Subquery Rules
      Using the ANY, ALL and SOME Operators
    • DAY 3
      Session 12: 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 13: MANAGING TABLES
      Creating Tables
      Specifying Constraints
      Altering Tables, Columns and Constraints
      Dropping Tables, Columns and Constraints
      Recovering Dropped Tables
      Copying Tables


      Session 14: MANAGING INDEXES AND VIEWS
      Creating Indexes
      Dropping Indexes
      Listing Indexes
      Creating and Using Views
      Dropping Views
      Listing Views


      Session 15: MANAGING SEQUENCES AND SYNONYMS
      Create a Sequence
      View Sequence Details
      Create a Synonym
      List Synonyms
What's included
    • Comprehensive course materials and exercises
    • For courses held on our premises, complimentary tea, coffee and biscuits are available. Lunch is not provided, however, there is a wide variety of eateries within a short walk of the centre.

 

IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk

Printed: 21 Nov 19

 

 

 

Contact us

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields