ORACLE SQL Fundamentals Training Course

Introduction

• Overview
• Aims and Objectives
• Sample Data
• Schedule
• Introductions
• Pre-requisites
• Responsibilities

Relational Databases

• The Database
• The Relational Database
• Tables
• Rows and Columns
• Sample Database
• Selecting Rows
• Supplier Table
• Saleord Table
• Primary Key Index
• Secondary Indexes
• Relationships
• Analogy
• Foreign Key
• Foreign Key
• Joining Tables
• Referential Integrity
• Types of Relationship
• Many to Many Relationship
• Resolving a Many-to-Many Relationship
• One to One Relationship
• Completing the Design
• Resolving Relationships
• Microsoft Access - Relationships
• Entity Relationship Diagram
• Data Modelling
• CASE Tools
• Sample Diagram
• The RDBMS
• Advantages of an RDBMS
• Structured Query Language
• DDL - Data Definition Language
• DML - Data Manipulation Language
• DCL - Data Control Language
• Why Use SQL?
• Course Tables Handout

Data Retrieval

• SQL Developer
• SQL Developer - Connection
• Viewing Table Information
• Using SQL, Where Clause
• Using Comments
• Character Data
• Users and Schemas
• AND and OR Clause
• Using Brackets
• Date Fields
• Using Dates
• Formatting Dates
• Date Formats
• TO_DATE
• TRUNC
• Date Display
• Order By Clause
• DUAL Table
• Concatenation
• Selecting Text
• IN Operator
• BETWEEN Operator
• LIKE Operator
• Common Errors
• UPPER Function
• Single Quotes
• Finding Metacharacters
• Regular Expressions
• REGEXP_LIKE Operator
• Null Values
• IS NULL Operator
• NVL
• Accepting User Input

Using Functions

• TO_CHAR
• TO_NUMBER
• LPAD
• RPAD
• NVL
• NVL2 Function
• DISTINCT Option
• SUBSTR
• INSTR
• Date Functions
• Aggregate Functions
• COUNT
• Group By Clause
• Rollup and Cube Modifiers
• Having Clause
• Grouping By Functions
• DECODE
• CASE
• Workshop

Sub-Query & Union

• Single Row Sub-queries
• Union
• Union - All
• Intersect and Minus
• Multiple Row Sub-queries
• Union – Checking Data
• Outer Join

More On Joins

• Joins
• Cross Join or Cartesian Product
• Inner Join
• Implicit Join Notation
• Explicit Join Notation
• Natural Join
• Equi-Join
• Cross Join
• Outer Joins
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Using UNION
• Join Algorithms
• Nested Loop
• Merge Join
• Hash Join
• Reflexive or Self Join
• Single Table Join
• Workshop

Advanced Queries

• ROWNUM and ROWID
• Top N Analysis
• Inline View
• Exists and Not Exists
• Correlated Sub-queries
• Correlated Sub-queries with Functions
• Correlated Update
• Snapshot Recovery
• Flashback Recovery
• All
• Any and Some Operators
• Insert ALL
• Merge

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables
• The ORDER Tables
• The FILM Tables

Utilities

• What is a Utility?
• Export Utility
• Using Parameters
• Using a Parameter file
• Import Utility
• Using Parameters
• Using a Parameter file
• Unloading Data
• Batch Runs
• SQL*Loader Utility
• Running the Utility
• Appending Data