Course Outline
Introduction
- Course Overview
- Learning Objectives
- Sample Dataset
- Timetable
- Participant Introductions
- Prerequisites
- Participant Responsibilities
Relational Databases
- Database Concepts
- Relational Model
- Table Structure
- Rows and Columns
- Demonstration Database
- Row Selection
- Supplier Table
- Sales Order Table
- Primary Key Index
- Secondary Indexes
- Table Relationships
- Conceptual Analogy
- Foreign Keys
- Foreign Keys
- Joining Tables
- Referential Integrity
- Relationship Types
- Many-to-Many Relationships
- Resolving Many-to-Many Relationships
- One-to-One Relationships
- Finalizing the Design
- Relationship Resolution
- Microsoft Access - Relationships
- Entity Relationship Diagrams
- Data Modeling
- CASE Tools
- Sample Diagrams
- Relational Database Management Systems (RDBMS)
- Benefits of an RDBMS
- Structured Query Language (SQL)
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DCL - Data Control Language
- The Value of SQL
- Course Tables Handout
Data Retrieval
- SQL Developer Interface
- SQL Developer - Connectivity
- Inspecting Table Metadata
- SQL Filtering with WHERE Clauses
- Adding Comments
- Handling Character Data
- Users and Schemas
- Logical Operators (AND, OR)
- Using Parentheses
- Date Fields
- Utilizing Dates
- Date Formatting
- Date Formats
- TO_DATE Function
- TRUNC Function
- Date Display Options
- Sorting with ORDER BY
- The DUAL Table
- String Concatenation
- Selecting Text
- IN Operator
- BETWEEN Operator
- LIKE Operator
- Common Syntax Errors
- UPPER Function
- Quoting Strings
- Identifying Metacharacters
- Regular Expressions
- REGEXP_LIKE Operator
- Null Values
- IS NULL Operator
- NVL Function
- Prompting for User Input
Working with Functions
- TO_CHAR Function
- TO_NUMBER Function
- LPAD Function
- RPAD Function
- NVL Function
- NVL2 Function
- DISTINCT Modifier
- SUBSTR Function
- INSTR Function
- Date Functions
- Aggregate Functions
- COUNT Function
- Grouping with GROUP BY
- ROLLUP and CUBE Modifiers
- Filtering Groups with HAVING
- Grouping by Functions
- DECODE Function
- CASE Expression
- Hands-on Workshop
Subqueries and Set Operations
- Single-Row Subqueries
- UNION Operation
- UNION ALL Operation
- INTERSECT and MINUS Operations
- Multi-Row Subqueries
- UNION – Data Validation
- Outer Joins
Advanced Joins
- Join Fundamentals
- Cross Join / Cartesian Product
- Inner Join
- Implicit Join Syntax
- Explicit Join Syntax
- Natural Join
- Equi-Join
- Cross Join
- Outer Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Using UNION
- Join Algorithms
- Nested Loop Join
- Merge Join
- Hash Join
- Self-Joins
- Single Table Joins
- Hands-on Workshop
Advanced Query Techniques
- ROWNUM and ROWID
- Top N Analysis
- Inline Views
- EXISTS and NOT EXISTS
- Correlated Subqueries
- Correlated Subqueries with Functions
- Correlated Updates
- Snapshot Recovery
- Flashback Recovery
- Quantifier: ALL
- Quantifiers: ANY and SOME
- INSERT ALL Statement
- MERGE Statement
Sample Datasets
- Order Tables
- Film Tables
- Employee Tables
- Detailed Order Table Structure
- Detailed Film Table Structure
Database Utilities
- Understanding Utilities
- Export Utility
- Parameter Usage
- Using Parameter Files
- Import Utility
- Parameter Usage
- Using Parameter Files
- Data Unloading
- Batch Processing
- SQL*Loader Utility
- Executing the Utility
- Data Appending
Requirements
This session is ideal for individuals with existing SQL knowledge as well as those encountering Oracle for the first time.
Prior familiarity with interactive computing systems 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.
