SQLite PRAGMA Commands: In-Depth Knowledge Check Quiz

Explore key concepts and usage of SQLite PRAGMA commands with this quiz, designed to enhance your understanding of configuration, diagnostics, and database management query options. Ideal for users aiming to solidify foundational knowledge about SQLite PRAGMA statements, their effects, and common use cases.

1. Effect of PRAGMA on Foreign Keys

   Which PRAGMA command enables or disables enforcement of foreign key constraints in an SQLite database?

   1. PRAGMA foreign_keys
   2. PRAGMA cache_size
   3. PRAGMA page_size
   4. PRAGMA index_list

   Explanation: PRAGMA foreign_keys is used to enable or disable the enforcement of foreign key constraints in an SQLite database. PRAGMA index_list is used to list indexes for a specific table, not to control foreign keys. PRAGMA page_size sets the size of the database page, and PRAGMA cache_size configures the number of pages held in memory cache. Only PRAGMA foreign_keys is relevant for this specific function.

2. Querying Table Schema

   What is the correct PRAGMA statement to retrieve the schema details of a table named 'users'?

   1. PRAGMA table_info('users')
   2. PRAGMA table_schema('users')
   3. PRAGMA schema_info('users')
   4. PRAGMA user_info('table')

   Explanation: PRAGMA table_info('users') returns detailed information about the columns in the 'users' table, such as name, type, and whether the column is a primary key. PRAGMA table_schema and PRAGMA schema_info are not valid SQLite PRAGMAs. PRAGMA user_info('table') is an incorrect reference and does not exist in SQLite.

3. Modifying Auto-Vacuum Mode

   Which PRAGMA command is used to set or check the database's automatic vacuuming behavior?

   1. PRAGMA vacuum_settings
   2. PRAGMA auto_vac
   3. PRAGMA auto_vacuum
   4. PRAGMA vacuum_mode

   Explanation: PRAGMA auto_vacuum allows users to read or set the database's auto-vacuum mode, which affects how deleted space is reclaimed. PRAGMA vacuum_settings and PRAGMA vacuum_mode are common misinterpretations but are not actual SQLite PRAGMAs. PRAGMA auto_vac is a typo and not recognized. Only PRAGMA auto_vacuum is valid.

4. Temporary Storage Location

   To control whether SQLite uses memory or disk for temporary storage by default, which PRAGMA should be used?

   1. PRAGMA temp_storage
   2. PRAGMA temp_store
   3. PRAGMA memory_mode
   4. PRAGMA store_temp

   Explanation: PRAGMA temp_store allows you to set if temporary storage is managed in memory or on disk. PRAGMA store_temp and PRAGMA temp_storage look similar but are not real PRAGMA commands in SQLite. PRAGMA memory_mode does not exist for controlling temp storage. Only PRAGMA temp_store is designed for this setting.

5. Getting List of Indexes

   How can you obtain a list of all indexes associated with a table named 'orders' using a PRAGMA command?

   1. PRAGMA indexes('orders')
   2. PRAGMA index_list('orders')
   3. PRAGMA table_indexes('orders')
   4. PRAGMA indexinfo('orders')

   Explanation: PRAGMA index_list('orders') gives you all indexes related to the 'orders' table. PRAGMA indexes and PRAGMA table_indexes are common misconceptions and invalid commands in SQLite. PRAGMA indexinfo is used to get detailed info about a specific index but does not list all indexes.

6. Checking Database Integrity

   Which PRAGMA command is specifically designed to check for database corruption or integrity issues?

   1. PRAGMA database_check
   2. PRAGMA file_integrity
   3. PRAGMA integrity_check
   4. PRAGMA check_integrity

   Explanation: PRAGMA integrity_check runs a thorough check to detect corruption or integrity errors in the database. PRAGMA check_integrity and PRAGMA database_check are misleading and do not exist in SQLite. PRAGMA file_integrity is not a valid SQLite command for this purpose.

7. Setting Database Journal Mode

   If you want to configure the journal mode (e.g., DELETE, WAL) for an SQLite database, which PRAGMA should you use?

   1. PRAGMA set_journal
   2. PRAGMA journal_settings
   3. PRAGMA journal_mode
   4. PRAGMA mode_journal

   Explanation: PRAGMA journal_mode allows users to set or query the journal mode for a database, affecting how transactions are logged. PRAGMA mode_journal and PRAGMA journal_settings might look correct due to similar naming but are not valid SQLite PRAGMA commands. PRAGMA set_journal is also incorrect.

8. Controlling Query Planner Behavior

   Which PRAGMA statement can disable or enable SQLite's automatic query planner functionality?

   1. PRAGMA index_auto
   2. PRAGMA planner_mode
   3. PRAGMA query_planner
   4. PRAGMA automatic_index

   Explanation: PRAGMA automatic_index controls whether or not SQLite will automatically create temporary indexes to assist query planning. PRAGMA planner_mode and PRAGMA query_planner seem reasonable but are not real PRAGMAs. PRAGMA index_auto is a misnamed variant and not supported.

9. Tracking Write-Ahead Logging

   To check if Write-Ahead Logging (WAL) is active, which PRAGMA command can be used?

   1. PRAGMA logging_mode
   2. PRAGMA write_ahead
   3. PRAGMA journal_mode
   4. PRAGMA wal_status

   Explanation: PRAGMA journal_mode will show the current journal mode, such as WAL, so it's the right way to determine if Write-Ahead Logging is active. PRAGMA wal_status and PRAGMA write_ahead are not valid commands, while PRAGMA logging_mode is a plausible but incorrect option. Only PRAGMA journal_mode provides this information.

10. Obtaining SQLite Version

    What PRAGMA statement returns the version of the SQLite library in use?

    1. PRAGMA library_version
    2. PRAGMA db_version
    3. PRAGMA sqlite_version
    4. PRAGMA version

    Explanation: PRAGMA library_version returns the version of the SQLite library itself. PRAGMA version and PRAGMA db_version are not valid PRAGMA commands. PRAGMA sqlite_version is a legitimate SQLite command but is used as a function, not as a PRAGMA, so PRAGMA library_version is the precise answer.