Skip to content
Tutorials Center
My learning

Topics

Oracle SQL and PL/SQL Tutorial

Learn Oracle SQL and PL/SQL step by step: 38 lessons in 10 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 What Is Oracle Database? Meet Oracle Database 23ai: what it is, how an instance and a database fit together, what SQL and PL/SQL are, and how the examples in this course run.
  2. 2 Oracle Tools and Setup Install Oracle Database Free, connect with SQL*Plus, SQLcl or SQL Developer, create your first user, and learn the commands every Oracle session needs.
  3. 3 Oracle Data Types Learn the Oracle SQL data types: VARCHAR2, NUMBER precision and scale, DATE with time, TIMESTAMP, CLOB, BLOB, BOOLEAN in 23ai, and the empty-string rule.
  4. 4 First Queries: SELECT and DUAL Write your first Oracle queries: SELECT lists, column aliases, concatenation with ||, DISTINCT, arithmetic, and the one-row DUAL table used for expressions.

SQL in Oracle

  1. 5 Filtering and Sorting in Oracle Filter rows with WHERE, LIKE, IN and BETWEEN, sort with NULLS FIRST or LAST, and limit results with FETCH FIRST, OFFSET and the classic ROWNUM trap.
  2. 6 Creating Tables and Constraints Create Oracle tables with primary keys, foreign keys, unique, check and not-null constraints, add virtual columns, alter and drop tables, and read the dictionary.
  3. 7 INSERT, UPDATE, DELETE and MERGE Change data in Oracle with INSERT, INSERT ALL, UPDATE, DELETE and MERGE upserts, return changed values, and control transactions with COMMIT and ROLLBACK.
  4. 8 Sequences and Identity Columns Generate unique keys in Oracle with CREATE SEQUENCE, NEXTVAL and CURRVAL, identity columns, DEFAULT sequence values, caching, gaps and restarting a sequence.

Functions and Expressions

  1. 9 String Functions in Oracle Master Oracle string functions: UPPER, INITCAP, SUBSTR, INSTR, REPLACE, TRANSLATE, LPAD, TRIM, and the REGEXP family, with the 1-based positions Oracle uses.
  2. 10 Number and Date Functions in Oracle Round and truncate numbers, use MOD and POWER, and work with Oracle dates: SYSDATE, date arithmetic, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, TRUNC and EXTRACT.
  3. 11 Conversion and NULL Functions Convert data types with TO_CHAR, TO_DATE and TO_NUMBER format models, handle NULL with NVL, NVL2, COALESCE and NULLIF, and branch with DECODE and CASE.
  4. 12 Aggregate and Analytic Functions Summarize data in Oracle with GROUP BY, HAVING, LISTAGG and ROLLUP, then keep every row with analytic functions: RANK, ROW_NUMBER, LAG, LEAD and running totals.

Joins, Subqueries and Hierarchies

  1. 13 Joins in Oracle Combine tables in Oracle with INNER, LEFT, RIGHT, FULL and CROSS joins, USING and ON, self joins, the old (+) outer join syntax and LATERAL joins.
  2. 14 Subqueries and Set Operators Nest queries with scalar, IN, EXISTS and correlated subqueries, use inline views and the WITH clause, and combine results with UNION, INTERSECT and MINUS.
  3. 15 Hierarchical Queries with CONNECT BY Walk trees in Oracle with START WITH and CONNECT BY PRIOR, LEVEL, SYS_CONNECT_BY_PATH and ORDER SIBLINGS BY, and learn the recursive WITH equivalent you can run here.

PL/SQL Basics

  1. 16 PL/SQL Blocks and Variables Learn the structure of PL/SQL: anonymous blocks with DECLARE, BEGIN, EXCEPTION and END, variables and constants, data types, scope, nested blocks and DBMS_OUTPUT.
  2. 17 PL/SQL Control Flow Control PL/SQL programs with IF, ELSIF and CASE statements, then repeat work with LOOP, WHILE and FOR loops, EXIT, CONTINUE, labels and cursor FOR loops.
  3. 18 SELECT INTO, %TYPE and Records Read table data into PL/SQL variables with SELECT INTO, anchor types with %TYPE and %ROWTYPE, use records, run DML inside blocks and check SQL%ROWCOUNT.
  4. 19 PL/SQL Collections and Bulk Processing Store lists in PL/SQL with associative arrays, nested tables and VARRAYs, use collection methods, and speed up DML with BULK COLLECT and FORALL.

Cursors, Procedures and Functions

  1. 20 Implicit and Explicit Cursors in PL/SQL Learn how PL/SQL cursors work: the implicit SQL cursor, explicit cursors with OPEN, FETCH and CLOSE, cursor attributes, cursor FOR loops and parameters.
  2. 21 REF Cursors and Bulk Processing in PL/SQL Pass query results between PL/SQL and applications with REF CURSOR and SYS_REFCURSOR, then speed up loops with BULK COLLECT, LIMIT and FORALL.
  3. 22 Stored Procedures in Oracle PL/SQL Create, call, change and drop Oracle stored procedures. Learn IN, OUT and IN OUT parameters, default values, named notation, invoker rights and compile errors.
  4. 23 Stored Functions in Oracle PL/SQL Write Oracle stored functions, call them from SQL, and learn the rules: RETURN, DETERMINISTIC, RESULT_CACHE, side effects, table functions and when to pick a function over a procedure.

Packages and Triggers

  1. 24 Oracle PL/SQL Packages: Specification and Body Learn how Oracle packages group procedures, functions, types and constants. Write a specification and body, call public members, hide private code and manage dependencies.
  2. 25 Package State, Overloading and Initialization Understand persistent package state, session scope, overloaded subprograms, the initialization block, SERIALLY_REUSABLE and the ORA-04068 error in Oracle PL/SQL.
  3. 26 Oracle DML Triggers: BEFORE, AFTER, Row and Statement Learn Oracle DML triggers: timing, row-level versus statement-level, :NEW and :OLD, the WHEN clause, audit logging and validation, with live SQLite stand-ins.
  4. 27 INSTEAD OF, Compound and System Triggers in Oracle Make views updatable with INSTEAD OF triggers, avoid the mutating table error with compound triggers, and react to DDL and logon events with system triggers.

Exceptions and Transactions

  1. 28 PL/SQL Exception Handling Basics in Oracle Handle runtime errors in PL/SQL: the EXCEPTION section, predefined exceptions like NO_DATA_FOUND, WHEN OTHERS, SQLCODE and SQLERRM, propagation and nested blocks.
  2. 29 User-Defined Exceptions and Error Logging in PL/SQL Declare your own exceptions, bind Oracle errors with PRAGMA EXCEPTION_INIT, use RAISE_APPLICATION_ERROR, log errors autonomously and collect bulk errors with SAVE EXCEPTIONS.
  3. 30 COMMIT, ROLLBACK and SAVEPOINT in Oracle Learn how Oracle transactions work: implicit starts, COMMIT, ROLLBACK, SAVEPOINT, statement-level atomicity, read consistency, isolation levels and what causes an implicit commit.
  4. 31 Autonomous Transactions and Locking in Oracle Use PRAGMA AUTONOMOUS_TRANSACTION for logging, understand Oracle row locks, SELECT FOR UPDATE with NOWAIT and SKIP LOCKED, optimistic locking and deadlocks.

Performance and Administration

  1. 32 Oracle Execution Plans and Optimizer Statistics Read Oracle execution plans with EXPLAIN PLAN and DBMS_XPLAN, understand full scans, index scans and join methods, and keep optimizer statistics fresh with DBMS_STATS.
  2. 33 Oracle Indexes: B-tree, Bitmap, Function-Based and More Choose the right Oracle index: B-tree, composite, unique, function-based, bitmap and invisible indexes, plus when an index helps, when it hurts and how to monitor it.
  3. 34 Oracle SQL Tuning, Bind Variables and Optimizer Hints Practical Oracle SQL tuning: bind variables and the shared pool, set-based rewrites, sargable predicates, EXISTS versus IN, pagination, optimizer hints and SQL monitoring tools.
  4. 35 Oracle Users, Roles, Privileges and Tablespaces Create Oracle users, grant system and object privileges, build roles, set quotas on tablespaces, and understand the CDB and PDB architecture, profiles and the data dictionary.

Projects and Practice

  1. 36 Project: A Bank Ledger with PL/SQL Build a small double-entry bank ledger in Oracle: tables and constraints, a package with deposit and transfer, an audit trigger, error handling, plus a runnable SQLite version.
  2. 37 Project: Inventory and Order Processing in Oracle Build an order-processing module in Oracle: stock and orders tables, a package that reserves stock safely, a stock-history trigger, a bulk price import with error capture and a report.
  3. 38 Practice: PL/SQL Exercises with Solutions Ten Oracle PL/SQL practice exercises with solutions on cursors, functions, procedures, packages, triggers, exceptions, transactions and tuning, with runnable stand-ins to check your logic.