Oracle SQL & PL/SQL

Module 1: Database Fundamentals

• Introduction to RDBMS vs DBMS concepts.
• Oracle architecture and schema objects overview.
• SQL standards and Oracle-specific extensions.
• Installation of Oracle Database and SQL Developer.

Module 2: Basic SQL Queries

• SELECT statements with WHERE clauses and operators.
• Sorting data using ORDER BY and handling NULLs.
• Arithmetic and comparison operators in queries.
• Pseudo-columns like ROWID, ROWNUM, and SYSDATE.

Module 3: Advanced SQL Functions

• Single-row functions: string, numeric, and date manipulation.
• Aggregate functions: GROUP BY, HAVING, ROLLUP/CUBE.
• Analytic functions: RANK, DENSE_RANK, LAG/LEAD.
• Conversion functions: TO_CHAR, TO_DATE, NVL2.

Module 4: Joins and Set Operations

• INNER, OUTER, CROSS, SELF joins with syntax variations.
• Multi-table queries and join conditions.
• Set operators: UNION, INTERSECT, MINUS.
• Hierarchical queries using CONNECT BY.

Module 5: Subqueries and Correlated Queries

• Single-row and multi-row subqueries.
• Inline views and scalar subqueries.
• Correlated subqueries with EXISTS/NOT EXISTS.
• Common Table Expressions (CTEs) with WITH clause.

Module 6: Data Manipulation Language (DML)

• INSERT, UPDATE, DELETE, MERGE statements.
• Bulk operations and direct path inserts.
• Transaction control: COMMIT, ROLLBACK, SAVEPOINT.
• Flashback query for historical data access.

Module 7: DDL and Schema Objects

• CREATE, ALTER, DROP for tables, views, sequences.
• Constraints: PRIMARY KEY, FOREIGN KEY, CHECK.
• Indexes: B-tree, bitmap, function-based creation.
• Synonyms and materialized views maintenance.

Module 8: Introduction to PL/SQL

• PL/SQL block structure: DECLARE, BEGIN, EXCEPTION.
• Variables, constants, and %TYPE/%ROWTYPE anchors.
• Embedded SQL in PL/SQL blocks.
• Execution sections and SQL*Plus scripting.

Module 9: Control Structures in PL/SQL

• Conditional logic: IF-ELSIF-ELSE, CASE statements.
• Loop constructs: FOR, WHILE, basic loops with EXIT.
• Labels, GOTO, and nested blocks.
• Boolean expressions and short-circuit evaluation.

Module 10: Cursors and Cursor Management

• Implicit vs explicit cursors handling.
• Cursor FOR loops and parameters.
• Cursor variables (REF CURSOR) usage.
• Bulk collect and FORALL for bulk operations.

Module 11: Procedures and Functions

• Creating standalone and packaged procedures/functions.
• Input/output parameters and NOCOPY hint.
• Function overloading and purity levels.
• Invoking procedures from SQL contexts.

Module 12: Packages

• Package specification and body structure.
• Global variables, constants, and overloading.
• Package initialization and state management.
• Forward declarations and dependency handling.

Module 13: Triggers

• DML, DDL, system, and INSTEAD OF triggers.
• Trigger firing order and mutating table issues.
• Compound triggers and trigger variables.
• Autonomous transactions in triggers.

Module 14: Exception Handling

• Predefined and user-defined exceptions.
• SQLCODE, SQLERRM, and pragma EXCEPTION_INIT.
• Propagation rules and WHEN OTHERS clause.
• Exception logging and RAISER_APPLICATION_ERROR.

Module 15: Collections and Advanced Types

• Nested tables, VARRAYs, and associative arrays.
• Collection methods: EXISTS, COUNT, DELETE.
• Object types and TYPE inheritance.
• Pipelined functions for streaming data.

Experience in the Industry Gain knowledge from experts with practical Oracle SQL & PL/SQL project experience in a variety of sectors.

Backgrounds at the Top Prominent corporations such as HCL, TCS, Accenture, and Cognizant employ trainers.

Clear & Effective Teaching Excellent communication and real-world examples simplify complex subjects.

Hands-On Learning Focus Students can apply their abilities in real-world situations with the use of case studies and real-time projects.

Up-to-Date Knowledge Trainers stay current with the latest tools, techniques, and best practices.