ORACLE SQL Fundamentals Training Course

Introduction

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

Relational Databases

• Understanding the Database
• The Relational Database Model
• Concept of Tables
• Rows and Columns
• Overview of the Sample Database
• Retrieving Specific Rows
• The Supplier Table
• The Saleord Table
• Primary Key Indexes
• Secondary Indexes
• Data Relationships
• Practical Analogies
• Foreign Keys (I)
• Foreign Keys (II)
• Techniques for Joining Tables
• Maintaining Referential Integrity
• Types of Data Relationships
• Many-to-Many Relationships
• Strategies for Resolving Many-to-Many Relationships
• One-to-One Relationships
• Finalizing the Database Design
• Methods for Resolving Relationships
• Microsoft Access - Relationship Concepts
• Entity Relationship Diagrams (ERD)
• Principles of Data Modelling
• Computer-Aided Software Engineering (CASE) Tools
• Example Diagram
• The Relational Database Management System (RDBMS)
• Benefits of Using an RDBMS
• Introduction to Structured Query Language (SQL)
• DDL - Data Definition Language
• DML - Data Manipulation Language
• DCL - Data Control Language
• The Advantages of SQL
• Course Tables Reference Handout

Data Retrieval

• Getting Started with SQL Developer
• SQL Developer - Establishing Connections
• Inspecting Table Metadata
• Filtering Data with the WHERE Clause
• Best Practices for Using Comments
• Handling Character Data
• Users and Schemas
• Logical Operators: AND and OR
• Proper Use of Parentheses
• Working with Date Fields
• Querying by Date
• Date Formatting Techniques
• Common Date Format Models
• The TO_DATE Function
• The TRUNC Function
• Displaying Dates Effectively
• Sorting Results with the ORDER BY Clause
• The DUAL Table
• String Concatenation
• Selecting Text Values
• The IN Operator
• The BETWEEN Operator
• Pattern Matching with the LIKE Operator
• Identifying Common Syntax Errors
• The UPPER Function
• Correct Use of Single Quotes
• Locating Metacharacters
• Introduction to Regular Expressions
• The REGEXP_LIKE Operator
• Handling Null Values
• The IS NULL Operator
• The NVL Function
• Capturing User Input

Using Functions

• The TO_CHAR Function
• The TO_NUMBER Function
• Padding with LPAD
• Padding with RPAD
• Handling Nulls with NVL
• Conditional Logic with NVL2
• Removing Duplicates with DISTINCT
• Extracting Substrings with SUBSTR
• Finding Substrings with INSTR
• Essential Date Functions
• Introduction to Aggregate Functions
• The COUNT Function
• Grouping Data with GROUP BY
• Using Rollup and Cube Modifiers
• Filtering Groups with the HAVING Clause
• Grouping by Function Results
• Conditional Logic with DECODE
• Conditional Logic with CASE
• Hands-on Workshop

Sub-Query & Union

• Single-Row Sub-queries
• Combining Results with Union
• Union with Duplicates (Union All)
• Set Operations: Intersect and Minus
• Multi-Row Sub-queries
• Using Union for Data Validation
• Introduction to Outer Joins

More On Joins

• Fundamental Join Concepts
• 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
• Alternative Approaches Using UNION
• How Join Algorithms Work
• Nested Loop Join
• Merge Join
• Hash Join
• Reflexive or Self Joins
• Joining a Single Table to Itself
• Hands-on Workshop

Advanced Queries

• Using ROWNUM and ROWID
• Top N Analysis Techniques
• Inline Views
• Verification with Exists and Not Exists
• Correlated Sub-queries
• Correlated Sub-queries Incorporating Functions
• Performing Correlated Updates
• Snapshot Recovery Methods
• Flashback Recovery Techniques
• The ALL Operator
• The ANY and SOME Operators
• Multi-Table Inserts with INSERT ALL
• The Merge Statement

Sample Data

• Structure of ORDER Tables
• Structure of FILM Tables
• Structure of EMPLOYEE Tables
• Detailed Look at ORDER Tables
• Detailed Look at FILM Tables

Utilities

• Definition of Database Utilities
• The Export Utility
• Using Command-Line Parameters
• Using Parameter Files
• The Import Utility
• Managing Import Parameters
• Utilizing Import Parameter Files
• Techniques for Unloading Data
• Scheduling Batch Runs
• The SQL*Loader Utility
• Executing the SQL*Loader Utility
• Strategies for Appending Data