ORACLE SQL Fundamentals Training Course

Introduction

• Overview of the Course
• Aims and Objectives for Government
• Sample Data for Practical Application
• Schedule for Effective Learning
• Introductions to Key Concepts
• Pre-requisites for Participants
• Responsibilities of Attendees and Instructors

Relational Databases

• Understanding the Database Structure
• The Concept of a Relational Database for Government
• Tables and Their Importance
• Rows and Columns in Databases
• Sample Database for Practical Exercises
• Selecting Specific Rows from a Table
• Supplier Table Example
• Saleord Table Explanation
• Primary Key Index for Data Integrity
• Secondary Indexes for Efficient Querying
• Relationships Between Tables in a Database
• Analogy to Understand Relationships
• Foreign Key Constraint for Referential Integrity
• Joining Multiple Tables for Comprehensive Data Retrieval
• Referential Integrity in Database Design
• Types of Relationships in Databases
• Many-to-Many Relationships and Their Resolution
• One-to-One Relationships for Specific Use Cases
• Completing the Database Design Process
• Resolving Complex Relationships in Databases
• Microsoft Access - Managing Relationships
• Entity Relationship Diagram for Visualizing Data
• Data Modeling Techniques for Government
• CASE Tools for Advanced Database Design
• Sample Diagram to Illustrate Concepts
• The Relational Database Management System (RDBMS)
• Advantages of Using an RDBMS in Government Operations
• Structured Query Language (SQL) for Data Manipulation
• DDL - Data Definition Language for Schema Management
• DML - Data Manipulation Language for Data Operations
• DCL - Data Control Language for Security and Access
• Reasons to Use SQL in Government Applications
• Course Tables Handout for Reference

Data Retrieval

• Introduction to SQL Developer Tool
• Establishing a Connection in SQL Developer
• Viewing Table Information and Metadata
• Using the WHERE Clause for Conditional Selection
• Adding Comments to SQL Code for Clarity
• Handling Character Data in Queries
• Understanding Users and Schemas in Databases
• Using AND and OR Logical Operators
• Utilizing Brackets for Complex Conditions
• Working with Date Fields in SQL
• Formatting Dates for Readability
• Specifying Date Formats in Queries
• Using the TO_DATE Function for Conversion
• TRUNC Function for Date Truncation
• Displaying Dates in SQL Results
• ORDER BY Clause for Sorting Data
• DUAL Table for Single-Row Functions
• Concatenation of Strings in SQL
• Selecting Text from a Database
• IN Operator for Multiple Values
• BETWEEN Operator for Range Selection
• LIKE Operator for Pattern Matching
• Common Errors in SQL Queries
• Using the UPPER Function for Case Insensitivity
• Handling Single Quotes in SQL Strings
• Finding Metacharacters in Data
• Regular Expressions in SQL Queries
• REGEXP_LIKE Operator for Advanced Pattern Matching
• Handling Null Values in Databases
• IS NULL Operator for Checking Nulls
• NVL Function for Default Values
• Accepting User Input in SQL Queries

Using Functions

• TO_CHAR Function for Data Conversion
• TO_NUMBER Function for Numeric Conversion
• LPAD Function for Left Padding Strings
• RPAD Function for Right Padding Strings
• NVL Function for Handling Nulls in Calculations
• NVL2 Function for Conditional Values
• DISTINCT Option for Unique Results
• SUBSTR Function for Extracting Substrings
• INSTR Function for Finding Substring Positions
• Date Functions in SQL Queries
• Aggregate Functions for Summarizing Data
• COUNT Function for Row Counting
• GROUP BY Clause for Grouping Data
• Rollup and Cube Modifiers for Hierarchical Aggregation
• HAVING Clause for Filtering Groups
• Grouping by Functions in SQL Queries
• DECODE Function for Conditional Values
• CASE Statement for Complex Conditions
• Workshop to Apply Learned Concepts

Sub-Query & Union

• Single Row Sub-queries for Specific Data
• UNION Operator for Combining Results
• UNION ALL for Including Duplicates
• INTERSECT and MINUS Operators for Set Operations
• Multiple Row Sub-queries for Complex Conditions
• Union - Checking Data Consistency
• Outer Join for Inclusive Results

More On Joins

• Types of Joins in SQL Queries
• Cross Join or Cartesian Product for All Combinations
• Inner Join for Matching Rows
• Implicit Join Notation for Simplified Syntax
• Explicit Join Notation for Clarity
• Natural Join for Automatic Matching
• Equi-Join for Equal Conditions
• Cross Join for All Combinations (Revisited)
• Outer Joins for Incomplete Matches
• Left Outer Join for Preserving Left Table Rows
• Right Outer Join for Preserving Right Table Rows
• Full Outer Join for Combining All Rows
• Using UNION to Combine Results
• Join Algorithms for Performance Optimization
• Nested Loop Join for Small Datasets
• Merge Join for Sorted Data
• Hash Join for Large Datasets
• Reflexive or Self Join for Single Table Operations
• Single Table Join for Simplified Queries
• Workshop to Practice Joins and Set Operations

Advanced Queries

• ROWNUM and ROWID for Row Identification
• Top N Analysis for Ranking Data
• Inline View for Subquery Results
• EXISTS and NOT EXISTS for Conditional Queries
• Correlated Sub-queries for Related Data
• Using Functions in Correlated Sub-queries
• Correlated Update for Data Modification
• Snapshot Recovery for Data Integrity
• Flashback Recovery for Historical Data
• ALL Operator for Universal Conditions
• ANY and SOME Operators for Multiple Values
• Insert ALL for Batch Inserts
• Merge Statement for Conditional Updates and Inserts

Sample Data

• ORDER Tables for Practical Examples
• FILM Tables for Media Data
• EMPLOYEE Tables for Personnel Records
• Detailed Information on the ORDER Tables
• Detailed Information on the FILM Tables

Utilities

• Definition and Importance of Database Utilities
• Export Utility for Data Backup and Transfer
• Using Parameters in the Export Process
• Utilizing a Parameter File for Customization
• Import Utility for Restoring Data
• Using Parameters in the Import Process
• Utilizing a Parameter File for Import Customization
• Unloading Data for Migration and Backup
• Batch Runs for Automated Processing
• SQL*Loader Utility for Bulk Data Loading
• Running the SQL*Loader Utility
• Appending Data to Existing Tables