Course Description

This 2-day Oracle SQL Programming Introduction training course teaches students how to create, retrieve, and manipulate objects using the Oracle Structured Query Language (SQL). Students will also be introduced to the features and tools of the Oracle database system.

While this course utilises the Oracle 11g release for practical purposes, it is highly suitable for users of earlier or more recent releases of the Oracle RDBMS.

Pre-requisites

Familiarity with relational database concepts is recommended.

Who is this course for?

For application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle database system.

Further Training

After some further on-the-job programming experience using Oracle SQL, we recommend students consider attendance at our Oracle SQL Programming Advanced training course to gain higher-level skills in the usage of Oracle SQL.

Course content

Relational Database & SQL Overview
Review of Relational Database Terminology
Relational Database Management Systems
SQL Datatypes
Introduction to SQL
Oracle Database
Oracle Versioning & History
Logical & Physical Storage Structures
Datatypes
Overview of Oracle Architecture
Connecting to Oracle
SQL*Plus
Graphical Clients
The Oracle Data Dictionary
Sample Database
SQL Queries - the SELECT Statement
The SELECT Statement
Choosing Rows with the WHERE Clause
NULL Values
Compound Expressions
IN and BETWEEN
Pattern Matching
LIKE and REGEXP_LIKE
The CASE…WHEN Expression
Creating Some Order
Scalar Functions
SQL Functions
Using SQL Functions
String Functions
Regular Expression Functions
Numeric Functions
Date Functions
Date Formats
Conversion Functions
Literal Values
Intervals
Oracle Pseudocolumns
SQL Queries - Joins
Selecting from Multiple Tables
Joining Tables
Self Joins
Outer Joins
Equijoins, Non-equijoins, and Antijoins
Aggregate Functions and Advanced Techniques
Sub-queries
Correlated Subqueries
The EXISTS Operator
The Aggregate Functions
Nulls and DISTINCT
Grouping Rows
Combining SELECT Statements
Data Manipulation & Transactions
The INSERT Statement
The UPDATE Statement
The DELETE Statement
Transaction Management
Concurrency
Explicit Locking
Data Inconsistencies
Loading Tables From External Sources
Data Definition & Control Statements
Datatypes
Defining Tables
Constraints
Inline Constraints
Modifying Table Definitions
Deleting a Table Definition
Controlling Access to Your Tables
Other Database Objects
Views
Creating Views
Updatable Views
Sequences
Indexes
Appendix A - Using Oracle SQL*Plus
SQL*Plus
The SQL Buffer
Buffer Manipulation Commands
Running SQL*Plus Scripts
Tailoring Your SQL*Plus Environment
Viewing Table Characteristics
SQL*Plus Substitution Variables
Interactive SQL*Plus Scripts
SQL*Plus LOB Support
Appendix B - The Data Dictionary
Introducing the Data Dictionary
DBA, ALL, and USER Data Dictionary Views
Some Useful Data Dictionary Queries