Course Outline
Introduction
- Course Overview
- Learning Objectives
- Sample Dataset
- Schedule of Events
- Participant Introductions
- Prerequisites
- Participant Responsibilities
Relational Databases
- Database Concepts
- The Relational Model
- Understanding Tables
- Rows and Columns
- Sample Database Setup
- Row Selection
- Supplier Table Example
- Saleord Table Example
- Primary Key Indexes
- Secondary Indexes
- Defining Relationships
- Conceptual Analogy
- Foreign Keys (1)
- Foreign Keys (2)
- Table Joins
- Maintaining Referential Integrity
- Classification of Relationships
- Many-to-Many Relationships
- Resolving Many-to-Many Structures
- One-to-One Relationships
- Finalizing Database Design
- Relationship Resolution Strategies
- Microsoft Access Relationship Models
- Entity Relationship Diagrams
- Data Modelling Principles
- CASE Tools Overview
- Sample Diagram Analysis
- The RDBMS System
- Benefits of RDBMS
- Introduction to Structured Query Language
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- The Advantages of SQL
- Course Tables Handout
Data Retrieval
- Introduction to SQL Developer
- SQL Developer Connections
- Inspecting Table Metadata
- SQL Usage: WHERE Clauses
- Writing Code Comments
- Handling Character Data
- Users and Schemas
- Logical Operators: AND and OR
- Using Parentheses for Logic
- Date Field Handling
- Querying Dates
- Date Formatting
- Standard Date Formats
- TO_DATE Function
- TRUNC Function
- Date Display Options
- Sorting with ORDER BY
- The DUAL Table
- String Concatenation
- Selecting Text Data
- The IN Operator
- The BETWEEN Operator
- The LIKE Operator
- Common Query Errors
- UPPER Function
- Single Quote Syntax
- Identifying Metacharacters
- Regular Expressions Basics
- REGEXP_LIKE Operator
- Handling Null Values
- IS NULL Operator
- NVL Function
- Prompting for User Input
Using Functions
- TO_CHAR Function
- TO_NUMBER Function
- LPAD Function
- RPAD Function
- NVL Function Details
- NVL2 Function
- The DISTINCT Option
- SUBSTR Function
- INSTR Function
- Various Date Functions
- Aggregate Functions
- COUNT Function
- The GROUP BY Clause
- Rollup and Cube Modifiers
- The HAVING Clause
- Grouping with Functions
- DECODE Function
- CASE Statement
- Practical Workshop
Sub-Query & Union
- Single-Row Sub-queries
- The UNION Operator
- UNION ALL
- INTERSECT and MINUS Operators
- Multi-Row Sub-queries
- UNION for Data Validation
- Outer Joins Introduction
More On Joins
- Join Fundamentals
- Cross Joins and Cartesian Products
- Inner Joins
- Implicit Join Syntax
- Explicit Join Syntax
- Natural Joins
- Equi-Joins
- Cross Joins
- Overview of Outer Joins
- Left Outer Joins
- Right Outer Joins
- Full Outer Joins
- Utilizing UNION
- Join Algorithm Strategies
- Nested Loop Joins
- Merge Joins
- Hash Joins
- Reflexive (Self) Joins
- Single Table Joins
- Practical Workshop
Advanced Queries
- ROWNUM and ROWID
- Top N Analysis Techniques
- Inline Views
- EXISTS and NOT EXISTS
- Correlated Sub-queries
- Correlated Sub-queries with Functions
- Correlated Updates
- Snapshot Recovery
- Flashback Recovery
- The ALL Operator
- ANY and SOME Operators
- INSERT ALL Statement
- MERGE Statement
Sample Data
- ORDER Table Structure
- FILM Table Structure
- EMPLOYEE Table Structure
- Detailed ORDER Tables
- Detailed FILM Tables
Utilities
- Understanding Database Utilities
- Export Utility
- Parameter Usage in Export
- Export Parameter Files
- Import Utility
- Parameter Usage in Import
- Import Parameter Files
- Unloading Data Processes
- Batch Processing Runs
- SQL*Loader Utility
- Executing SQL*Loader
- Appending Data Strategies
Requirements
This course is designed for a broad audience, accommodating both those with existing SQL knowledge and individuals using ORACLE for the first time.
While prior experience with interactive computer systems is advantageous, it is not a strict requirement for participation.
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.