Oracle PL/SQL Developer

Module-1: Introduction to SQL

Introduction to Oracle Database
This section introduces Oracle Database 12c, including its key features, the basic design of databases, and the types of SQL statements. Students will learn to describe the dataset used in the course, log in using SQL Developer, and save queries to files or scripts for reuse.

Retrieve Data using the SQL SELECT Statement
Covers the capabilities of the SELECT statement, selecting all or specific columns, using arithmetic and concatenation operators, and employing the DESCRIBE command to examine table structures.

Learn to Restrict and Sort Data
Students learn to filter output using the WHERE clause, understand comparison and logical operators, operator precedence rules, character string literals, and sorting with ORDER BY. SQL row-limiting clauses such as FETCH FIRST, OFFSET, and WITH TIES are also included.

Usage of Single-Row Functions to Customize Output
Covers the differences between single-row and multiple-row functions, string and numeric manipulation using ROUND, TRUNC, MOD, arithmetic operations on dates, and DATE functions.

Invoke Conversion Functions and Conditional Expressions
Students learn implicit and explicit data type conversion, TO_CHAR, TO_NUMBER, TO_DATE functions, nesting multiple functions, using NVL, NULLIF, COALESCE, and applying IF-THEN-ELSE logic in SELECT statements.

Aggregate Data Using Group Functions
Covers aggregation functions, grouping with GROUP BY, and filtering grouped data with HAVING to generate meaningful reports.

Display Data From Multiple Tables Using Joins
Students learn SELECT statements across multiple tables, outer joins for unmatched data, and self-joins.

Use Sub-queries to Solve Queries
Covers defining sub-queries, types of sub-queries, and writing single-row and multiple-row sub-queries.

The SET Operators
Introduces SET operators, combining results using UNION, UNION ALL, INTERSECT, MINUS, and controlling row order.

Data Manipulation Statements
Covers INSERT, UPDATE, DELETE statements, transaction control with COMMIT and ROLLBACK, and the concept of read consistency.

Use of DDL Statements to Create and Manage Tables
Covers creating, altering, truncating, and dropping tables, enforcing data integrity through constraints such as primary key, foreign key, NOT NULL, UNIQUE, CHECK, and understanding the Recycle Bin concept.

Other Schema Objects
Covers creating simple and complex views, retrieving data from views, managing sequences, creating and maintaining indexes, and working with private and public synonyms.

Module-2: Oracle Database Program with PL/SQL

Introduction to PL/SQL
Provides an overview of PL/SQL, benefits of PL/SQL subprograms, types of PL/SQL blocks, creating anonymous blocks, and generating output.

Declare PL/SQL Identifiers
Covers types of identifiers, using the declarative section, variables, scalar data types, the %TYPE attribute, bind variables, and sequences in PL/SQL expressions.

Write Executable Statements
Includes basic PL/SQL syntax guidelines, code commenting, using SQL functions in PL/SQL, data type conversion, nested blocks, operators, SQL cursors, cursor attributes for DML feedback, and saving/discarding transactions.

Control Structures
Covers conditional processing using IF and CASE statements, simple loops, WHILE loops, FOR loops, and the CONTINUE statement.

Composite Data Types
Covers PL/SQL records, the %ROWTYPE attribute, and inserting/updating using records.

Overview of Collections
Includes index-by tables, clustered tables, and VARRAYs.

Explicit Cursors
Covers defining explicit cursors, opening, fetching, and closing cursors, cursor FOR loops, %NOTFOUND and %ROWCOUNT attributes, and using FOR UPDATE and WHERE CURRENT clauses.

Exception Handling
Covers understanding exceptions, handling predefined and user-defined exceptions, propagating exceptions, and using RAISE_APPLICATION_ERROR.

Stored Procedures
Covers modularized subprogram design, differences between anonymous blocks and subprograms, creating, calling, and removing stored procedures, implementing parameters and modes, and viewing procedure information.

Triggers
Covers trigger concepts, event types and bodies, business scenarios for triggers, creating DML triggers, statement-level vs. row-level triggers, INSTEAD OF and disabled triggers, and managing, testing, and removing triggers.