Menu

Tel: 01423 534630    Email: info@tts-uk.com

Oracle SQL

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.

Versions supported 11gR2, 12cR2, 18c, 19c.

Course Objectives

To provide the skills needed to query and update data held in an Oracle Relational Database.

Who will the Course Benefit?

Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.

Skills Gained

The delegate will learn and acquire skills as follows:

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

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.


  • 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
    Data Types
    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

Duration: 3 days
Price: £1295 + VAT

Payment logos


Book Now Enquire now

Share Course:

Register to attend

    All fields with * are mandatory.