Course Outline
Introduction
- Course Overview
- Learning Objectives and Aims
- Introduction to Sample Data
- Schedule and Timeline
- Participant Introductions
- Prerequisites Check
- Roles and Responsibilities
Relational Databases
- Understanding the Database
- The Concept of Relational Databases
- Structure of Tables
- Rows and Columns Explained
- Review of Sample Database
- Techniques for Selecting Rows
- The Supplier Table Structure
- The Saleord Table Structure
- Primary Key Indexing
- Secondary Indexes
- Data Relationships
- Conceptual Analogies
- Defining Foreign Keys
- Foreign Key Implementation
- Strategies for Joining Tables
- Ensuring Referential Integrity
- Classifying Types of Relationships
- Handling Many-to-Many Relationships
- Resolving Many-to-Many Relationships
- One-to-One Relationships
- Finalizing Database Design
- Methods for Resolving Relationships
- Relationships in Microsoft Access
- Entity Relationship Diagrams
- Principles of Data Modelling
- Introduction to CASE Tools
- Example Diagram
- The Role of the RDBMS
- Benefits of Using an RDBMS
- Overview of Structured Query Language
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- The Case for Using SQL
- Course Tables Reference Handout
Data Retrieval
- Introduction to SQL Developer
- Establishing a Connection in SQL Developer
- Viewing Table Metadata
- Querying with the WHERE Clause
- Using Comments in Code
- Handling Character Data
- Understanding Users and Schemas
- Logical Operators: AND and OR
- Grouping Conditions with Brackets
- Working with Date Fields
- Querying by Date
- Formatting Date Output
- Custom Date Formats
- The TO_DATE Function
- The TRUNC Function
- Displaying Dates
- Sorting Results with ORDER BY
- The DUAL Table Utility
- String Concatenation
- Selecting Text Values
- Using the IN Operator
- Using the BETWEEN Operator
- Pattern Matching with LIKE
- Common Syntax Errors
- The UPPER Function
- String Delimiters (Single Quotes)
- Identifying Metacharacters
- Introduction to Regular Expressions
- Using the REGEXP_LIKE Operator
- Managing Null Values
- Checking for Nulls with IS NULL
- Handling Nulls with NVL
- Prompts for User Input
Using Functions
- Converting Data Types with TO_CHAR
- Converting Data Types with TO_NUMBER
- Left Padding with LPAD
- Right Padding with RPAD
- Null Value Handling with NVL
- The NVL2 Function
- Removing Duplicates with DISTINCT
- Extracting Substrings with SUBSTR
- Locating Substrings with INSTR
- Advanced Date Functions
- Introduction to Aggregate Functions
- Counting Records with COUNT
- Grouping Data with GROUP BY
- ROLLUP and CUBE Modifiers
- Filtering Groups with HAVING
- Grouping Using Functions
- Conditional Logic with DECODE
- Conditional Logic with CASE
- Practical Workshop Session
Sub-Queries & Union
- Working with Single Row Sub-queries
- Combining Results with UNION
- Including Duplicates with UNION ALL
- Finding Common or Unique Data with Intersect and Minus
- Handling Multiple Row Sub-queries
- Using UNION for Data Validation
- Implementing Outer Joins
In-Depth Joins
- Fundamental Join Concepts
- Cross Joins and Cartesian Products
- Performing Inner Joins
- Implicit Join Syntax
- Explicit Join Syntax
- Using Natural Joins
- Executing Equi-Joins
- Understanding Cross Joins
- Variations of Outer Joins
- Left Outer Joins
- Right Outer Joins
- Full Outer Joins
- Combining Results with UNION
- How Join Algorithms Work
- The Nested Loop Algorithm
- The Merge Join Algorithm
- The Hash Join Algorithm
- Reflexive or Self Joins
- Joining a Single Table
- Practical Workshop Session
Advanced Queries
- Working with ROWNUM and ROWID
- Performing Top N Analysis
- Utilizing Inline Views
- Checking Existence with Exists and Not Exists
- Writing Correlated Sub-queries
- Correlated Sub-queries with Functions
- Performing Correlated Updates
- Snapshot Recovery Concepts
- Flashback Recovery Techniques
- Using the ALL Operator
- The Any and Some Operators
- Multi-Table Inserts with INSERT ALL
- Merging Data with the MERGE Statement
Sample Data Sets
- Structure of ORDER Tables
- Structure of FILM Tables
- Structure of EMPLOYEE Tables
- Detailed Look at ORDER Tables
- Detailed Look at FILM Tables
Database Utilities
- Defining Database Utilities
- Using the Export Utility
- Configuring with Parameters
- Using Parameter Files
- Using the Import Utility
- Configuring Import Parameters
- Using Parameter Files for Import
- Unloading Data from the Database
- Automating Batch Runs
- Introducing SQL*Loader Utility
- Executing the Utility
- Appending Data to Existing Records
Requirements
This course is designed to be accessible to both individuals with some prior exposure to SQL and those encountering ORACLE for the first time.
While previous experience with interactive computer systems is beneficial, 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.
