SQLite Example Scripts for “3-Minute Quiz”

This section contains the create, insert and select statements for the “Test your SQL Know-How in 3 Minutes” test. You may want to test yourself before reading this page.

The create and insert statements are available in the example schema archive.

The execution plans are formatted for better readability.

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT COUNT(*)
  FROM tbl
 WHERE STRFTIME ('%Y', date_column) = '2024';

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= '2024-01-01'
   AND date_column <  '2018-01-01';

The first execution plan performs a full index scan (SCAN TABLE). The second execution plan, on the other hand, uses the index properly (SEARCH is the important part here).

0|0|0|SCAN TABLE tbl USING COVERING INDEX tbl_idx

0|0|0|SEARCH TABLE tbl USING COVERING INDEX tbl_idx 
                  (date_column>? AND date_column<?)

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1;

The query uses the index and no SORT operation.

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=?)

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

DROP INDEX tbl_idx ;

CREATE INDEX tbl_idx ON tbl (b, a);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

The first query can use both indexes efficiently:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=? AND b=?)

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (b=? AND a=?)

The second query cannot use the first index and reads the entire table (SCAN TABLE) instead:

0|0|0|SCAN TABLE tbl

Changing the column order allows the second query to use the index too:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (b=?)

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);

PRAGMA case_sensitive_like = true;

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%';

The query can use the index efficiently.

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (text>? AND text<?)

Note that this example requires the above pragma setting: otherwise, like works case-insensitive in SQLite and cannot use this index for this query.

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column;

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column;

Both indexes are used, of course. The difference is that the first query doesn’t access the table (COVERING), so the first query is much faster.

0|0|0|SEARCH TABLE tbl USING COVERING INDEX tbl_idx (a=?)

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=?)

The second query must be considerably slower because every row needs a table access — also for those that are filtered by the new condition. Even if the index has a low clustering factor, it is still about twice as many blocks to read.