01_basic_queries.sql

-- SELECT / WHERE / ORDER BY / LIMIT / DISTINCT / LIKE — the fundamentals.
-- Run against the schema in 00_setup_sample_db.sql, e.g.:
--   sqlite3 cookbook.db < sql/01_basic_queries.sql

.headers on
.mode column

-- SELECT specific columns instead of `SELECT *` — cheaper, and makes the
-- query self-documenting about what it actually needs.
SELECT name, tissue FROM samples;

-- WHERE filters rows; string literals use single quotes in SQL, not double.
SELECT chrom, pos, ref, alt, qual
FROM variants
WHERE type = 'SNP' AND qual >= 50
ORDER BY qual DESC;

-- LIMIT caps the number of rows returned — combine with ORDER BY to get a
-- deterministic "top N" instead of an arbitrary N rows.
SELECT chrom, pos, qual
FROM variants
ORDER BY qual DESC
LIMIT 3;

-- DISTINCT removes duplicate rows from the result.
SELECT DISTINCT type FROM variants;

-- LIKE for pattern matching: % matches any run of characters, _ matches
-- exactly one character.
SELECT name FROM samples WHERE name LIKE 'sample_';

-- IN checks membership against a fixed list; BETWEEN checks an inclusive range.
SELECT symbol, chrom FROM genes WHERE symbol IN ('BRCA1', 'TP53');
SELECT chrom, pos FROM variants WHERE pos BETWEEN 43000000 AND 44000000;

-- IS NULL / IS NOT NULL — never use `= NULL`, it always evaluates to
-- neither true nor false (NULL propagates, same idea as R's NA).
SELECT name FROM samples WHERE collected_on IS NOT NULL;