ORACLE SQL Fundamentals Training Course

Introduction

• Course Overview
• Learning Objectives and Goals
• Sample Data Set
• Daily Schedule
• Participant Introductions
• Prerequisites Review
• Roles and Responsibilities

Relational Databases

• Database Concepts
• The Relational Model
• Understanding Tables
• Rows and Columns Explained
• Sample Database Walkthrough
• Filtering Rows
• Supplier Table Example
• Saleord Table Example
• Primary Key Indexes
• Secondary Indexes
• Data Relationships
• Conceptual Analogies
• Foreign Keys
• Foreign Key Constraints
• Connecting Tables
• Maintaining Referential Integrity
• Classifying Relationships
• Many-to-Many Relationships
• Resolving Many-to-Many Constraints
• One-to-One Relationships
• Finalizing the Design
• Managing Relationship Resolutions
• Microsoft Access Relationship Views
• Entity Relationship Diagrams
• Data Modelling Principles
• Computer-Aided Software Engineering (CASE) Tools
• Sample ER Diagram
• The Relational DBMS (RDBMS)
• Benefits of Using an RDBMS
• Introduction to Structured Query Language (SQL)
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Rationale for Using SQL
• Course Tables Handout

Data Retrieval

• SQL Developer Interface
• Establishing SQL Developer Connections
• Inspecting Table Metadata
• Applying the WHERE Clause
• Best Practices for Commenting
• Handling Character Data
• Users and Schema Management
• Combining Conditions with AND and OR
• Logical Grouping with Brackets
• Working with Date Fields
• Manipulating Dates
• Customizing Date Formats
• Date Format Specifications
• The TO_DATE Function
• Truncating with TRUNC
• Displaying Date Values
• Sorting with the ORDER BY Clause
• Utilizing the DUAL Table
• String Concatenation
• Selecting Text Data
• The IN Operator
• The BETWEEN Operator
• The LIKE Operator
• Common Query Errors
• The UPPER Function
• Quote Handling
• Identifying Metacharacters
• Introduction to Regular Expressions
• The REGEXP_LIKE Operator
• Handling Null Values
• Checking for Nulls with IS NULL
• The NVL Function
• Prompting for User Input

Using Functions

• Converting to Character with TO_CHAR
• Converting to Number with TO_NUMBER
• Left Padding with LPAD
• Right Padding with RPAD
• Null Replacement with NVL
• Advanced Null Handling with NVL2
• Removing Duplicates with DISTINCT
• Substring Extraction with SUBSTR
• Finding Positions with INSTR
• Manipulating Date Values
• Overview of Aggregate Functions
• Counting Records with COUNT
• Grouping Results with GROUP BY
• Hierarchical Aggregation with Rollup and Cube
• Filtering Groups with HAVING
• Grouping by Function Results
• Conditional Logic with DECODE
• Complex Conditionals with CASE
• Practical Workshop Exercise

Sub-Query & Union

• Single-Row Sub-queries
• Combining Results with Union
• Including Duplicates with Union - All
• Finding Common and Unique Data with Intersect and Minus
• Multi-Row Sub-queries
• Validating Data with Union
• Introduction to Outer Joins

More On Joins

• Joining Concepts Overview
• Cross Joins and Cartesian Products
• Filtering with Inner Joins
• Implicit Join Syntax
• Explicit Join Syntax
• Automatic Matching with Natural Joins
• Matching on Equality with Equi-Joins
• Comprehensive Cross Joins
• Overview of Outer Joins
• Preserving Left Table Data with Left Outer Joins
• Preserving Right Table Data with Right Outer Joins
• Preserving Both Tables with Full Outer Joins
• Alternative Methods Using UNION
• Understanding Join Algorithms
• Row-by-Row Matching with Nested Loop
• Sorting and Merging with Merge Join
• Efficient Matching with Hash Join
• Joining a Table to Itself (Reflexive/Self Join)
• Single-Table Join Scenarios
• Hands-On Join Workshop

Advanced Queries

• Row Identification with ROWNUM and ROWID
• Finding Top N Records
• Creating Derived Tables with Inline Views
• Verifying Existence with Exists and Not Exists
• Nested Logic with Correlated Sub-queries
• Correlated Sub-queries Involving Functions
• Updating Based on Related Data (Correlated Update)
• Historical Data Access via Snapshot Recovery
• Point-in-Time Recovery via Flashback
• Universal Quantification with All
• Existential Quantification with Any and Some
• Multi-Table Inserts with Insert ALL
• Updating/Inserting with Merge

Sample Data

• Order-Related Tables
• Film-Related Tables
• Employee-Related Tables
• Detailed Order Table Structure
• Detailed Film Table Structure

Utilities

• Defining Database Utilities
• Data Extraction with Export Utility
• Configuring Export via Parameters
• Managing Export Settings with Parameter Files
• Data Ingestion with Import Utility
• Configuring Import via Parameters
• Managing Import Settings with Parameter Files
• Writing Data to Flat Files (Unloading)
• Automating Processes with Batch Runs
• High-Volume Data Loading with SQL*Loader
• Executing the Loading Process
• Adding New Data to Existing Tables (Appending)