Course Outline
Introduction
- Course Overview
- Learning Aims and Goals
- Sample Datasets
- Timetable
- Participant Introductions
- Prerequisites
- Participant and Instructor Responsibilities
Relational Databases
- Database Concept
- Relational Database Model
- Tables
- Rows and Columns
- Example Database
- Filtering Rows
- Supplier Table Structure
- Saleord Table Structure
- Primary Key Indexing
- Secondary Indexes
- Inter-Table Relationships
- Conceptual Analogies
- Foreign Key Constraints
- Foreign Key Definitions
- Combining Tables
- Ensuring Referential Integrity
- Varieties of Relationships
- Many-to-Many Associations
- Managing Many-to-Many Connections
- One-to-One Associations
- Finalizing the Design
- Strategy for Resolving Relationships
- Microsoft Access - Managing Relationships
- Entity Relationship Diagrams
- Data Modeling Techniques
- CASE Tools
- Diagram Examples
- RDBMS Fundamentals
- Benefits of an RDBMS
- Structured Query Language
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DCL - Data Control Language
- The Case for SQL
- Course Tables Handout
Data Retrieval
- SQL Developer Interface
- Establishing Connections in SQL Developer
- Inspecting Table Metadata
- SQL Usage and Where Clauses
- Adding Comments
- Handling Character Data
- Users and Schemas
- Logical Operators AND and OR
- Using Parentheses for Clarity
- Date Field Handling
- Working with Dates
- Date Formatting
- Date Format Standards
- TO_DATE Function
- TRUNC Function
- Displaying Dates
- Order By Clause
- DUAL Table
- String Concatenation
- Selecting Textual Data
- IN Operator
- BETWEEN Operator
- LIKE Operator
- Common Mistakes
- UPPER Function
- Quotation Marks
- Identifying Metacharacters
- Regular Expressions
- REGEXP_LIKE Operator
- Handling Null Values
- IS NULL Operator
- NVL Function
- Accepting User Input
Using Functions
- TO_CHAR
- TO_NUMBER
- LPAD
- RPAD
- NVL
- NVL2 Function
- DISTINCT Option
- SUBSTR
- INSTR
- Date Functions
- Aggregate Functions
- COUNT
- Group By Clause
- Rollup and Cube Modifiers
- Having Clause
- Grouping with Functions
- DECODE
- CASE
- Workshop Session
Sub-Query & Union
- Single Row Sub-queries
- Union
- Union - All
- Intersect and Minus
- Multiple Row Sub-queries
- Union – Data Validation
- Outer Join
More On Joins
- Joins
- Cross Join or Cartesian Product
- Inner Join
- Implicit Join Notation
- Explicit Join Notation
- Natural Join
- Equi-Join
- Cross Join
- Outer Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Using UNION
- Join Algorithms
- Nested Loop
- Merge Join
- Hash Join
- Reflexive or Self Join
- Single Table Join
- Workshop Session
Advanced Queries
- ROWNUM and ROWID
- Top N Analysis
- Inline View
- Exists and Not Exists
- Correlated Sub-queries
- Correlated Sub-queries with Functions
- Correlated Update
- Snapshot Recovery
- Flashback Recovery
- All
- Any and Some Operators
- Insert ALL
- Merge
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
- The ORDER Tables
- The FILM Tables
Utilities
- What is a Utility?
- Export Utility
- Using Parameters
- Using a Parameter file
- Import Utility
- Using Parameters
- Using a Parameter file
- Unloading Data
- Batch Runs
- SQL*Loader Utility
- Running the Utility
- Appending Data
Requirements
This course is designed to accommodate learners with varying levels of SQL experience, welcoming both those with existing knowledge and individuals using 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.
