• Understand SQL coding techniques
• Employ appropriate database relationships and table joins
• Use SQL developer tools
• Manage SQL data
• Create suitable queries
• Manage tables and their structure
• Manage views, sequences and indexes
• Control access to data

Introduction to Relational Data Storage

Introduce the Basic Concepts of Relational Theory

Introduce the Basic Concepts of Data Modelling

Describe how relational theory is implemented in a Modern RDBM:

- Entities and Tables

- Attributes and Columns

- Tuples and Rows

- Relationships and Keys

Describe SQL as a Language

Provide an Overview of Oracle's Main Architectural components

Describe how Oracle implements SQL and allows users to Interact with the Data

SqlPlus and SQL Developer Tools

Connecting via the SqlPlus Interface

SQL commands versus SqlPlus commands

Using scripts with SQLPlus

Connecting via the SQL Developer

Opening a SQLDeveloper worksheet

Running command in SQLDeveloper

Running scripts in SQLDeveloper

Writing SQL SELECT statements

Define and explain the terms: projection, selection, and join

The Basic SQL SELECT statement

Selecting single and multiple columns

Statements containing arithmetic operators

Operator Precedence

Using Literal strings

Quotes and the quote operator

The use of column aliases

The concatenation operator

The Null character; its definition and usage

Interpreting Nulls in various expressions

Restricting and Sorting Data

Limiting Rows During a Selection

Using the WHERE Clause

Explain the main Comparison Operators

Using the LIKE Operator to Compare Literal Values

Explain the Logical Operators AND, OR, NOT

Using Multiple Conditions in the WHERE clause

Describe the Rules of Precedence

The ORDER BY Clause

Substitution Variables

The && Substitution Variable

The DEFINE Command

The VERIFY Command

Using Oracle's Built-In SQL Functions

Explain Single Row Functions

Case and Character Manipulation Functions

Numeric Manipulation Functions; MOD, ROUND and TRUNC

Working with Dates

Date Formatting and Conversion Functions

Date Manipulation Functions

Arithmetical Operation on Dates

Data Type Conversion, Explicit and Implicit

Conditional Operators; CASE, DECODE

Multi-Row Functions

Explain the various Types of Group Functions

Using COUNT, SUM, MAX, MIN and AVG Functions

Using the DISTINCT Operator

Explain the interpretation of Nulls by Group Functions

Using the GROUP BY Clause

Group Data by multiple columns

Restrictions on the use of the GROUP BY clause

Restricting the results of a Group Function using the HAVING Clause

Accessing Data Dispersed Across Multiple Tables

Joining Multiple Tables

Selecting Across Multiple Tables (FROM Clause)

Restrictions Across Multiple Tables (WHERE Clause)

Cartesian Joins

Natural Joins

 Inner Joins

Out Joins; Left, Right and Full

Self Joins

The Use of Aliases to Simplify Statements

The USING clause

The ON clause

Using Subqueries

Why use a subquery?

Where to place a subquery in a statement

Single Row and Multiple Row subqueries

Using Subquery Operators

Sub queries and Grouping Functions

Restrictions on Subqueries

Handling Null in Subqueries

Set Operators

The UNION operator

The UNION ALL operator

The INTERSECT operator

The MINUS operator

Guidelines when using SET operators

Data Manipulation

Inserting Data; the INSERT command

Inserting Date and Time Values

Inserting Null Values

Inserting Data via a Select Statement

Changing Data: the UPDATE command

Updating Rows in a Table

Updating Rows based on a Select Statement

Updating a Single Column

Updating Multiple Columns

Deleting Rows from a Table

The DELETE Statement

Deleting Rows based on a Select Statement

Deleting All Rows in a Table

The TRUNCATE Command

What is a Transaction?

Controlling Transactions with Transaction Control Statements

The COMMIT Command

The ROLLBACK Command

The SAVEPOINT Command

Implicit Transaction Control Statements

Viewing Committed and Uncommitted Data

Managing Tables

Identify the Naming Rules

Creating Tables

Data Types

Alter Table Command

Drop Table Command

Adding a Column

Modifying a Column

Dropping a Column, Set Column UNUSED

External Tables

Managing Constraints

Types of Constraints

Creating Constraints

Adding, Enabling and Disabling Constraints

Managing Views, Sequences and Indexes

Creating views

Accessing Data Via Views

Read-only views

Performing DML on Views

Creating Sequences

Creating indexes

Creating Function-Based Indexes

Create a synonym

Accessing Meta-Data via Data Dictionary Views

Types of Dictionary Views Available

How to Find the Relevant Dictionary Views

List Commonly Used Dictionary Views

Writing Queries to Gather Schema Information

Adding Comments to Objects with the COMMENT command

Controlling Data Access Controlling Access

System Privileges and Objects Privileges.

Granting Object Privileges

Revoking Object Privileges

Managing Roles

Grouping Privileges with Roles

Managing Passwords

Manipulating Large Data Sets

Manipulating Data with subqueries

Inserting Rows from another Table

Multi Column Updates via a Subquery

Deleting Data via a Subquery

The CHECK option

Multi-table INSERT Statements

Unconditional Inserts

Conditional Inserts

Pivoting Inserts

The MERGE Statement

Aggregating Data

The GROUP BY Clause

The Having Clause

The ROLLUP Operator

The CUBE Operator

Grouping Function

Grouping Sets

Composite Columns

Concatenated Groupings

Managing Data in Different Time Zones

Managing DATE and TIMESTAMP Data

Defining Time Zones

Session Parameters

Date and Time functions

The TIMESTAMP Data Types

Local Time Zones

Translating between Time Zones

The INTERVAL Data Types

Daylight Saving

Advanced Subqueries

Multi Column Sub queries

Comparing Columns

Comparison of Paired Data

Scalar Expressions

Correlated Subqueries

The EXISTS Operator

Correlated DML

The WITH Clause

Hierarchical Retrieval

Natural Tree Walk

Hierarchical Queries

Tree Walking; Bottom to Top

Tree Walking; Top to Bottom

Ranking Rows using the LEVEL Pseudo column

Using LEVEL and LPAD

Pruning Using the WHERE and CONNECT BY clauses

Regular Expression Support

Introduction to Regular Expression

Meta Characters

Regular Expression Functions

REGEXP Syntax

Searches with Regular Expressions

Replacing Sub-strings

Check Constraints and Regular Expressions