ORACLE SQL Fundamentals Training Course

Introduction

• Course Overview
• Learning Objectives and Aims
• Sample Data Introduction
• Schedule Details
• Participant Introductions
• Prerequisites Review
• Participant Responsibilities

Relational Databases

• Database Concepts
• Relational Database Structure
• Understanding Tables
• Rows and Columns
• The Sample Database
• Row Selection
• The Supplier Table
• The Saleord Table
• Primary Key Indexes
• Secondary Indexes
• Data Relationships
• Conceptual Analogy
• Foreign Keys (Part 1)
• Foreign Keys (Part 2)
• Table Joins
• Referential Integrity
• Relationship Types
• Many-to-Many Relationships
• Resolving Many-to-Many Relationships
• One-to-One Relationships
• Design Completion
• Relationship Resolution Strategies
• Microsoft Access Relationship Management
• Entity-Relationship Diagrams
• Data Modelling Principles
• CASE Tools
• Sample Diagram Analysis
• The RDBMS Architecture
• Benefits of RDBMS
• Structured Query Language (SQL) Overview
• DDL - Data Definition Language
• DML - Data Manipulation Language
• DCL - Data Control Language
• The Advantages of SQL
• Course Tables Handout

Data Retrieval

• SQL Developer Interface
• Establishing Connections in SQL Developer
• Inspecting Table Metadata
• Utilizing the WHERE Clause
• Commenting Code
• Handling Character Data
• Users and Schemas
• Logical Operators: AND and OR
• Using Parentheses for Clarity
• Working with Date Fields
• Date Handling Techniques
• Date Formatting Standards
• Specific Date Formats
• TO_DATE Function
• TRUNC Function
• Displaying Dates
• Sorting with ORDER BY
• The DUAL Table
• String Concatenation
• Selecting Textual Data
• The IN Operator
• The BETWEEN Operator
• The LIKE Operator
• Common Pitfalls and Errors
• UPPER Function Usage
• Quoting Rules
• Identifying Metacharacters
• Introduction to Regular Expressions
• REGEXP_LIKE Operator
• Handling Null Values
• IS NULL Checks
• NVL Function
• Capturing User Input

Function Usage

• TO_CHAR Function
• TO_NUMBER Function
• LPAD Function
• RPAD Function
• NVL Function Review
• NVL2 Function
• The DISTINCT Keyword
• SUBSTR Function
• INSTR Function
• Date Manipulation Functions
• Aggregate Functions Overview
• COUNT Function
• The GROUP BY Clause
• ROLLUP and CUBE Modifiers
• The HAVING Clause
• Grouping by Functions
• DECODE Function
• CASE Expressions
• Practical Workshop

Sub-Queries & Unions

• Single-Row Sub-Queries
• UNION Operation
• UNION ALL Operation
• INTERSECT and MINUS Operators
• Multi-Row Sub-Queries
• Using UNION for Data Validation
• Outer Joins Introduction

Advanced Joins

• Join Fundamentals
• Cross Joins and Cartesian Products
• Inner Joins
• Implicit Join Syntax
• Explicit Join Syntax
• Natural Joins
• Equi-Joins
• Cross Joins Review
• Overview of Outer Joins
• Left Outer Joins
• Right Outer Joins
• Full Outer Joins
• Using UNION in Join Contexts
• Join Algorithms Explained
• Nested Loop Join
• Merge Join
• Hash Join
• Reflexive (Self) Joins
• Single Table Join Scenarios
• Practical Workshop

Advanced Query Techniques

• ROWNUM and ROWID
• Top N Analysis
• Inline Views
• EXISTS and NOT EXISTS
• Correlated Sub-Queries
• Correlated Sub-Queries with Functions
• Correlated Updates
• Snapshot Recovery
• Flashback Recovery
• The ALL Keyword
• ANY and SOME Operators
• INSERT ALL Statement
• MERGE Statement

Sample Datasets

• ORDER Table Structure
• FILM Table Structure
• EMPLOYEE Table Structure
• Detailed Order Data
• Detailed Film Data

Utility Tools

• Defining Database Utilities
• Export Utility Overview
• Parameter Configuration
• Using Parameter Files
• Import Utility Overview
• Parameter Configuration for Import
• Using Parameter Files for Import
• Data Unloading Procedures
• Batch Processing Runs
• SQL*Loader Utility
• Executing Utilities
• Appending Data