Course Outline
Introduction
- Overview of Program Scope
- Program Aims and Objectives
- Illustrative Data Sets
- Instructional Schedule
- Participant Introductions
- Prerequisites for Participation
- Role Responsibilities
Relational Databases
- Database Fundamentals
- Principles of Relational Databases
- Table Structures
- Rows and Columns
- Example Database Configuration
- Querying Records
- Supplier Data Table
- Sales Order Data Table
- Primary Key Indexing
- Secondary Indexes
- Data Relationships
- Conceptual Analogies
- Foreign Key Constraints
- Foreign Key Constraints
- Table Join Operations
- Referential Integrity
- Relationship Classifications
- Many-to-Many Relationships
- Resolution Strategies for Many-to-Many Relationships
- One-to-One Relationships
- Finalizing Database Design
- Managing Complex Relationships
- Microsoft Access Relationship Management
- Entity-Relationship Diagrams
- Data Modeling Practices
- Computer-Aided Software Engineering (CASE) Tools
- Illustrative Diagram Examples
- Relational Database Management Systems (RDBMS)
- Benefits of RDBMS Implementation
- Structured Query Language (SQL)
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Strategic Advantages of SQL Usage
- Instructional Reference Materials
Data Retrieval
- SQL Developer Environment
- Establishing SQL Developer Connections
- Inspecting Table Metadata
- Filtering Records with Where Clauses
- Utilizing Code Comments
- Handling Character Data
- User Accounts and Schemas
- Logical AND and OR Operators
- Expression Grouping with Brackets
- Date Field Management
- Processing Date Values
- Date Output Formatting
- Standard Date Formats
- TO_DATE Functionality
- TRUNC Functionality
- Date Presentation Standards
- Sorting Results with Order By
- DUAL Table Usage
- String Concatenation
- Selecting Text Fields
- IN Operator Application
- BETWEEN Operator Application
- LIKE Operator Application
- Common Error Identification
- UPPER Function Application
- Handling Single Quotes
- Identifying Metacharacters
- Regular Expressions
- REGEXP_LIKE Operator Application
- Management of Null Values
- IS NULL Operator Application
- NVL Function Application
- Handling User-Defined Input
Using Functions
- TO_CHAR Function
- TO_NUMBER Function
- LPAD Function
- RPAD Function
- NVL Function
- NVL2 Function
- DISTINCT Clause Option
- SUBSTR Function
- INSTR Function
- Date Manipulation Functions
- Aggregate Function Usage
- COUNT Function
- Group By Clause
- Rollup and Cube Modifiers
- Having Clause Application
- Grouping with Functions
- DECODE Function
- CASE Expression
- Practical Workshop Exercises
Sub-Query & Union
- Single-Row Sub-Queries
- Union Operations
- Union-All Operations
- Intersect and Minus Operations
- Multi-Row Sub-Queries
- Union for Data Validation
- Outer Join Operations
More On Joins
- Join Fundamentals
- Cross Join and Cartesian Products
- Inner Join Operations
- Implicit Join Syntax
- Explicit Join Syntax
- Natural Join Operations
- Equi-Join Operations
- Cross Join Operations
- Outer Join Operations
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Integration Using UNION
- Join Execution Algorithms
- Nested Loop Method
- Merge Join Method
- Hash Join Method
- Reflexive or Self-Joins
- Single-Table Joins
- Practical Workshop Exercises
Advanced Queries
- ROWNUM and ROWID Usage
- Top N Analysis Techniques
- Inline View Construction
- Exists and Not Exists Checks
- Correlated Sub-Queries
- Correlated Sub-Queries with Functions
- Correlated Update Statements
- Snapshot Recovery Processes
- Flashback Recovery Processes
- Universal Quantifier (All)
- Any and Some Operators
- Multi-Table Insert ALL
- Merge Statement Execution
Sample Data
- ORDER Table Structures
- FILM Table Structures
- EMPLOYEE Table Structures
- Detailed ORDER Table Analysis
- Detailed FILM Table Analysis
Utilities
- Definition of Data Utilities
- Export Utility Operations
- Parameter Configuration
- Using Parameter Files
- Import Utility Operations
- Parameter Configuration
- Using Parameter Files
- Data Unloading Procedures
- Batch Processing Execution
- SQL*Loader Utility
- Executing Utility Tasks
- Appending Data Records
Requirements
This program is designed for individuals possessing foundational knowledge of SQL, as well as those encountering Oracle systems for the first time. It serves to standardize technical skills across diverse workforce levels.
Prior experience with interactive computing systems is recommended but not mandatory, ensuring broad accessibility for government personnel.
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.
