Advanced Oracle SQL Course

After learning the fundamentals of Oracle SQL, most professionals quickly encounter tasks that require more advanced queries, processing large volumes of data, or optimizing query performance. In the development of business reports, data analyses, and database applications, advanced SQL techniques, analytic functions, Common Table Expressions (CTEs), and other query optimization tools play a key role.

The aim of this training is to help participants master the advanced features of Oracle SQL and learn how to create well-structured, efficient, and high-performance queries. During the course, participants will become familiar with the modern capabilities of Oracle SQL.

The training is highly practice-oriented: each topic is followed by hands-on lab exercises, and the course concludes with a comprehensive business case study in which participants apply the knowledge they have acquired.

This training is recommended for professionals who are already confident in using the fundamental Oracle SQL statements and want to expand their skills to create more advanced queries, reports, and data processing solutions. It is particularly suitable for data analysts, BI and data warehouse developers, data engineers, software developers, and database administrators (DBAs).

A képzés sikeres elvégzését követően a résztvevő képes lesz:

• összetett Oracle SQL lekérdezések készítésére haladó al-lekérdezési technikák és közös táblakifejezések (CTE) alkalmazásával;
• rekurzív lekérdezések megírására hierarchikus adatok feldolgozásához;
• analitikus (window) függvények használatára rangsorolási, idősoros és üzleti elemzési feladatok során;
• több eredményhalmaz kombinálására SET operátorok segítségével;
• az Oracle végrehajtási terv (Execution Plan) értelmezésére és alkalmazására;
• az indexek alapvető használatára.

Advanced Subqueries

• Overview of single-row and multiple-row subqueries
• Correlated subqueries
• Using scalar subqueries
• Using inline views
• EXISTS and NOT EXISTS
• Fundamentals of subquery optimization
• Hands-on exercise: creating complex business queries using multi-level subqueries.

Overview of SQL Data Manipulation Statements

• INSERT (inserting single and multiple records)
• Using INSERT...SELECT
• UPDATE (conditional updates and updating multiple records)
• DELETE (deleting records and using DELETE safely)
• TRUNCATE
• MERGE

Using Common Table Expressions (WITH)

• The purpose of the WITH clause
• Using multiple CTEs in a single query
• Improving code readability
• Structuring complex queries
• Building recursive CTEs
• Processing hierarchical data
• Practical applications of recursive queries
• Using CONNECT BY (handling the Gaps and Islands problem)
• Hands-on exercise: querying organizational hierarchies and multi-level data structures.

Using Set Operators

• UNION
• UNION ALL
• INTERSECT
• MINUS (or EXCEPT)
• Differences between the set operators
• Practical use cases
• Hands-on exercise: combining and comparing results from different data sources.

Using Analytic (Window) Functions

• Using the OVER clause, PARTITION BY, and ORDER BY with analytic functions
• Ranking functions: ROW_NUMBER, RANK, DENSE_RANK
• Row-by-row analysis: LAG, LEAD
• Running aggregations: SUM OVER, running totals, and the fundamentals of moving averages

Introduction to SQL Execution Plans

• The purpose of the Execution Plan
• Using EXPLAIN PLAN
• Understanding the structure of an execution plan
• Fundamentals of cost-based optimization
• Interpreting the most important execution operations
• Overview and application of JOIN strategies
• Hands-on exercise: analyzing and comparing query execution plans.

Introduction to Indexes

• The purpose of indexes
• How B-tree indexes work
• When does Oracle use an index?
• When does Oracle not use an index?
• Factors affecting index usage

Introduction to Transaction Management (Optional)

• COMMIT
• ROLLBACK
• Automatic transaction management
• The role of transactions
• Hands-on exercise: performing data modifications using transactions.

Completion of the Oracle SQL Fundamentals course, or equivalent knowledge, is required.