Oracle Database Tutorial

1. Oracle Database Introduction

What Oracle Database is, getting a free instance to practice on, connecting via SQL*Plus, and the DUAL table.

2. Syntax and Data Types

CREATE TABLE, VARCHAR2, NUMBER, DATE vs TIMESTAMP, CLOB/BLOB, ROWID, and constraints in Oracle.

3. Queries and Joins

The legacy (+) join operator, modern ANSI joins, and pagination with ROWNUM vs FETCH FIRST.

4. Functions and Analytic Queries

NVL, DECODE, TO_CHAR, and window functions like RANK, LAG/LEAD, and running totals.

5. PL/SQL Fundamentals

Anonymous blocks, variables, %TYPE/%ROWTYPE, control flow, and cursors in PL/SQL.

6. Stored Procedures, Functions, and Packages

IN/OUT parameters, exception handling, RAISE_APPLICATION_ERROR, and building packages.

7. Indexes, Execution Plans, and Tuning

B-tree vs bitmap indexes, reading EXPLAIN PLAN/DBMS_XPLAN, and an intro to partitioning.

8. Transactions, Sequences, and Concurrency

COMMIT/ROLLBACK, Oracle read consistency, SEQUENCE objects, and identity columns.

9. Views, Materialized Views, and Triggers

Regular vs materialized views, refresh strategies, and row-level vs statement-level triggers.

10. Tricky Queries and Puzzles

Classic Oracle interview puzzles: Nth highest salary, gaps and islands, pivots, and CONNECT BY.

11. Example Project: Employee Management

A worked mini-project: schema design, business queries, and a validated transfer procedure.

12. Oracle Interview Questions

Conceptual Oracle interview Q&A: read consistency, materialized views, sequences, and packages.