Oracle Databases

Table of Contents

Introduction To Oracle SQL

Introduction And About The Author

What Is SQL?

Relational Databases Explained - Part 1

Relational Databases Explained - Part 2

How To Query Data From A Database

The Difference Between SQL And PL/SQL

Setting Up An Oracle Apex Account

Running SQL Using Oracle Apex

Assignment 1

Assignment 1 - Solution

Basic SQL Select Statement

Retrieving All Data From A Table

Retrieving Some Data From A Table

Restricting The Retrieval Of Rows From A Table

Using Mathematical And Text Expressions

Sorting The Results Using Column Names

Assignment 2

Assignment 2 - Solution

Restricting Data - The Basics

What Is A Primary Key?

Comparison Operators

Logical Operators

Dealing With Null Values

Sorting The Results Using Column Position

Assignment 3

Assignment 3 - Solution

Joining Tables - The Basics

What Is A Foreign Key?

Inner Joins With The Using Clause

Joins With The On Clause

Joins And The Order Of Processing

Assignment 4

Assignment 4 - Solution

Summarizing Data

Aggregate Functions

The Count Functions

The Min And Max Functions

The Avg And Sum Functions

Concatenating Data

Aggregates - Order Of Processing

Assignment 5

Assignment 5 - Solution

Joining Multiple Tables

Many-To-Many Relationships - Part 1

Many-To-Many Relationships - Part 2

Join Multiple Tables With The Using Clause

Join Multiple Tables With The On Clause

Eliminating Duplicate Rows From The Results

Sorting The Results Using Column Aliases

Assignment 6

Assignment 6 - Solution

Data Subsets

Using The Group By Clause

Using The Having Clause

Having With Compound Conditions

Assignment 7

Assignment 7 - Solution

Restricting Data - Advanced Concepts

Using The In Operator

Using The Between Operator

Using The Like Operator For Pattern Matching

Outer Joins

Assignment 8

Assignment 8 - Solution

Using Subqueries - The Basics

How Do Subqueries Work?

Subqueries With The In Operator

Subqueries Using A Comparison Operator

Subqueries Vs. Joins

Using A Subquery Vs. A Self-Join

Assignment 9

Assignment 9 - Solution

Data Modeling - The Basics

How Data Is Organized In A Relational Database

The Entity-Relationship Approach

Normalization Guidelines - Part 1

Normalization Guidelines - Part 2

Assignment 10

Assignment 10 - Solution

Creating Tables And Manipulating Data

Creating Tables - Part 1

Creating Tables - Part 2

Specifying Keys

The Check Constraint

Creating Indexes

Modifying And Removing Tables

More Constraints

Adding Data To A Table

Changing Data In A Table - Part 1

Changing Data In A Table - Part 2

Removing Data From A Table

Assignment 11

Assignment 11 - Solution

Single-Row Functions

String Functions - Part 1

String Functions - Part 2

Number Functions

Date Functions

Limiting Rows And The Dual Table

Assignment 12

Assignment 12 - Solution

Creating And Using Views

Creating And Using Views

Set Operators

Assignment 13

Assignment 13 - Solution

Database Management

Transaction Management

Concurrency Control

Security Considerations

Stored Procedures And Triggers

Database Integrity

Assignment 14

Assignment 14 - Solution

Legacy SQL Techniques

Implicit Joins

Subqueries With Modifiers

Subqueries Using The Exists Clause

Assignment 15

Assignment 15 - Solution

Conclusion

Difference Between Oracle And Other SQLs

SQL In A Programming Language

Embedded SQL

What Is Next?

Introduction

What You Should Expect From This Course

About The Author

What is Oracle?

The Oracle Technology Family

Hardware

Operating Systems

Virtualization

Middleware And Java

Oracle Database Technology

What Is A Database?

Oracle RDBMS

Oracle Database Server Editions

Oracle Database Server Versioning

Oracle Database Options

Software

Documentation

SQL Developer

Database Architecture

The Oracle RDBMS - Instance And Database

The Instance - Background Memory Processes

The Instance - System Global Area

The Instance - Program Global Area

The Database - Core Datafiles

The Redo Architecture

Real Application Clusters

Container Databases

Pluggable Databases

Installing The Oracle Software

Planning An Installation

The Oracle Universal Installer

Installing The Software - Part 1

Installing The Software - Part 2

Creating An Oracle Database

Planning Database Creation

Running NETCA And DBCA

Stepping Through Database Creation - Part 1

Stepping Through Database Creation - Part 2

Stepping Through Database Creation - Part 3

Setting Up The SQL Developer

Structured Query Language (SQL)

What Is SQL?

Select

Insert

Update

Deletes

Joins

Single Row Functions

Data Aggregation

Optimizer And Execution Plans

Transactions And Concurrency

PL And SQL

What Is PL And SQL?

Anonymous Blocks

Procedures

Functions

Packages

Object Types

Oracle Storage Structures

Blocks

Extents

Segments

Datafiles

Smallfile Tablespaces

Bigfile Tablespaces

Automatic Storage Management - ASM

Database Objects

Tables

Partitions

Temporary Tables

B-tree Indexes

Bitmap Indexes

Index Organized Tables

Function Based Indexes

Views

Primary And Unique Keys

Foreign Keys

Check Constraints

Synonyms

Sequences

Database Security

Principles Of Database Security

Oracle Schema Based Approach

Creating And Dropping Users

System Privileges

Object Privileges

Roles

Profiles

Auditing

Database Administrative Tasks

Instance Startup

Instance Shutdown

Parameter File

Modifying Parameters

The Data Dictionary

Password File

ADRCI

Alert Log And Trace Files

Memory Management

Monitoring Sessions

Monitoring Space Usage

Database Backup And Recovery

Backup And Recovery Concepts

RMAN Backups

RMAN Incremental Backups

RMAN Complete Recovery

RMAN Incomplete Recovery

RMAN Backup Maintenance

Data Migration

SQL Loader

External Tables

Data Pump Export

Data Pump Import

Oracle Networking

Understanding Oracle Connections

Networking Configuration Files

Listener Control

Conclusion

Wrap-Up And Review

Introduction/The Basics

Course Introduction

About The Author

Block Structure

Scalar Variables

Rowtype Variables

Introduction To Tables

Index By Tables

Nested Tables

Varrays

Loops

Control Structures

Errors

Error Introduction

Predefined Exceptions

Exception Handlers

User Defined Exceptions

Data Manipulation

Query Introduction

Cursors

Ref Cursors

Nested Cursors

Cursor Loops

Bulk Fetch

Inserting Data

Bulk Inserts

Insert Returning

Insert Save Exceptions

Updating Data

Bulk Updates

Update Returning

Update Save Exceptions

Deleting Data

Bulk Deletes

Delete Returning

Delete Save Exceptions

Merge

Database Procedure

Procedures

Calling Procedures

Packages

Package Specification

Package Bodies

Package Private And Public

Calling Packages

Triggers

DML Triggers

Trigger Ordering

Compound Triggers

DDL Triggers

Instead Of Triggers

Event Triggers

After Suspend Triggers

Functions

Functions

Result Cache Functions

Table Functions

Pipelined Functions

Parallel Table Functions

Streaming Table Functions

Code Compilation

PLSQL Compilation

Inquiry Directives

Conditional Compilation

PLSQL Optimize

Code Assessment

PLSQL Warnings

PLSQL Profiler

PLSQL Trace

PLSQL Scope

Security

Invoker Rights

Security Roles

Accessible By

Bequeath View

Inherit Privileges

Secure Roles

Dynamic SQL

Dynamic SQL

Dynamic SQL Parameters

Dynamic SQL Returning

Data Definition Language

DDL Create

DDL Modify

DDL Drop

Tansactions

Transactions

Read Only Transactions

Serializable Transactions

Autonomous Transactions

Savepoints

PLSQL In SQL

PLSQL In SQL

Wrap Up

Wrap Up

Apex - Installation And Administration

Introduction And About The Author

What Is Oracle Application Express?

The Apex Architecture

Downloading And Installation

High-Level Overview Of Apex Pages

Apex Hierarchy - Workspaces, Applications, Pages, Regions And Items

Creating A Workspace

Creating Workspace Users

Setting Workspace-Level Attributes

Setting Security Attributes

Apex Basics - Applications, Pages And Regions

Types Of Apex Applications - Part 1

Types Of Apex Applications - Part 2

Creating A New Application - Part 1

Creating A New Application - Part 2

Understanding Pages And Regions

Using The Wizards - Part 1

Using The Wizards - Part 2

After The Wizards - Part 1

After The Wizards - Part 2

Page Rendering - Part 1

Page Rendering - Part 2

Page Rendering - Part 3

Apex Regions - Forms, Reports, Charts And More

Forms - Part 1

Forms - Part 2

Forms - Part 3

Forms - Part 4

Forms - Part 5

Forms - Part 6

Reports - Part 1

Reports - Part 2

Reports - Part 3

Reports - Part 4

Reports - Part 5

Charts - Part 1

Charts - Part 2

Maps

Trees

Calendars

Other Types

Shared Components - Part 1

Shared Components - Part 2

Dynamic Actions

Tabs

Linking To Other Pages - Part 1

Linking To Other Pages - Part 2

User Interface, Templates And Themes

What Is A Template?

What Is A Theme?

Applying Themes And Templates To Your Application

Making Changes To A Template

Allowing Users To Make Their Own Changes

Creating Mobile Applications

Mobile Vs. Desktop

The Apex Mobile Theme - Part 1

The Apex Mobile Theme - Part 2

ThemeRoller And PhoneGap

Apex Advanced Topics - Security, Performance And Team Development

Authentication Schemes

Other Security Features

Debugging And Tuning Your Application For Performance

Team Development Features

The Apex Listener

Web Services

Conclusion

Wrap-Up