Get in Touch
 Duration 14 hours

Course Outline

Introduction

  • Course Overview
  • Goals and Outcomes
  • Illustrative Datasets
  • Instructional Timeline
  • Participant and Instructor Introductions
  • Required Prerequisites
  • Participant and Instructor Responsibilities

Relational Database Architecture

  • Fundamental Database Concepts
  • Principles of Relational Modeling
  • Table Structures
  • Records and Fields
  • Example Database Schema
  • Filtering Data Records
  • Supplier Record Structure
  • Sales Order Structure
  • Primary Key Identification
  • Auxiliary Indexing
  • Inter-Record Associations
  • Conceptual Comparisons
  • External Identifier Definitions
  • Foreign Key Constraints
  • Inter-Table Connection Methods
  • Data Consistency and Integrity
  • Classification of Data Associations
  • Multi-Multi Associations
  • Strategies for Resolving Multi-Multi Associations
  • One-to-One Associations
  • Finalizing Schema Design
  • Association Resolution Techniques
  • Microsoft Access Implementation
  • Entity Relationship Modeling
  • Data Modeling Processes
  • Computer-Aided Software Engineering Tools
  • Representative Diagrams
  • Relational Database Management Systems
  • Benefits of RDBMS Implementation
  • Standard Query Language Fundamentals
  • Data Definition Statements
  • Data Manipulation Statements
  • Data Access Control Statements
  • Rationale for SQL Adoption
  • Reference Materials and Distributions

Data Extraction Techniques

  • SQL Developer Environment
  • Establishing Database Connections
  • Inspecting Table Metadata
  • Applying Conditional Filters
  • Utilizing Annotation Comments
  • Handling Alphanumeric Data
  • User Accounts and Database Schemas
  • Boolean Logic Operations
  • Grouping Logical Conditions
  • Date and Time Fields
  • Processing Date Values
  • Standardizing Date Presentation
  • Valid Date Formats
  • Date Parsing Functions
  • Date Truncation Functions
  • Date Output Configuration
  • Sorting Result Sets
  • Utility Table for Query Testing
  • String Concatenation
  • Text Data Selection
  • Membership Testing Operators
  • Range Boundary Operators
  • Pattern Matching Operators
  • Frequent Syntax Errors
  • Character Case Conversion
  • String Delimiters
  • Identifying Special Characters
  • Pattern Matching Expressions
  • Regular Expression Matching
  • Missing Data Handling
  • Null Value Verification
  • Null Substitution Functions
  • Interactive Parameter Entry

Application of SQL Functions

  • Date-to-String Conversion
  • String-to-Numeric Conversion
  • Left Padding Functions
  • Right Padding Functions
  • Null Replacement Functions
  • Conditional Null Substitution
  • Unique Value Filtering
  • Substring Extraction
  • Position Locating Functions
  • Temporal Calculation Functions
  • Summary Statistics Functions
  • Record Counting
  • Data Grouping Clauses
  • Hierarchy and Cube Aggregations
  • Group Filtering Conditions
  • Aggregation with Conditional Logic
  • Value Mapping Functions
  • Conditional Expression Evaluation
  • Practical Exercise Session

Nested Queries and Set Operations

  • Single-Result Subqueries
  • Combining Distinct Result Sets
  • Combining Result Sets with Duplicates
  • Intersection and Exclusion Operations
  • Multi-Result Subqueries
  • Data Validation via Set Operations
  • Non-Matching Record Inclusion

Advanced Join Methodologies

  • Table Combination Principles
  • Full Cartesian Products
  • Matching Record Joins
  • Inferred Join Syntax
  • Defined Join Syntax
  • Automatic Column Matching
  • Equality-Based Joins
  • Comprehensive Row Pairing
  • Inclusion of Non-Matching Records
  • Left-Sided Record Preservation
  • Right-Sided Record Preservation
  • Full Record Preservation
  • Alternative Set Combination Methods
  • Join Processing Mechanisms
  • Iterative Matching Algorithm
  • Sorted Merge Algorithm
  • Hash-Based Matching Algorithm
  • Self-Referential Table Joins
  • Internal Table Relationships
  • Practical Exercise Session

Complex Query Construction

  • Row Numbering and Physical Location
  • Top-Ranked Record Analysis
  • In-Line Derived Tables
  • Existence Verification Logic
  • Dependent Nested Queries
  • Nested Queries with Functional Logic
  • Dependent Update Statements
  • Temporal State Recovery
  • Historical Data Restoration
  • Universal Quantifier Operators
  • Partial Quantifier Operators
  • Multi-Target Insertion Statements
  • Data Integration Statements

Illustrative Datasets

  • Order Management Schema
  • Media Catalog Schema
  • Human Resources Schema
  • Transactional Data Structures
  • Inventory and Catalog Structures

Operational Utility Tools

  • Definition of Database Utilities
  • Data Export Mechanisms
  • Execution Parameter Configuration
  • Configuration File Management
  • Data Import Mechanisms
  • Execution Parameter Configuration
  • Configuration File Management
  • Data Extraction Processes
  • Scheduled Batch Processing
  • Bulk Data Loading Utility
  • Utility Execution Procedures
  • Incremental Data Appending

Requirements

This course is designed for personnel with existing familiarity with SQL syntax, as well as individuals utilizing the Oracle database platform for the first time. It accommodates a range of skill levels to ensure broad accessibility within public sector teams.

Prior experience with interactive computing systems is recommended but not strictly required, allowing for diverse entry points for staff members seeking to enhance their technical capabilities.

Number of participants


Price per participant

Testimonials (7)

Upcoming Courses

Related Categories