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 Fundamentals
  • Public Scheduled Price£2120 +VAT
  • Course Duration5 Days
  • Course Options
    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!
    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.

Overview
The Oracle SQL Fundamentals course is designed to give delegates practical experience in writing Oracle SQL statements and scripts. A wide range of SQL functions and data manipulation statements are introduced.
Prerequisites
    • There are no formal pre-requisites for the Oracle SQL Fundamentals course, although an understanding of databases and exposure to information technology in general would be useful.
Objectives
    • CreatE Oracle SQL statements to query database tables
    • UsE standard aggregate functions and related SELECT statement clauses
    • Implement extended SQL functions
    • Join Tables
    • UsE subqueries
    • CreatE and alter tables and views
    • Insert, update and delete rows in database tables
    • Retrieve Data Using Correlated Subqueries, In-Line Views and Multi-Column Subqueries
    • Use the WITH Clause
    • Insert and Update Rows Using Complex Subqueries
    • Create Hierarchical Queries
    • Use ROLLUP, CUBE and GROUPING SETS Group By Enhancements
    • Implement the GROUPING and GROUPING_ID Functions
    • Manage Large Data Sets
    • Insert Data Conditionally and Unconditionally
    • Merge Rows in a Table
    • Create TIMESTAMP and INTERVAL Datatypes
    • Use TIMESTAMP, TIMEZONE and INTERVAL Functions and Literals
    • Use Regular Expressions and Implementing Related Functions
    • Manage Objects with the Data Dictionary
    • Manage Schema Objects
    • Perform Flashback Operations
    • Create and Use External Tables
    • Control User Access to Database Objects
Target Audience
    • Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.
Course Content
    • DAY 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

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

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

      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

      AGGREGATE FUNCTIONS
      Overview of Built In Aggregate Functions
      The GROUP BY Clause
      The HAVING Clause
    • DAY 2
      JOINING TABLES
      Overview of Table Joins
      Inner Joins
      Table Aliases
      Outer Joins
      Self Joins
      ANSI Standard Joins

      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

      CONVERSION AND MISCELLANEOUS FUNCTIONS
      Conversion Functions
      The NVL and NVL2 Functions
      The DECODE Function
      CASE Expressions
      The COALESCE and NULLIF Functions

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

      USING SUBQUERIES
      Overview of Subqueries
      In-Line Views
      Top-N Queries
      Complex Subqueries
      Multi Column Subqueries
      Correlated Subqueries
      Subquery Rules
      Using the ANY, ALL and SOME Operators
    • DAY 3
      MANAGING DATA
      Inserting Rows
      Updating Rows
      Deleting Rows
      Verifying Updates
      Transaction Control
      Commit and Rollback
      Savepoints
      Commits and Constraints
      Amending Data in SQL Developer

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

      MANAGING INDEXES AND VIEWS
      Creating Indexes
      Dropping Indexes
      Listing Indexes
      Creating and Using Views
      Dropping Views
      Listing Views

      MANAGING SEQUENCES AND SYNONYMS
      Create a Sequence
      View Sequence Details
      Create a Synonym
      List Synonyms
    • DAY 4
      RETRIEVE DATA USING SUBQUERIES
      ANY, ALL or SOME Operators
      Correlated Subqueries
      In-Line Views
      The Exists Operator
      The WITH Clause
      Multi-Column Subqueries
      Insert and Update Using a Query
      Correlated Update and Delete

      HIERARCHICAL QUERIES
      Hierarchical data
      The START WITH and CONNECT BY clauses
      The LEVEL pseudo-column
      Sequencing the output
      Eliminating nodes and branches

      ENHANCED GROUPING FEATURES
      Review of basic grouping concepts
      The ROLLUP and CUBE extensions
      The GROUPING SETS extension
      Using the GROUPING and GROUPING_ID functions

      MANAGE LARGE DATA SETS
      Multi-table Inserts
      Conditional and Unconditional Inserts
      Merging Data into a Table
      Table and View Based Merge
      A Sub Query Based Merge

      FLASHBACK TECHNOLOGY
      Flashback Query
      The AS Clause
      Flashback Table
      Flashback Drop
    • DAY 5
      DATE, TIME AND TIMEZONE SUPPORT
      Date, Timestamp and Interval datatypes
      Handling dates and times
      Handling intervals
      Date, timestamp and interval functions and literals
      Related NLS parameters

      REGULAR EXPRESSION SUPPORT
      Regular Expression Notation
      Character matching
      Repetition operators
      Sub expression grouping
      Regular expression functions

      MANAGE OBJECTS WITH THE DATA DICTIONARY
      The Data Dictionary
      Useful Data Dictionary Tables
      Using the Data Dictionary

      MANAGE SCHEMA OBJECTS
      Column Operations
      Constraint Operations
      Creating and Dropping Indexes
      B-Tree Indexes
      Function Based Indexes
      Descending Indexes
      Creating and Using External Tables

      ACCESS CONTROL
      System Privileges and Roles
      Create Users
      Alter User Details
      Grant and Revoke Object Privileges
What's included
    • 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.
    • Comprehensive course materials and exercises

 

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