Oracle Programmer

Module-1 Basic Concept of RDBMS
This module introduces the fundamental concepts of relational database management systems (RDBMS), including database architecture, components, and basic terminology used in database operations.

Module-2 Introduction to SQL

Introduction to Oracle Database
Students will learn about Oracle Database 12c features, its basic design, and the categorization of SQL statements. The module covers describing the dataset used in the course, logging into the database using SQL Developer, and saving queries to files for reuse in scripts.

Retrieve Data using the SQL SELECT Statement
This section covers the capabilities of the SELECT statement, retrieving all columns or specific columns from a table, using arithmetic and concatenation operators, and using the DESCRIBE command to view table structures.

Learn to Restrict and Sort Data
Students will use the WHERE clause to filter output, understand comparison and logical operators, learn precedence rules, use character string literals, apply ORDER BY for sorting, and implement SQL row-limiting clauses like FETCH FIRST, OFFSET, and WITH TIES.

Usage of Single-Row Functions to Customize Output
The section covers differences between single-row and multiple-row functions, string manipulation functions in SELECT and WHERE clauses, numeric functions like ROUND, TRUNC, and MOD, date arithmetic, and using DATE functions.

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

Aggregate Data Using the Group Functions
This part explains aggregation functions, grouping data using GROUP BY, and filtering grouped data with HAVING clauses to produce meaningful reports.

Display Data From Multiple Tables Using Joins
Students will write SELECT statements to access data from multiple tables, use outer joins to view unmatched data, and perform self-joins.

Use Sub-queries to Solve Queries
This section describes the types of problems sub-queries can solve, defines sub-queries, lists sub-query types, and demonstrates writing single-row and multiple-row sub-queries.

The SET Operators
Students will learn about SET operators, combining results using UNION, UNION ALL, INTERSECT, and MINUS, and controlling the order of returned rows.

Data Manipulation Statements
The module covers DML statements—INSERT, UPDATE, DELETE—saving and discarding changes using COMMIT and ROLLBACK, and explaining read consistency.

Use of DDL Statements to Create and Manage Tables
This section teaches creating, altering, truncating, and dropping tables, understanding the Recycle Bin concept, enforcing data integrity with constraints like primary key, foreign key, NOT NULL, UNIQUE, and CHECK.

Other Schema Objects
Students will create and manage simple and complex views, retrieve data from views, create and use sequences, maintain indexes, and work with private and public synonyms.

Module-3 Oracle Database Program with PL/SQL

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

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

Write Executable Statements
Explains basic PL/SQL syntax, commenting, deploying SQL functions, data type conversion, nested blocks, operators, SQL cursors, cursor attributes for DML feedback, and transaction control.

Control Structures
Covers conditional processing with IF and CASE statements, looping structures (simple, WHILE, FOR loops), and the CONTINUE statement.

Composite Data Types
Focuses on PL/SQL records, %ROWTYPE attribute, and performing insert and update operations with records.

Overview of Collections
Introduces index-by tables, clustered tables, and VARRAY collections.

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

Exception Handling
Students learn to understand and handle exceptions, trap predefined, non-predefined, and user-defined exceptions, propagate exceptions, and use RAISE_APPLICATION_ERROR.

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

Triggers
Introduces triggers, event types, body, DML triggers in SQL Developer, firing timing, statement-level vs row-level triggers, INSTEAD OF and DISABLED triggers, and management, testing, and removal of triggers.