Skip to content
info@bluemarkacademy.com0212 706 07 05
Contact

Advanced Data Analysis with Oracle PL/SQL

Training Format: Live Online / In-Person Duration: 3 Days Download PDF
Home›Courses›Advanced Data Analysis with Oracle PL/SQL

Description

This course is designed for professionals who need to perform advanced data analysis in Oracle environments using SQL and PL/SQL query techniques. Participants learn to derive meaningful results from complex data sets through temporary tables, subqueries, analytical functions, and data transformation methods. Hands-on exercises cover performant query design, date and time data management, and PIVOT and UNPIVOT transformations. The course focuses on query and temporary table operations for decision support scenarios without developing procedures, packages, or functions. Delegates will learn how to
  • Perform data and table operations with INSERT, UPDATE, DELETE, and DDL commands
  • Prepare data using Global Temporary Tables and External Tables
  • Build complex queries with CTEs, correlated subqueries, and nested subqueries
  • Use analytical functions for ranking, comparison, and cumulative calculations
  • Manage date, time zone, and INTERVAL data in analysis scenarios
  • Transform data between row and column structures with PIVOT and UNPIVOT
  • Assess query performance using basic execution plan analysis
Audience
  • Auditors with Oracle PL/SQL knowledge who need to perform advanced data analysis
  • Professionals working on decision support reporting and data investigation
  • Data analysts creating complex queries in Oracle environments
  • Professionals seeking to improve their use of temporary tables and analytical functions

Outline

1. Advanced DML and DDL Operations
  • Data operations with INSERT, UPDATE, and DELETE
  • Using CREATE TABLE, ALTER TABLE, and DROP TABLE
  • Data type conversion with TO_CHAR, TO_DATE, CAST, and CONVERT
  • NOT NULL, UNIQUE, CHECK, and DEFAULT constraints
  • Creating tables from query results with CREATE TABLE AS SELECT
2. Database Objects and Data Dictionary
  • Table, View, Index, Sequence, Synonym, and Trigger concepts
  • The role of database objects in query activities
  • Data Dictionary structure and basic catalog queries
3. Data Preparation with Temporary and External Tables
  • Global Temporary Table structure and usage scenarios
  • Inserting and querying data in temporary tables
  • External Table concepts and data loading examples
  • Building analysis flows with intermediate tables
4. Subqueries and CTEs
  • Scalar, multicolumn, and nested subqueries
  • Data analysis with correlated subqueries
  • Using WITH clause and Common Table Expressions
  • INSERT, UPDATE, and DELETE operations with subqueries
  • Readability and performance considerations for subqueries
5. Date, Time, and Time Zone Analysis
  • Using CURRENT_DATE and LOCALTIMESTAMP
  • Calculating date differences with INTERVAL data types
  • Managing data across different time zones
  • Creating analysis conditions with date and time data
6. Data Analysis with Analytical Functions
  • Using PARTITION BY and ORDER BY
  • Ranking with RANK, DENSE_RANK, and ROW_NUMBER
  • Comparing rows with LEAD and LAG
  • Value analysis with FIRST_VALUE and LAST_VALUE
  • Cumulative calculations and real-world data scenarios
7. Query Performance, PIVOT, and Applied Analysis
  • Execution plan concepts and basic plan reading
  • Performance considerations for nested queries, WITH clauses, and Views
  • Data access techniques for partitioned tables
  • Data transformations with PIVOT and UNPIVOT
  • Currency conversion, insurance limit, and cumulative calculation scenarios
  • Prompt-based examples for query error analysis and improvement

Prerequisites

  • Working knowledge of Oracle SQL, including SELECT, JOIN, WHERE, GROUP BY, and basic subqueries
  • Experience running queries in an Oracle PL/SQL environment
  • Required access permissions for querying tables and working with temporary tables in the training environment