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.
Testimonials (7)
Greg was very patient and helpful
Chris Havel - Encyclopaedia Britannica
Course - ORACLE SQL Fundamentals
Theory was explained very well
Sven - LGT Financial Services AG
Course - ORACLE SQL Fundamentals
I liked the split screen database portal that we worked off of and saw where on the course we were so I can go back to retry the exercises. He was great to learn from - he was engaging and encouraging. I appreciate the training being in my time zone while my trainer is 7 hrs ahead.
Olivia Button - Encyclopaedia Britannica
Course - ORACLE SQL Fundamentals
it was very informative
Metuatini (aka) Metua - Ministry of Justice
Course - ORACLE SQL Fundamentals
- Learning about SQL and different types of Data bases. - Creating tables with authors and then creating the books and then connecting the information and using those for the sql queries we had - Enjoyed the different scenarios that we could apply certain sql queries. I enjoyed learning about the different 'Joins', calculating average salaries for certain employees as well as many other different sql queries to find out specific information. - The training set up was user friendly and if we had issues on our desktops, Jose was able to remote in and see the issue and resolve.
Frank - Ministry of Justice
Course - ORACLE SQL Fundamentals
The way he explain the topic with reference from previous topics and its important applications.
Ferdinand - National Grid Corporation of the Philippines
Course - ORACLE SQL Fundamentals
Luka is an excellent, patient teacher with a sense of humor. His relaxed style made the stressful experience of "be called to the blackboard" more pleasant. Also one student explaining or guiding the other was a very good idea. I will use the motto "KISS methodology" he shared with us in both my SQL exercises , private and professional life since I like to overcomplicate things. Luka also kept the good pace considering how much material was there for him to show and for us to learn.