SQLite Virtual Tables u0026 FTS5 Fundamentals Quiz Quiz

Explore essential concepts of SQLite virtual tables and FTS5 full-text search in this quiz. Enhance your understanding of creating, querying, and managing virtual tables and FTS5 indexes with practical scenarios and basic explanations.

1. Purpose of a Virtual Table

   What is the main purpose of a virtual table in SQLite?

   1. To provide a table interface to data not stored in the database file
   2. To automatically encrypt all stored columns
   3. To create duplicate tables for backup purposes
   4. To store large binary files in a database

   Explanation: A virtual table lets SQLite access data that isn’t stored in its internal database file, such as data from an external file or computation. The other options are incorrect because virtual tables don’t directly store files, encrypt data automatically, or serve as backup tools. Their purpose is to abstract non-database data sources within the familiar table structure.

2. Using FTS5 for Text Search

   Which statement describes a key benefit of using FTS5 in SQLite for text search operations?

   1. FTS5 automatically translates queries to every language
   2. FTS5 provides optimized full-text search with features like stemming and ranking
   3. FTS5 compresses all database data for faster storage
   4. FTS5 performs only numerical calculations on text columns

   Explanation: FTS5 is designed specifically for efficient full-text searches, enabling features like word stemming and result ranking. It does not compress data, translate queries, or focus on numerical calculations, as mentioned in the other distractors. FTS5 extends search capabilities beyond ordinary LIKE queries.

3. Creating a Virtual Table with FTS5

   Which command starts the creation of an FTS5 virtual table for storing documents with 'title' and 'body' columns?

   1. ADD VIRTUAL docs TO fts5(title, body);
   2. CREATE TABLE docs FTS5(title, body);
   3. CREATE VIRTUAL TABLE docs USING fts5(title, body);
   4. CREATE INDEX docs WITH fts5(title, body);

   Explanation: The correct syntax to create an FTS5 table specifies 'CREATE VIRTUAL TABLE' followed by 'USING fts5' and your desired columns. The other options contain incorrect SQL syntax, such as missing keywords, invalid ordering, or misuse of CREATE INDEX. Only the first option matches SQLite's requirements for FTS5.

4. Default Tokenizer Usage

   Which tokenizer is used by default in an FTS5 table if none is specified?

   1. english
   2. porter
   3. unicode61
   4. simple

   Explanation: The default FTS5 tokenizer is 'unicode61', supporting a wide range of Unicode text. The 'simple' and 'porter' tokenizers must be chosen explicitly, and 'english' is not a recognized tokenizer in SQLite. This ensures robust multilingual support straight away.

5. Querying FTS5 for Matches

   When searching for rows where the 'body' column includes the word 'science', which clause should be used with an FTS5 table named 'articles'?

   1. SELECT * FROM articles WHERE body LIKE '%science%';
   2. SELECT * FROM articles WHERE articles MATCH 'science';
   3. SELECT * FROM articles WHERE articles CONTAINS 'science';
   4. SELECT * FROM articles WHERE MATCH 'science';

   Explanation: With FTS5, the correct way to perform a full-text search is using the 'MATCH' operator applied to the table name, not the column. The LIKE operator performs basic pattern matching, not full-text search, while the 'CONTAINS' clause and 'MATCH' applied only to the column are not valid in SQLite queries.

6. Automatic Column Storage in FTS5

   What happens if you declare a column in an FTS5 table as 'UNINDEXED'?

   1. The column is automatically encrypted
   2. The column cannot be inserted or updated
   3. The column stores only integer values
   4. The column stores text but is not searchable by the full-text engine

   Explanation: 'UNINDEXED' columns retain their text values but are not included in the full-text search index, meaning searches ignore them. They can still be inserted and updated. The other options falsely claim automatic encryption, limitations on value types, or restrictions on standard data operations.

7. Dropping an FTS5 Virtual Table

   Which command deletes an existing FTS5 virtual table named 'docs' from a database?

   1. DELETE VIRTUAL docs;
   2. DROP VIRTUAL TABLE docs;
   3. REMOVE FTS5 docs;
   4. DROP TABLE docs;

   Explanation: Despite being a virtual table, you use the standard 'DROP TABLE' syntax to remove it in SQLite. The other options are incorrect because SQLite does not require using 'VIRTUAL' or 'FTS5' keywords in the DROP TABLE command, and 'DELETE' or 'REMOVE' commands are not valid for dropping tables.

8. FTS5 Search Query Boosting

   Which way can you influence FTS5 search results to give more weight to matches in the 'title' column over the 'body' column?

   1. Use the bm25() ranking function with a higher weight for 'title'
   2. Set the column as PRIMARY KEY
   3. Convert 'title' to uppercase
   4. Add an INDEX to the 'title' column

   Explanation: The bm25() function provides advanced ranking, and you can increase the importance of one column by assigning it a larger weight during ranking. Setting PRIMARY KEY or INDEX is not relevant for FTS5 ranking, and text case does not affect boost. Only the first option directly influences search ranking.

9. FTS5 Rowid Column

   In every FTS5 virtual table, which implicit column uniquely identifies each row and can be used in queries?

   1. id_col
   2. rowid
   3. idx
   4. doc_id

   Explanation: By default, every SQLite table, including virtual ones like FTS5 tables, provides a 'rowid' as a unique identifier. The other options, like 'doc_id', may sound similar but are not present unless explicitly added. 'rowid' enables fast lookups and internal tracking.

10. FTS5 vs. Standard Tables

    How is an FTS5 virtual table different from a standard SQLite table?

    1. It always enforces foreign keys
    2. It is stored outside the database file
    3. It only supports integer values
    4. It indexes all text columns for full-text search and does not support all regular SQL constraints

    Explanation: FTS5 tables automatically index their text columns for fast text searches and may not support features like foreign keys or UNIQUE constraints. They can store text and are located in the database file, so the other options are misleading. Only the correct option covers both key FTS5 traits.