Course Outline
Introduction
- Course Overview
- Learning Objectives and Goals
- Sample Dataset
- Itinerary
- Participant Introductions
- Prerequisites
- Course Responsibilities
Relational Databases
- Database Concepts
- The Relational Model
- Tables
- Records and Fields
- Sample Database Structure
- Row Selection
- Supplier Table Example
- Saleord Table Example
- Primary Key Indexing
- Secondary Indexing
- Data Relationships
- Conceptual Analogies
- Foreign Keys
- Foreign Key Constraints
- Table Joining
- Referential Integrity
- Relationship Types
- Many-to-Many Relationships
- Resolving Many-to-Many Relationships
- One-to-One Relationships
- Finalizing Database Design
- Relationship Resolution Strategies
- Microsoft Access Relationships
- Entity-Relationship Diagrams (ERD)
- Data Modeling
- Computer-Aided Software Engineering (CASE) Tools
- Sample ERD
- Relational Database Management Systems (RDBMS)
- Benefits of RDBMS
- Introduction to SQL
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DCL - Data Control Language
- The Advantages of Using SQL
- Course Reference Tables
Data Retrieval
- SQL Developer Environment
- Establishing SQL Developer Connections
- Inspecting Table Metadata
- Using the WHERE Clause
- Adding SQL Comments
- Handling Character Data
- Users and Schemas
- Logical AND and OR Operators
- Using Parentheses for Logic
- Date Fields
- Querying with Dates
- Formatting Date Output
- Supported Date Formats
- TO_DATE Function
- TRUNC Function
- Date Display Options
- ORDER BY Clause
- The DUAL Table
- String Concatenation
- Selecting Text Data
- IN Operator
- BETWEEN Operator
- LIKE Operator
- Common SQL Errors
- UPPER Function
- String Quoting
- Identifying Metacharacters
- Regular Expressions
- REGEXP_LIKE Operator
- Handling Null Values
- IS NULL Operator
- NVL Function
- Accepting User Input
Using Functions
- TO_CHAR Function
- TO_NUMBER Function
- LPAD Function
- RPAD Function
- NVL Function
- NVL2 Function
- DISTINCT Keyword
- SUBSTR Function
- INSTR Function
- Date Manipulation Functions
- Aggregate Functions
- COUNT Function
- GROUP BY Clause
- ROLLUP and CUBE Modifiers
- HAVING Clause
- Grouping with Functions
- DECODE Function
- CASE Expression
- Practical Workshop
Sub-Queries & Unions
- Single-Row Subqueries
- UNION Operator
- UNION ALL Operator
- INTERSECT and MINUS Operators
- Multi-Row Subqueries
- Using UNION for Data Validation
- Outer Joins
Advanced Joins
- Join Fundamentals
- Cross Joins and Cartesian Products
- Inner Joins
- Implicit Join Syntax
- Explicit Join Syntax
- Natural Joins
- Equi-Joins
- Cross Joins
- Outer Joins Overview
- Left Outer Joins
- Right Outer Joins
- Full Outer Joins
- Using UNION with Joins
- Join Execution Algorithms
- Nested Loop Joins
- Merge Joins
- Hash Joins
- Reflexive (Self) Joins
- Single-Table Joins
- Practical Workshop
Advanced Queries
- ROWNUM and ROWID Pseudocolumns
- Top N Analysis
- Inline Views
- EXISTS and NOT EXISTS Operators
- Correlated Subqueries
- Correlated Subqueries with Functions
- Correlated Updates
- Snapshot Recovery
- Flashback Recovery
- 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 (EXP)
- Using Utility Parameters
- Utilizing Parameter Files
- Import Utility (IMP)
- Using Utility Parameters
- Utilizing Parameter Files
- Data Unloading
- Batch Processing Runs
- SQL*Loader Utility
- Executing the Utility
- Appending Data
Requirements
This course is designed for individuals with some familiarity with SQL, as well as those encountering Oracle for the first time.
Prior experience with interactive computing environments is recommended but not mandatory.
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.
