Appendices: Projects, Interview Questions, Cheat Sheet & Certification

Capstone projects, interview questions, a quick-reference cheat sheet, and the certification path.

Appendix A: Capstone Projects

Project 1: E-Commerce Database (SQL) Build the SHOP schema with constraints, identity columns, comments, and indexes on FKs.

Generate 1M+ rows with CONNECT BY LEVEL and DBMS_RANDOM.

Write 40 analytical queries: top customers, RFM segmentation, cohort retention, YoY growth with LAG, running totals, gaps and islands, PIVOT reports, hierarchical category trees.

Project 2: Order Management Backend (PL/SQL) ORDER_PKG with create, cancel, and ship operations, collections, BULK processing, custom exceptions, and autonomous error logging.

Compound triggers for audit, plus JSON APIs via JSON_OBJECT / JSON_TABLE.

utPLSQL tests at ≥ 80% coverage. Expose REST endpoints through ORDS.

Project 3: Performance Tuning Lab Create skewed data, then reproduce:

Bad cardinality estimates Bind peeking issues Unindexed FK locking

Diagnose with DBMS_XPLAN ALLSTATS LAST, AWR/ASH (or Statspack), and SQL Monitor.

Fix with indexes, histograms, column groups, rewrites, and SPM baselines. Document before and after metrics.

Project 4: Data Warehouse Star schema with interval-partitioned fact tables, bitmap indexes, star transformation, materialized views with query rewrite, external tables for loads, Hybrid Columnar or Advanced compression, In-Memory column store.

An ETL pipeline using MERGE plus DML error logging. SCD Type 2 dimensions.

Project 5: DBA HA/DR Lab Install Oracle on two Linux VMs (or containers). Create a CDB with 2 PDBs.

Configure ARCHIVELOG mode, FRA, and an RMAN strategy (level 0/1, BCT, catalog). Perform:

Datafile loss recovery Control file loss recovery Build a Data Guard physical standby with the Broker. Perform a switchover, failover, and reinstate. Enable Fast-Start Failover.

Clone and relocate PDBs, Data Pump schema refreshes, Scheduler jobs, unified auditing, TDE.

Project 6: Secure Multi-Tenant Application VPD with application contexts, Data Redaction for PII, TDE tablespace encryption, unified audit policies, SQL Firewall (23ai), least-privilege roles with DB_DEVELOPER_ROLE.

Project 7: AI-Powered Semantic Search (23ai/26ai) Load documents, chunk them with DBMS_VECTOR_CHAIN, embed with an in-database ONNX model, create an HNSW vector index, and build a hybrid (Oracle Text + vector) search API plus a RAG prompt pipeline. Optionally use Select AI on Autonomous Database.

Appendix B: Interview Questions

SQL (Beginner → Intermediate) What is the difference between DELETE, TRUNCATE, and DROP in Oracle?

What is the difference between VARCHAR and VARCHAR2? Between CHAR and VARCHAR2?

How does Oracle treat empty strings?

What is DUAL?

What is the difference between ROWNUM and ROW_NUMBER()? Why does WHERE ROWNUM > 1 return no rows?

What is the difference between ROWID and ROWNUM?

Explain NVL, NVL2, COALESCE, NULLIF, and DECODE vs CASE.

What is the difference between UNION and UNION ALL, and between MINUS and INTERSECT?

What is the difference between WHERE and HAVING?

What are the join types? What does (+) mean?

How do you find the Nth highest salary? How do you find duplicates and delete them?

What is a correlated subquery? When is EXISTS better than IN?

Explain CONNECT BY hierarchical queries and LEVEL.

What is the difference between a view and a materialized view? Name the MV refresh types.

What is a sequence? Why do gaps occur?

What is a synonym? Private vs public?

Explain RANK, DENSE_RANK, and ROW_NUMBER, and LAG / LEAD.

What is MERGE?

What are constraint states (ENABLE NOVALIDATE, DEFERRABLE)?

What is a Global Temporary Table vs a Private Temporary Table?

What is the structure of a PL/SQL block?

What are %TYPE and %ROWTYPE?

Implicit vs explicit cursors, and the cursor attributes?

What is a REF cursor? Strong vs weak?

Procedure vs function? Can a function do DML?

What are packages? What are the benefits? What is package state, and what causes ORA-04068?

What is overloading?

Explain exception handling, PRAGMA EXCEPTION_INIT, and RAISE_APPLICATION_ERROR.

What is the mutating table error, and how do you fix it?

What are compound triggers? Row vs statement triggers? INSTEAD OF triggers?

What are the collection types and their differences?

What are BULK COLLECT and FORALL? What does SAVE EXCEPTIONS do?

What is an autonomous transaction? Give a use case.

EXECUTE IMMEDIATE vs DBMS_SQL? How do you prevent SQL injection?

What are pipelined table functions?

What do NOCOPY, DETERMINISTIC, RESULT_CACHE, and PRAGMA UDF do?

What is AUTHID CURRENT_USER vs DEFINER?

What is Edition-Based Redefinition?

DBA and Architecture Explain Oracle's architecture: instance vs database, SGA components, background processes.

What happens when you issue a SELECT? An UPDATE? A COMMIT?

What is the SCN? What is a checkpoint?

Explain redo vs undo.

What is ORA-01555, and how do you resolve it?

Explain the startup stages and shutdown modes.

What is the difference between SPFILE and PFILE?

What are tablespaces, segments, extents, and blocks? What is the HWM?

What is row chaining vs row migration?

ARCHIVELOG vs NOARCHIVELOG mode?

Explain multitenant: CDB, PDB, common vs local users, PDB cloning and plugging.

RMAN: backup sets vs image copies, incremental levels, block change tracking, recovery catalog.

How do you recover from loss of a datafile? A control file? An online redo log group?

What is Flashback? Name the Flashback technologies.

Data Pump vs exp/imp? What are the REMAP_* options and NETWORK_LINK?

What is ASM? What are the redundancy levels?

How do you patch Oracle (RU, OPatch, datapatch)? How do you upgrade (AutoUpgrade)?

Explain the listener, tnsnames.ora, sqlnet.ora, and service registration.

Explain profiles, roles, system vs object privileges, WITH ADMIN vs WITH GRANT OPTION.

What is TDE? VPD? Data Redaction? Unified Auditing? Database Vault?

How do you read an execution plan? What do you look at first?

What is the CBO? What are cardinality and selectivity?

Explain the join methods: nested loops vs hash vs sort merge.

Explain the index types: B-tree vs bitmap, function-based, reverse key, invisible.

When does Oracle not use an index?

What is the clustering factor?

What are histograms, and when are they needed? What are extended statistics?

What is bind peeking and Adaptive Cursor Sharing?

Hard parse vs soft parse? What are the cursor sharing options?

What are AWR, ASH, and ADDM? How do you read an AWR report?

Name common wait events and their causes.

What are SQL Profiles, SQL Plan Baselines, and SQL Patches?

Explain partitioning types, local vs global indexes, and partition pruning.

What is Database In-Memory?

Explain parallel execution and Auto DOP.

How would you troubleshoot a sudden slowdown in production?

High Availability and Cloud Explain RAC architecture, Cache Fusion, SCAN, and services.

What are the RAC wait events, and how do you reduce interconnect traffic?

Explain Data Guard: physical vs logical vs snapshot standby, and the protection modes.

Switchover vs failover? What is Fast-Start Failover?

What is Active Data Guard?

What is GoldenGate, and when would you choose it over Data Guard?

What is the Maximum Availability Architecture?

What is Autonomous Database? How does it differ from a Base DB System?

What are the key new features in 23ai/26ai (vectors, duality views, BOOLEAN, domains, SQL Firewall)?

Classic Query Puzzles

-- 3rd highest salary
SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;
SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) WHERE r = 3 AND ROWNUM = 1;

-- Delete duplicates (keep the lowest ROWID)
DELETE FROM customers a WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM customers b WHERE b.email = a.email);

-- Employees earning more than their manager
SELECT e.last_name FROM employees e JOIN employees m ON m.employee_id = e.manager_id WHERE e.salary > m.salary;

-- Top earner per department
SELECT department_id, MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) top_earner FROM employees GROUP BY department_id;

-- Department-wise cumulative salary
SELECT department_id, last_name, salary,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS UNBOUNDED PRECEDING) cum_sal FROM employees;

-- Even/odd rows
SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY employee_id) rn FROM employees e) WHERE MOD(rn, 2) = 0;

-- Split CSV into rows
SELECT REGEXP_SUBSTR('A,B,C', '[^,]+', 1, LEVEL) val FROM dual CONNECT BY LEVEL <= REGEXP_COUNT('A,B,C', ',') + 1;

-- Missing sequence numb
…

Appendix C: SQL, PL/SQL and DBA Cheat Sheet

-- ===== DDL =====
CREATE TABLE t (id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(50 CHAR) NOT NULL);
ALTER TABLE t ADD (col NUMBER);   ALTER TABLE t MODIFY (col NUMBER(10));   ALTER TABLE t DROP COLUMN col;
CREATE INDEX ix_t_name ON t(name);   DROP INDEX ix_t_name;
TRUNCATE TABLE t;   DROP TABLE t PURGE;   FLASHBACK TABLE t TO BEFORE DROP;
CREATE OR REPLACE VIEW v AS SELECT ...;   CREATE SEQUENCE s;   CREATE SYNONYM sy FOR schema.t;

-- ===== DML / TCL =====
INSERT INTO t (name) VALUES ('a');   UPDATE t SET name = 'b' WHERE id = 1;   DELETE FROM t WHERE id = 1;
MERGE INTO tgt USING src ON (tgt.id = src.id) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;
COMMIT;  ROLLBACK;  SAVEPOINT sp1;  ROLLBACK TO sp1;

-- ===== Query =====
SELECT col, COUNT(*) FROM t WHERE ... GROUP BY col HAVING COUNT(*) > 1 ORDER BY 2 DESC FETCH FIRST 10 ROWS ONLY;
WITH c AS (SELECT ...) SELECT * FROM c;
SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) FROM t;
SELECT LEVEL, name FROM t START WITH mgr IS NULL CONNECT BY PRIOR id = mgr;

-- ===== PL/SQL =====
DECLARE v t.name%TYPE; BEGIN SELECT name INTO v FROM t WHERE id = 1; DBMS_OUTPUT.PUT_LINE(v);
EXCEPTION WHEN NO
…

# RMAN
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;  LIST BACKUP SUMMARY;  RESTORE DATABASE;  RECOVER DATABASE;  DELETE OBSOLETE;
# Data Pump
expdp system DIRECTORY=dp DUMPFILE=x.dmp SCHEMAS=hr ;  impdp system DIRECTORY=dp DUMPFILE=x.dmp REMAP_SCHEMA=hr:hr2
# Net / cluster
lsnrctl status ; tnsping svc ; srvctl status database -d db ; crsctl stat res -t ; dgmgrl / "show configuration"

Appendix D: Certification Path and Resources

Oracle Certifications (verify the current exam numbers on Oracle University; they change) 1Z0-071: Oracle Database SQL Oracle Database SQL Certified Associate

1Z0-149: Oracle Database Program with PL/SQL Oracle PL/SQL Developer Certified Professional

1Z0-082 + 1Z0-083 (19c) / 1Z0-182 + 1Z0-183 (23ai)

Oracle Database Administrator Certified Professional 1Z0-084: Performance and Tuning Management

Performance Management and Tuning Certified Professional 1Z0-931: Autonomous Database Cloud Autonomous Database Cloud Professional

1Z0-1085 / 1Z0-1072: OCI Foundations / Architect OCI Generative AI / AI Vector Search Professional APEX Developer Professional

Oracle Help Center (docs.oracle.com): Concepts Guide, SQL Language Reference, PL/SQL Language Reference, Administrator's Guide, Performance Tuning Guide, SQL Tuning Guide, Backup and Recovery User's Guide, Data Guard Concepts, RAC Administration, Security Guide, New Features Guide, Licensing Information

Oracle LiveLabs (free hands-on workshops), FreeSQL / Live SQL, Oracle Dev Gym, Oracle University (free learning paths for some certifications)

Ask TOM (asktom.oracle.com), the Oracle Developers YouTube channel, and the Oracle Database Insider and Oracle Developers blogs

My Oracle Support (support.oracle.com) for licensed customers

*Oracle Database Concepts* (official): read it first *Expert Oracle Database Architecture*: Thomas Kyte and Darl Kuhn

*Oracle PL/SQL Programming*: Steven Feuerstein *Pro Oracle SQL Development*: Jon Heller

*Troubleshooting Oracle Performance*: Christian Antognini *Cost-Based Oracle Fundamentals* and *Oracle Core*: Jonathan Lewis

*Oracle Database 19c/23ai Performance Tuning Recipes*: Kuhn, Alapati, Padfield

*RMAN Recipes / Oracle RMAN Pocket Reference* *Pro Oracle Database 18c/23ai Administration*: Michelle Malcher and Darl Kuhn

*Expert Oracle RAC*: Riyaj Shamsudeen et al.

*Oracle Data Guard Handbook*: Larry Carpenter et al.

Blogs and experts: Jonathan Lewis, Tanel Poder (Snapper, session tools), Connor McDonald, Chris Saxon, Tim Hall (ORACLE-BASE.com, an excellent how-to reference), Franck Pachot, Mike Dietrich (upgrades), Maria Colgan (optimizer and features), Richard Foote (indexes), Carlos Sierra (SQLT), Frits Hoogland

Forums: Oracle Forums, DBA Stack Exchange, r/oracle User groups: IOUG, UKOUG, AIOUG (India), DOAG, RMOUG; Oracle CloudWorld / Oracle AI World

Free tools: SQLT (SQLTXPLAIN), Tanel Poder's scripts, Logger, utPLSQL, Ansible collections for Oracle Suggested 14-Week Study Plan

Concepts, setup, architecture overview, data types DDL, DML, SELECT, single-row functions

Aggregation, joins, set operators, subqueries WITH, hierarchies, analytic functions, PIVOT/MODEL/MATCH_RECOGNIZE, views and MVs

Transactions, read consistency, locking, design PL/SQL basics, cursors, exceptions, procedures, packages

Triggers, collections, bulk processing, dynamic SQL, advanced PL/SQL, built-in packages

JSON/XML, spatial/graph/text/vector, Flashback, external data

Startup/shutdown, storage, redo/undo, Oracle Net, multitenant

Security, RMAN, Data Pump, Scheduler, ASM, patching/upgrades

Indexing, optimizer, statistics, SQL tuning AWR/ASH/ADDM, memory, partitioning, compression and In-Memory, parallel, Resource Manager

RAC, Data Guard, GoldenGate, MAA, Exadata, Cloud/ADB 63–66 + Capstone

New features, APEX/ORDS, monitoring, DevOps, best practices Final tip: Read the *Oracle Database Concepts* guide, build a lab and break it on purpose (delete datafiles, kill instances, corrupt blocks, then recover), read execution plans with ALLSTATS LAST for every slow query, and practice daily on Live SQL or your own 23ai Free container. Happy learning! 🚀