Oracle SQL – analytical functions and Oracle database programming

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Accurate data joins - You will learn how to choose the right join type for each task, so you can combine data from multiple tables more reliably and avoid common mistakes in query results.

Advanced aggregations - You will master rollup, cube, grouping, and grouping sets, which will help you build richer summaries and reports without writing many separate queries.

Confident set operations - You will see when to use UNION, UNION ALL, MINUS, and INTERSECT to compare result sets, merge data, and handle common analytical tasks more efficiently.

Stronger subqueries and CTEs - You will practice correlated, scalar, and multirow subqueries along with the WITH clause, so your SQL becomes clearer, more modular, and easier to maintain.

More efficient data work - You will learn how indexes, integrity constraints, MERGE, and multitable INSERT work, so you can modify data faster and keep database structures more consistent.

Analysis with window functions - You will use OVER(), RANK, ROW_NUMBER, DENSE_RANK, LEAD, and LAG to create rankings, Top-N analyses, and row-by-row comparisons for real reporting scenarios.

Advanced Oracle queries - You will understand hierarchical queries, inline views, regular expressions, and date-time handling with time zones, helping you solve more demanding business cases.

Safer transaction handling - You will understand transaction control, shared data issues, read anomalies, and isolation levels, so you can better predict the impact of SQL changes in real systems.

Training programme

1. Discussion of advanced techniques for joining data from multiple tables in SQL

natural join,
using,
on,
cross join,
left, right, full outer joins,
joining a table to itself.

2. Overview of aggregate operators in SQL

rollup,
cube,
grouping,
grouping sets,
non-disjoint columns,
grouping combinations.

3. Discussion of operations on data sets

Union,
Union all,
Minus,
Intersect.

4. Expansion of subquery topics

multi-value, multi-row subqueries,
correlated queries,
scalar subqueries,
correlated DML.

5. Introduction to CTE (Common Table Expressions), i.e. the WITH clause

6. Discussion of inline views, i.e. subqueries

7. Detailed explanation of hierarchical queries

8. Integrity Constraints Overview

9. Discussion of indexes in SQL

10. Description of multitable INSERT operations

Unconditional INSERT ALL,
INSERT ALL with condition,
INSERT FIRST with condition,
Pivoting INSERT ALL.

11. Discussion of the MERGE command

12. Overview of regular expressions in SQL

13. Discussion of time data processing in SQL with consideration of time zones

functions,
data types.

14. Overview of advanced analytical functions in SQL

Top-n analysis,
Over(),
Rank,
RowNumber,
DenseRank,
LEAD/LAG.

15. Discussion of transaction management principles in relational databases

control,
shared data and read errors,
isolation levels.

16. Introduction to the retrospective mechanism in Oracle SQL

What are the prerequisites for participating in the training?

SQL basics - You should be comfortable writing simple SELECT statements and using WHERE, ORDER BY, and basic GROUP BY so you can focus on genuinely advanced topics.

Joins and subqueries - You should already know standard joins and simple subqueries, because the course expands these areas and does not start from a complete beginner level.

Database practice - You should have hands-on experience working with relational databases and tables so you can follow examples related to analysis, DML operations, and transactions.

Oracle SQL fundamentals - You should know the basic Oracle SQL syntax and be able to run queries, because the course focuses on advanced techniques and Oracle-specific extensions.