CREATE DATABASE, CREATE SCHEMA, and Namespaces

CREATE DATABASE academy    WITH OWNER = academy_owner         ENCODING = 'UTF8'         TEMPLATE = template0;-- Connect to the new database before creating its schemas.-- In psql: \connect academy

CREATE SCHEMA admissions AUTHORIZATION academy_owner;CREATE SCHEMA learning   AUTHORIZATION academy_owner;CREATE SCHEMA reporting  AUTHORIZATION reporting_owner;CREATE TABLE learning.course (    course_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    course_code text NOT NULL UNIQUE,    title text NOT NULL);CREATE VIEW reporting.course_catalog ASSELECT course_id, course_code, titleFROM learning.course;

SELECT course_id, titleFROM learning.course;SET search_path = reporting, learning, pg_catalog;-- Now course_catalog resolves through reporting first.SELECT * FROM course_catalog;

-- The opened file is qualified as main.CREATE TABLE main.course (    course_id   INTEGER PRIMARY KEY,    course_code TEXT NOT NULL UNIQUE,    title       TEXT NOT NULL) STRICT;-- TEMP objects live in the connection-local temp database.CREATE TEMP TABLE import_course (    course_code TEXT,    title       TEXT);-- Attach another file under a chosen namespace.ATTACH DATABASE 'analytics.db' AS analytics;CREATE TABLE analytics.course_snapshot ASSELECT course_id, course_code, titleFROM main.course;PRAGMA database_list;

PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS active_course_catalog;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS department;CREATE TABLE department (    department_id INTEGER PRIMARY KEY,    code          TEXT NOT NULL UNIQUE,    name          TEXT NOT NULL UNIQUE,    budget_cents  INTEGER NOT NULL DEFAULT 0 CHECK (budget_cents >= 0),    created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE instructor (    instructor_id INTEGER PRIMARY KEY,    department_id INTEGER NOT NULL REFERENCES department(department_id),    email         TEXT NOT NULL UNIQUE,    full_name     TEXT NOT NULL,    hired_on      TEXT NOT NULL CHECK (date(hired_on) IS NOT NULL),    active        INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))) STRICT;CREATE TABLE course (    course_id      INTEGER PRIMARY KEY,    department_id  INTEGER NOT NULL REFERENCES department(department_id),    instructor_id  INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL,    course_code    TEXT NOT NULL,    title          TEXT NOT NULL,    credits        INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6),    capacity       INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0),    published      INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)),    display_name   TEXT GENERATED ALWAYS AS (course_code || ' · ' || title) VIRTUAL,    created_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    UNIQUE (department_id, course_code)) STRICT;CREATE TABLE enrollment (    course_id   INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE,    student_id  INTEGER NOT NULL,    enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,    status      TEXT NOT NULL DEFAULT 'enrolled'                CHECK (status IN ('enrolled', 'completed', 'withdrawn')),    PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;CREATE VIEW active_course_catalog ASSELECT    c.course_id,    d.code AS department_code,    c.course_code,    c.title,    c.credits,    c.capacityFROM course AS cJOIN department AS d ON d.department_id = c.department_idWHERE c.published = 1;INSERT INTO department (department_id, code, name, budget_cents) VALUES    (1, 'DATA', 'Data Engineering', 25000000),    (2, 'CS',   'Computer Science', 30000000);INSERT INTO instructor    (instructor_id, department_id, email, full_name, hired_on)VALUES    (10, 1, 'nadia@example.edu', 'Nadia Rahimi', '2024-09-01'),    (11, 2, 'omar@example.edu',  'Omar Haddad',  '2023-02-15');INSERT INTO course    (course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES    (100, 1, 10, 'SQL-101', 'SQL Foundations', 3, 40, 1),    (101, 1, 10, 'DE-201',  'Data Pipelines',  4, 30, 1),    (102, 2, 11, 'DB-220',  'Database Systems',4, 35, 0);INSERT INTO enrollment (course_id, student_id, status) VALUES    (100, 1001, 'enrolled'),    (100, 1002, 'completed'),    (101, 1001, 'enrolled');

ATTACH DATABASE ':memory:' AS reporting;CREATE TABLE reporting.course_metrics (    course_id        INTEGER PRIMARY KEY,    enrolled_count   INTEGER NOT NULL,    completed_count  INTEGER NOT NULL,    refreshed_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO reporting.course_metrics (    course_id,    enrolled_count,    completed_count)SELECT    c.course_id,    COUNT(e.student_id),    SUM(CASE WHEN e.status = 'completed' THEN 1 ELSE 0 END)FROM main.course AS cLEFT JOIN main.enrollment AS e ON e.course_id = c.course_idGROUP BY c.course_id;SELECT * FROM reporting.course_metrics ORDER BY course_id;DETACH DATABASE reporting;