SECTION 1 — SQL Overview (15 minutes)
Slide 1: Introduction to SQL
Trainer Script:
“Welcome everyone. Today we’ll cover key SQL concepts used in analytics
and Oracle Fusion reporting. By the end of this session, you’ll be able to
write basic SELECT queries, use pseudocolumns, and understand essential
SQL functions.”
Slide 2: What Is a Database & Database Objects
Trainer Script:
“In Oracle, a database stores information in organized structures. The
major objects we work with are tables, views, and schemas.”
What Is a Table?
“A table is a structured set of rows and columns. Each row is a record, and
each column represents an attribute.”
What Is a View?
“A view is a virtual table. It doesn’t store data itself; it displays data from
one or more underlying tables.”
What Is a Schema?
“A schema is a container or namespace that groups tables, views, and
other objects belonging to a user.”
Slide 3: Structure of SQL
What Is DQL?
“DQL stands for Data Query Language — used to retrieve data. SELECT is
the main DQL command.”
Purpose of SELECT
“It’s used to extract data from tables.”
SQL Execution Order
“SQL executes in this sequence:
1. FROM
2. WHERE
3. SELECT
4. ORDER BY
This helps in debugging and understanding why some results
appear the way they do.”
Slide 4: Importance of SQL
Trainer Script:
“SQL is critical for analytics because it allows us to extract, filter,
summarize, and transform data directly from the source. Nearly all BI,
reporting, and HCM analytics tools rely on SQL.”
🔹 SECTION 2 — SELECT Queries (30 minutes)
Slide 5: Basic SQL Syntax
Trainer Script:
“A SELECT query has three essential parts:
SELECT column1, column2
FROM table_name
WHERE condition;
Let’s break each one down.”
Slide 6: SQL Tools
Using Data Model
“Oracle Fusion provides a data model view where we can explore available
tables, columns, and joins.”
Toad Studio for Oracle Fusion
“In Toad, we write queries, run them, and inspect results. We’ll use Toad
today for our examples.”
Slide 7: Retrieving Data
Selecting Specific Columns
SELECT first_name, last_name FROM employees;
Selecting All Columns
SELECT * FROM employees;
Using Column Aliases
SELECT first_name AS fname FROM employees;
Trainer Note:
Highlight that aliases improve readability.
Slide 8: Sorting Records
ORDER BY
SELECT * FROM employees ORDER BY hire_date;
ASC / DESC
ORDER BY salary DESC;
Multiple Columns
ORDER BY department_id, salary DESC;
Slide 9: Operators
Explain each with examples:
Comparison Operators
=, <, >, <=, >=, <>
Logical Operators
AND, OR, NOT
IN
WHERE department_id IN (10, 20);
BETWEEN
WHERE salary BETWEEN 5000 AND 10000;
LIKE
WHERE first_name LIKE 'A%';
IS NULL / IS NOT NULL
WHERE manager_id IS NULL;
🔹 SECTION 3 — Pseudocolumns & SQL Functions (30 minutes)
Slide 10: What Are Pseudocolumns?
Trainer Script:
“Pseudocolumns are system-generated values that behave like table
columns, but they’re not actually stored.”
Slide 11: Common Pseudocolumns
ROWID
“Shows physical location of a row.”
ROWNUM
“Useful for limiting results.”
SELECT * FROM employees WHERE ROWNUM <= 5;
LEVEL
“Helps generate sequences — used in hierarchical queries.”
SYSDATE
“Returns current system date/time.”
Slide 12: The DUAL Table
Why It Exists
“A single-row dummy table for running expressions.”
Examples
SELECT SYSDATE FROM DUAL;
SELECT 10+5 FROM DUAL;
SELECT UPPER('test') FROM DUAL;
Slide 13: DISTINCT & NULL Handling
DISTINCT
SELECT DISTINCT department_id FROM employees;
NVL
SELECT NVL(commission_pct, 0) FROM employees;
COALESCE
“Returns the first non-null expression.”
DECODE
“Oracle-specific conditional logic.”
SELECT DECODE(status, 'A', 'Active', 'Inactive') FROM employees;
SECTION 4 — Practice Block (30 minutes)
Trainer Script:
“Now it’s your turn. Here are your exercises. I’ll be walking around to
help.”
Practice Exercises
1. Get top 5 latest entries using ROWNUM
SELECT * FROM employees
ORDER BY hire_date DESC
FETCH FIRST 5 ROWS ONLY;
2. Fetch system date/time
SELECT SYSDATE FROM DUAL;
3. Use LEVEL to create number series 1–10
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
4. Remove duplicates using DISTINCT
SELECT DISTINCT job_id FROM employees;
5. Replace NULL using NVL
SELECT NVL(manager_id, 0) FROM employees;
6. HCM Relevant Examples (You can customize):
Fetch all active employees
List employees without managers
Find employees hired in last 30 days
Get email IDs of people in HR department
Show employees earning more than a threshold
Assessment for the Day
Ask learners to complete:
“Write a single query that:
selects employee name, salary
replaces NULL commission with 0
filters salary > 5000
sorts by salary descending”
SECTION 5 — Summary + Q&A (5–10 minutes)
Trainer Script:
“To summarize today:
You learned SQL basics and Oracle objects
You practiced SELECT queries
You used pseudocolumns and functions
You explored NULL handling and ordering
And you did real HCM-style exercises