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