{"title":"## Advanced SQLite Features","description":"**A hard quiz covering advanced SQLite features:**\n- WAL mode\n- FTS5 full-text search\n- JSON1\n- recursive CTEs\n- window functions\n- virtual tables\n- UPSERT\n- STRICT tables\n- foreign keys\n- and the RETURNING clause\n\nDesigned for developers who *think* they know SQLite.","settings":{"shuffle_questions":true,"shuffle_options":true,"reveal_answers":true,"allow_review":true,"time_limit_seconds":600,"pass_percent":70},"questions":[{"kind":"single_choice","prompt":"What does `PRAGMA journal_mode = WAL` enable in SQLite?","explanation":"WAL (Write-Ahead Logging) mode allows multiple concurrent readers to proceed simultaneously while a single writer appends to a separate .wal file. In the default rollback journal mode, a write blocks all readers. WAL is effectively mandatory for any production SQLite deployment serving concurrent connections.","points":1,"options":[{"label":"Enables foreign key constraint checking"},{"label":"Allows concurrent readers during writes"},{"label":"Encrypts the database file at rest"},{"label":"Splits the database into multiple files for performance"}]},{"kind":"single_choice","prompt":"Which operator is used to perform full-text search queries on an FTS5 virtual table?","explanation":"The MATCH operator (or equivalently the = operator on the table-valued column) triggers the FTS5 inverted index. Unlike LIKE or GLOB, MATCH uses the full-text index directly and supports phrase queries, prefix search (with *), boolean AND/OR/NOT, the NEAR operator, column filters, and BM25 relevance ranking.","points":1,"options":[{"label":"CONTAINS"},{"label":"MATCH"},{"label":"SEARCH"},{"label":"FIND"}]},{"kind":"single_choice","prompt":"Which SQLite function extracts a nested value from a JSON string stored in a TEXT column?","explanation":"`json_extract(data, '$.user.name')` returns the value at the given JSON path. SQLite has no native JSON column type - JSON is stored as TEXT and queried with the JSON1 extension functions. The `->` and `->>` operators (3.38+) are syntactic sugar for json_extract.","points":1,"options":[{"label":"json_get()"},{"label":"json_extract()"},{"label":"json_value()"},{"label":"json_query()"}]},{"kind":"single_choice","prompt":"Which keyword is required to write a recursive Common Table Expression (CTE) in SQLite?","explanation":"`WITH RECURSIVE` is required for recursive CTEs. Without the RECURSIVE keyword, SQLite only allows non-recursive CTEs (which are essentially named subqueries). Recursive CTEs consist of an anchor member (non-recursive initial SELECT) and a recursive member (SELECT that references the CTE itself), separated by UNION ALL.","points":1,"options":[{"label":"LOOP"},{"label":"RECURSIVE"},{"label":"ITERATE"},{"label":"REPEAT"}]},{"kind":"single_choice","prompt":"What does `RANK() OVER (PARTITION BY department ORDER BY salary DESC)` calculate?","explanation":"This window function assigns a rank to each employee row within their department, ordered by salary descending. The PARTITION BY clause resets the ranking for each department, while ORDER BY DESC means the highest salary gets rank 1. Ties receive the same rank, leaving gaps (unlike DENSE_RANK which does not gap).","points":1,"options":[{"label":"The total salary per department"},{"label":"A rank within each department, sorted by salary descending, with gaps for ties"},{"label":"The moving average of salaries across all departments"},{"label":"The cumulative sum of salaries without partitioning"}]},{"kind":"single_choice","prompt":"What is a virtual table in SQLite?","explanation":"A virtual table is backed by user-code callbacks (a 'module') rather than the built-in b-tree storage engine. To SQL, it looks and behaves like a regular table (SELECT, INSERT, UPDATE, DELETE, JOIN all work), but the module implements xCreate, xBestIndex, xFilter, xNext, etc. FTS5, R*Tree, json_each, dbstat, and csv are all shipped virtual table modules.","points":1,"options":[{"label":"A temporary table that exists only for the current session"},{"label":"A table backed by user-code callbacks instead of b-tree storage"},{"label":"A VIEW that caches its query results"},{"label":"A table with no persistent storage that always returns zero rows"}]},{"kind":"single_choice","prompt":"What syntax does SQLite use to handle conflicts during INSERT (upsert)?","explanation":"SQLite uses `INSERT ... ON CONFLICT(col) DO UPDATE SET col=excluded.col`. The `excluded.` prefix refers to the values that were rejected by the conflict. Unlike MySQL's `ON DUPLICATE KEY UPDATE` or standard SQL's `MERGE`, SQLite's syntax is column-specific - you can target a specific constraint with `ON CONFLICT ON CONSTRAINT name`. The DO NOTHING variant silently skips conflicting rows.","points":1,"options":[{"label":"ON DUPLICATE KEY UPDATE (MySQL syntax)"},{"label":"ON CONFLICT ... DO UPDATE SET"},{"label":"MERGE WHEN MATCHED THEN UPDATE"},{"label":"REPLACE INTO ... VALUES"}]},{"kind":"single_choice","prompt":"What must a developer do to ensure foreign key constraints are enforced in SQLite?","explanation":"Foreign key constraints must be enabled per-connection with `PRAGMA foreign_keys = ON`. SQLite disables FK enforcement by default for backwards compatibility with legacy databases created before FK support was added (3.6.19). Even if you declare FOREIGN KEY in CREATE TABLE, without this pragma, violations are silently ignored. This is the single most common SQLite pitfall.","points":1,"options":[{"label":"Simply declaring FOREIGN KEY in CREATE TABLE is sufficient"},{"label":"Enable PRAGMA foreign_keys=ON on every new connection"},{"label":"Foreign keys are always enforced automatically"},{"label":"Run PRAGMA foreign_keys=ON once when the database is created"}]},{"kind":"single_choice","prompt":"What does a STRICT table (SQLite 3.37+) enforce that a regular table does not?","explanation":"STRICT tables enforce type checking per column, rejecting values whose type does not match the declared type. In a regular SQLite table, affinity is just a hint - you can insert the string 'hello' into an INTEGER column and SQLite stores it. STRICT tables raise an error instead. They also reject booleans, dates, and unrecognized types, allowing only INTEGER, REAL, TEXT, BLOB, and ANY.","points":1,"options":[{"label":"STRICT tables require a primary key on every table"},{"label":"STRICT tables enforce column type checking, rejecting type mismatches"},{"label":"STRICT tables prevent NULL values in all columns"},{"label":"STRICT tables disallow ALTER TABLE statements entirely"}]},{"kind":"single_choice","prompt":"What does the RETURNING clause (SQLite 3.35+) do?","explanation":"The RETURNING clause returns the modified row(s) directly from an INSERT, UPDATE, or DELETE statement, eliminating the need for a separate SELECT after a write. It supports `RETURNING *` (all columns) or specific column expressions. This is particularly valuable in web APIs where you need to return the created/updated record to the client without a separate round-trip query.","points":1,"options":[{"label":"Rolls back the transaction if a condition is not met"},{"label":"Returns the modified row(s) from INSERT, UPDATE, or DELETE"},{"label":"Returns only the total number of affected rows"},{"label":"Disables triggers for the duration of the statement"}]}]}