Course Outline
Introduction
- Course Overview
- Learning Objectives and Aims
- Sample Datasets
- Daily Schedule
- Participant Introductions
- Pre-requisites
- Role Responsibilities
Relational Databases
- Database Concepts
- Understanding Relational Databases
- Tables Structure
- Rows and Columns
- Sample Database Environment
- Selecting Specific Rows
- Supplier Table Example
- Saleord Table Example
- Primary Key Index
- Secondary Indexes
- Data Relationships
- Conceptual Analogy
- Foreign Key Constraints
- Foreign Key References
- Joining Multiple Tables
- Ensuring Referential Integrity
- Types of Relationships
- Many-to-Many Relationships
- Resolving Many-to-Many Structures
- One-to-One Relationships
- Finalizing the Schema Design
- Strategies for Resolving Relationships
- Relationships in Microsoft Access
- Entity Relationship Diagrams
- Data Modelling Principles
- CASE Tools
- Sample Diagrams
- The RDBMS Architecture
- Benefits of Using an RDBMS
- Introduction to SQL
- DDL: Data Definition Language
- DML: Data Manipulation Language
- DCL: Data Control Language
- Advantages of SQL
- Course Tables Reference Handout
Data Retrieval
- Introduction to SQL Developer
- Establishing Connections in SQL Developer
- Inspecting Table Information
- Using SQL with Where Clauses
- Adding Comments to Code
- Handling Character Data
- Understanding Users and Schemas
- Combining Conditions with AND and OR
- Using Brackets for Logic Grouping
- Working with Date Fields
- Querying with Dates
- Formatting Date Output
- Defining Date Formats
- Using the TO_DATE Function
- Using the TRUNC Function
- Displaying Dates
- Ordering Results with Order By
- The DUAL Table
- String Concatenation
- Selecting Text Values
- Using the IN Operator
- Using the BETWEEN Operator
- Using the LIKE Operator
- Common Syntax Errors
- The UPPER Function
- Usage of Single Quotes
- Identifying Metacharacters
- Introduction to Regular Expressions
- Using the REGEXP_LIKE Operator
- Handling Null Values
- Using the IS NULL Operator
- Using the NVL Function
- Prompting for User Input
Using Functions
- The TO_CHAR Function
- The TO_NUMBER Function
- Padding with LPAD
- Padding with RPAD
- Handling Nulls with NVL
- The NVL2 Function
- Using the DISTINCT Option
- Extracting Substrings with SUBSTR
- Finding Positions with INSTR
- Applying Date Functions
- Using Aggregate Functions
- Counting Records with COUNT
- Using the Group By Clause
- Rollup and Cube Modifiers
- Filtering Groups with Having
- Grouping by Functions
- Conditional Logic with DECODE
- Conditional Logic with CASE
- Practical Workshop
Sub-Query & Union
- Single-Row Sub-queries
- Combining Results with Union
- Union Including Duplicates
- Using Intersect and Minus
- Multi-Row Sub-queries
- Validating Data with Union
- Performing Outer Joins
More On Joins
- Introduction to Joins
- Cross Joins and Cartesian Products
- Understanding Inner Joins
- Implicit Join Syntax
- Explicit Join Syntax
- Natural Joins
- Equi-Joins
- Cross Joins
- Types of Outer Joins
- Left Outer Joins
- Right Outer Joins
- Full Outer Joins
- Combining Results with UNION
- Join Algorithms Overview
- Nested Loop Joins
- Merge Joins
- Hash Joins
- Reflexive or Self Joins
- Joining a Table to Itself
- Practical Workshop
Advanced Queries
- Working with ROWNUM and ROWID
- Performing Top N Analysis
- Creating Inline Views
- Using Exists and Not Exists
- Building Correlated Sub-queries
- Correlated Sub-queries with Functions
- Updating with Correlated Sub-queries
- Snapshot Recovery Concepts
- Flashback Recovery Features
- Universal Quantification with All
- Existential Quantification with Any/Some
- Multi-Insert Statements (Insert ALL)
- Using the Merge Statement
Sample Data
- ORDER Table Schema
- FILM Table Schema
- EMPLOYEE Table Schema
- Exploring the ORDER Tables
- Exploring the FILM Tables
Utilities
- Definition of Database Utilities
- Using the Export Utility
- Configuring Export Parameters
- Utilizing Parameter Files for Export
- Using the Import Utility
- Configuring Import Parameters
- Utilizing Parameter Files for Import
- Extracting Data
- Automating Batch Runs
- Introduction to SQL*Loader
- Executing Loader Utilities
- Appending New Data
Requirements
This course is designed for individuals with some existing knowledge of SQL, as well as those encountering ORACLE for the first time.
Prior experience with interactive computer systems is beneficial 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.
