This four-day course provides an in-depth understanding of intermediate SQL concepts and advanced querying techniques using Toad for Oracle in an Oracle 19C environment. The course includes hands-on labs to reinforce key concepts, focusing on performance tuning, analytical functions, and advanced SQL operations.
There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Custom Intermediate Oracle SQL class.
Day 1: SQL Fundamentals Refresher
Lesson 1: SQL Review – Filtering & Aggregations
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
DISTINCT, LIMIT, FETCH
Hands-on: Writing basic queries with filtering and grouping
Lesson 2: Joins – Combining Data from Multiple Tables
INNER, LEFT, RIGHT, FULL, CROSS JOIN
Hands-on: Writing and optimizing JOIN queries
Lesson 3: Set Operators & Subqueries (Basic)
UNION, UNION ALL, INTERSECT, MINUS
Single-row and multi-row subqueries
Hands-on: Using set operators and subqueries
Lesson 4: Correlated Subqueries & Inline Views
Correlated subqueries
Inline views and temporary tables
Hands-on: Writing and optimizing correlated subqueries
Lesson 5: Single-Row Functions in Oracle SQL
String, Numeric, and Date functions
Hands-on: Applying functions in queries
Lesson 6: Common Table Expressions (CTEs) & Recursive Queries
WITH Clause for readability
Recursive CTEs
Hands-on: Using CTEs for complex queries
Day 2: Advanced SQL Queries
Module 2: Advanced Query Techniques
Lesson 7: Advanced Grouping Techniques
GROUPING SETS, ROLLUP, CUBE
GROUPING_ID function
Hands-on: Advanced aggregations
Lesson 8: Pivoting and Unpivoting Data
PIVOT and UNPIVOT operators
Hands-on: Transforming rows into columns and vice versa
Module 3: Oracle SQL Analytical Functions (Part 1)
Lesson 9: Introduction to Analytical Functions
Aggregate vs. Analytical functions
OVER() Clause, PARTITION BY
Hands-on: Basic analytical queries
Lesson 10: Ranking Functions
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
Hands-on: Ranking datasets
Day 3: Oracle SQL Analytical Functions & Reporting
Module 3: Oracle SQL Analytical Functions (Part 2)
Lesson 11: Windowing Functions
LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()
Hands-on: Trend analysis using window functions
Lesson 12: Statistical & Aggregate Functions
STDDEV(), VARIANCE(), COVAR_POP(), CORR()
LISTAGG() for string aggregation
Hands-on: Using statistical functions in queries
Lesson 13: Running Totals & Cumulative Aggregates
SUM(), AVG() as window functions
Running totals with ORDER BY
Hands-on: Financial and cumulative reporting
Day 4: SQL Performance Tuning & Best Practices
Module 4: SQL Performance Tuning
Lesson 14: Indexes & Their Impact on Performance
B-Tree, Bitmap, Function-Based Indexes
Index usage in WHERE and JOIN clauses
Hands-on: Creating and analyzing indexes
Lesson 15: Query Optimization Techniques
SQL Hints (USE_NL, PARALLEL, etc.)
Avoiding full table scans
Hands-on: Optimizing slow queries
Lesson 16: Understanding Execution Plans
Basics of EXPLAIN PLAN
Reading Cost, Cardinality, and Access Paths
Autotrace and SQL Monitor for Performance Insights
Hands-on: Running EXPLAIN PLAN
There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Custom Intermediate Oracle SQL class.
There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Custom Intermediate Oracle SQL class.
