Skip to content

README.md

Latest commit

 

History

History
78 lines (55 loc) · 4.61 KB

README.md

File metadata and controls

78 lines (55 loc) · 4.61 KB
Raw

🗄️ Advanced SQL / PL-SQL & NoSQL Labs

A collection of database labs (TPs) covering advanced SQL/PL-SQL (Oracle) — DDL & constraints, triggers, stored procedures, views, object-relational modeling (REF/DEREF, type inheritance) — plus a NoSQL/MongoDB series (CRUD, embedded documents, aggregation pipelines) for comparison.

The focus of this repo is advanced SQL / data-analysis-oriented database work: enforcing business rules in the database layer (triggers), encapsulating logic (stored procedures/functions), analytical queries (JOINs, GROUP BY, aggregation pipelines), and modeling complex/nested data both relationally (object-relational) and in a document store (MongoDB).

📁 Structure

.
├── plsql-oracle/
│   ├── 01-ddl-constraints/
│   │   ├── ex1_ddl_constraints.sql              # PK/FK/CHECK, ALTER TABLE, DROP CASCADE
│   │   └── ex2_cinema_schema.sql                # multi-table schema, ON DELETE CASCADE
│   ├── 02-triggers-procedures/
│   │   ├── ex1_trigger_salary_log.sql           # BEFORE UPDATE trigger, audit log
│   │   ├── ex2_trigger_capacity_check.sql       # BEFORE INSERT + business rule validation
│   │   ├── ex3_disjoint_subtype_procedures_views.sql  # disjoint-subtype triggers, procedures, views
│   │   └── ex4_drone_missions_view_function_trigger.sql # view + function + range-check trigger
│   └── 03-object-relational/
│       ├── ex1_type_hierarchy_treat.sql         # type inheritance (UNDER), TREAT, VALUE()
│       ├── ex2_client_commande_refs.sql         # REF/DEREF object-relational model
│       └── ex2b_client_commande_refs_extended.sql # + CARDINALITY, TABLE() unnesting, ALTER TYPE
├── mongodb-nosql/
│   ├── ex1_crud_etudiant.js                     # basic CRUD, regex queries, sort
│   ├── ex2_nested_documents_personne.js         # embedded documents/arrays vs. joined tables
│   └── ex3_aggregation_student_scores.js        # $group/$match/$project/$avg pipelines
├── basic-sql/
│   └── users_orders_join.sql                    # simple schema + JOIN
└── LICENSE

🧩 PL/SQL — Oracle (plsql-oracle/)

Each script is self-contained: it creates a fresh Oracle user (CREATE USER c##...), grants privileges, connects as that user, then builds and exercises the schema. Run with SQL*Plus or SQL Developer as a DBA-privileged account.

sqlplus sys/your_password@XEPDB1 as sysdba @plsql-oracle/01-ddl-constraints/ex1_ddl_constraints.sql

Highlights:

  • Triggers — BEFORE UPDATE/BEFORE INSERT row-level triggers, :OLD/:NEW pseudo-records, RAISE_APPLICATION_ERROR for enforcing business rules directly in the database (capacity limits, disjoint subtypes, valid altitude ranges, uniqueness across a nested table).
  • Stored procedures & functions — encapsulating multi-table inserts and reusable calculations, with exception handling (NO_DATA_FOUND).
  • Views — including views built on top of other views for layered aggregation.
  • Object-relational features — OBJECT types, type hierarchies (UNDER, NOT FINAL, TREAT), REF/DEREF pointers between object tables, nested tables of REF, CARDINALITY(), TABLE() collection unnesting, and ALTER TYPE ... ADD ATTRIBUTE ... CASCADE for schema evolution.

Note: 02-triggers-procedures/ex3_disjoint_subtype_procedures_views.sql had a stray leftover token before the CREATE OR REPLACE VIEW statement in the original script (would have caused an ORA error) — it's been cleaned up here so the script runs top to bottom.

🍃 MongoDB / NoSQL (mongodb-nosql/)

Run with mongosh. Exercise 3 requires importing an external dataset first (see the header comment in that file for the mongoimport command).

Highlights:

  • CRUD basics, regex pattern matching, sorting, updateOne with $set.
  • Modeling a relationship (person → follows → writes → tags) as embedded documents/arrays instead of separate joined tables — the NoSQL counterpart to the relational REF/nested-table modeling done in the object-relational PL/SQL exercises.
  • Aggregation framework: $group, $match, $project, $avg across multiple fields, $addFields, multi-key (gender + career) grouping — the MongoDB equivalent of GROUP BY + aggregate functions.

🔧 Basic SQL (basic-sql/)

A minimal two-table schema with a JOIN, kept as a quick reference example.

🧰 Tech stack

Oracle PL/SQL (SQL*Plus / SQL Developer) · MongoDB (mongosh, mongoimport)

📄 License

MIT — see LICENSE.