Oracle SQL & PL/SQL Labs

A set of database labs built with Oracle SQL and PL/SQL, covering schema design, integrity constraints, triggers, transaction control, and cursor-based procedural logic.

Each lab is self-contained: a runnable .sql script plus a Markdown write-up explaining the reasoning behind each step. The write-ups are in French (original coursework language); the code is standard Oracle SQL/PL-SQL and is readable regardless of language.

Skills demonstrated

• Schema design — primary/foreign keys, CHECK constraints, self-referencing foreign keys
• DML & querying — joins (including self-joins), correlated/non-correlated subqueries, aggregates, GROUP BY, CTEs
• Transaction control — COMMIT / ROLLBACK, understanding transaction scope
• Triggers — row-level validation (BEFORE INSERT), audit counters via MERGE
• PL/SQL procedural code — explicit and implicit cursors, %ROWTYPE, %ROWCOUNT, control flow, exception handling (RAISE_APPLICATION_ERROR)
• Database administration — user/schema creation, privilege grants, cross-schema views

Labs

Lab	Topic	Files
tp1-integrity-constraints/	Table creation with PK/FK/CHECK constraints, INSERT/UPDATE/DELETE/ROLLBACK/COMMIT, and 17 SELECT queries (joins, subqueries, aggregates)	tp1.sql, rapport.md
tp2-triggers/	BEFORE INSERT triggers for business-rule validation (postal code, spouse-name consistency) on a CLIENT/COMPTE/DETENTEUR schema	tp2.sql, tp2-alt-trigger.sql, rapport.md
tp3-joins-views-users/	Transaction/isolation behavior, a PIECE/SERVICE/COMMANDE schema (joins, subqueries, ALL), update-counting triggers (MERGE), multi-user Oracle setup, and cross-schema views	tp3.sql
tp4-plsql-cursors-views/	Explicit (OPEN/FETCH/CLOSE) and implicit (FOR) PL/SQL cursors, in-procedure filtering/aggregation, updatable views	tp4.sql, rapport.md

Requirements

Written for Oracle Database (SQL*Plus / PL-SQL). A few statements (CREATE USER, GRANT, ALL_TABLES) require DBA privileges and aren't portable to other RDBMS without adaptation.

Notes

• Some scripts intentionally include queries that fail (constraint violations, duplicate keys) to demonstrate integrity-constraint behavior — see the -- erreur comments in the code.
• tp2-alt-trigger.sql is an alternate version of the postal-code validation trigger, using numeric ranges instead of a string whitelist.

Author

Aya Dalache