00_setup_sample_db.sql

-- Sample schema + seed data used by every other file in sql/. Small and
-- bioinformatics-flavored on purpose: samples, genes, and the variants
-- observed in each sample, so joins/aggregations/window functions below
-- have something realistic to operate on.
--
-- Build the database with:
--   sqlite3 cookbook.db < sql/00_setup_sample_db.sql
-- (or let bin/run_sql_examples.sh do it for you against a temp file)

DROP TABLE IF EXISTS variants;
DROP TABLE IF EXISTS samples;
DROP TABLE IF EXISTS genes;

CREATE TABLE samples (
    sample_id   INTEGER PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    tissue      TEXT NOT NULL CHECK (tissue IN ('tumor', 'normal')),
    collected_on TEXT NOT NULL  -- ISO date string; SQLite has no native DATE type
);

CREATE TABLE genes (
    gene_id INTEGER PRIMARY KEY,
    symbol  TEXT NOT NULL UNIQUE,
    chrom   TEXT NOT NULL,
    start   INTEGER NOT NULL,
    end     INTEGER NOT NULL
);

CREATE TABLE variants (
    variant_id INTEGER PRIMARY KEY,
    sample_id  INTEGER NOT NULL REFERENCES samples(sample_id),
    gene_id    INTEGER NOT NULL REFERENCES genes(gene_id),
    chrom      TEXT NOT NULL,
    pos        INTEGER NOT NULL,
    ref        TEXT NOT NULL,
    alt        TEXT NOT NULL,
    qual       REAL NOT NULL,
    depth      INTEGER NOT NULL,
    type       TEXT NOT NULL CHECK (type IN ('SNP', 'INDEL'))
);

INSERT INTO samples (sample_id, name, tissue, collected_on) VALUES
    (1, 'sampleA', 'tumor',  '2026-01-10'),
    (2, 'sampleB', 'normal', '2026-01-10'),
    (3, 'sampleC', 'tumor',  '2026-02-03'),
    (4, 'sampleD', 'normal', '2026-02-03');
    -- sampleD deliberately has no variants below, for LEFT JOIN examples.

INSERT INTO genes (gene_id, symbol, chrom, start, end) VALUES
    (1, 'BRCA1', 'chr17', 43044295, 43125483),
    (2, 'TP53',  'chr17', 7661779,  7687550),
    (3, 'EGFR',  'chr7',  55019017, 55211628),
    (4, 'KRAS',  'chr12', 25205246, 25250936);
    -- KRAS deliberately has no variants below, for LEFT JOIN examples.

INSERT INTO variants (variant_id, sample_id, gene_id, chrom, pos, ref, alt, qual, depth, type) VALUES
    (1,  1, 1, 'chr17', 43045000, 'A', 'T',  55.2, 42, 'SNP'),
    (2,  1, 2, 'chr17', 7662500,  'G', 'A',  60.1, 38, 'SNP'),
    (3,  1, 3, 'chr7',  55021000, 'C', 'CA', 30.4, 20, 'INDEL'),
    (4,  2, 1, 'chr17', 43046200, 'A', 'T',  58.7, 45, 'SNP'),
    (5,  2, 2, 'chr17', 7663000,  'T', 'C',  22.5, 15, 'SNP'),
    (6,  3, 1, 'chr17', 43047800, 'A', 'T',  49.9, 33, 'SNP'),
    (7,  3, 3, 'chr7',  55022500, 'G', 'T',  61.0, 40, 'SNP'),
    (8,  3, 3, 'chr7',  55023000, 'T', 'TA', 18.2, 12, 'INDEL'),
    (9,  1, 2, 'chr17', 7664200,  'C', 'CAT', 40.0, 25, 'INDEL');