SQLite Backup, Restore, and Data Export Essentials Quiz Quiz

Delve into key concepts of SQLite backup, restore, and data export with this quiz designed to reinforce best practices and technical knowledge. Enhance your understanding of core commands, formats, and procedures to manage database safety and migration efficiently.

1. Using a Command for Full Backup

   Which SQLite command creates a complete backup of a database and writes it to a file called backup.db?

   1. .restore backup.db
   2. .copy backup.db
   3. .export backup.db
   4. .backup backup.db

   Explanation: The .backup command in SQLite copies the entire database to a specified file like backup.db. The .restore command is used to load a backup, not create one. .export is not a valid SQLite command, and .copy is used for copying tables, not making full backups.

2. Exporting Data to CSV

   What is the correct command to export contents of a table named 'users' to a CSV file in SQLite?

   1. .backup users.csv
   2. .mode csvn.output users.csvnSELECT * FROM users;
   3. .save users.csv
   4. .export users.csv users

   Explanation: Setting the mode to CSV and using .output with SELECT exports the data into a CSV file. .export is not a valid command, .backup is for full database backup, and .save only saves the main database, not in CSV format.

3. Restoring a Database from Backup

   Given a backup file named 'mybackup.db', which command will restore this into the current SQLite session?

   1. .import mybackup.db
   2. .recover mybackup.db
   3. .restore mybackup.db
   4. .load mybackup.db

   Explanation: The .restore command loads the contents of a backup into the current database. .import is for importing data (such as CSV), .recover is used for recovering from a corrupted database, and .load is meant for loading extensions, not backups.

4. Choosing an Export Format

   If you need to move data between SQLite and a spreadsheet program, which format is most commonly used for data export?

   1. DB
   2. CSV
   3. SQL
   4. JSON

   Explanation: CSV is a widely accepted data format for spreadsheet programs, making it ideal for data export to such applications. SQL is a notation for commands, JSON is less commonly supported in spreadsheet programs, and DB refers to binary database files.

5. Ensuring Data Consistency During Backup

   Why is it important to ensure no active write operations during an SQLite backup?

   1. To increase export speed
   2. To change file format
   3. To prevent incomplete or corrupted backups
   4. To reduce the backup size

   Explanation: Active write operations can result in a backup that is inconsistent or corrupted. Reducing backup size or increasing export speed are not direct reasons related to active writes. File format cannot be changed by simply halting write operations.

6. Importing Data Back into SQLite

   When importing a CSV file named 'data.csv' into an existing table 'records', what should be done first in SQLite?

   1. .mode csv
   2. .compress data.csv
   3. .backup data.csv
   4. .encode csv

   Explanation: .mode csv sets the input format for the import in SQLite to recognize CSV files correctly. .backup creates backups and isn’t for imports, .compress isn’t a valid command, and .encode csv is not recognized by SQLite for this purpose.

7. Understanding .dump Output

   What does the SQLite .dump command produce when run in the command-line tool?

   1. A CSV export of all tables
   2. A binary backup of the database
   3. An encrypted copy of the database
   4. A file containing SQL statements to recreate the database

   Explanation: .dump outputs SQL statements that can be used to reconstruct the entire database schema and insert its data. It does not create a binary backup, doesn’t encrypt the database, and is not meant to produce CSV exports.

8. Limiting Data During Export

   If you want to export only active users from an 'accounts' table to CSV, how would you filter the data?

   1. Export the whole table and delete inactive users from the file
   2. Change the file extension to .active
   3. Use .limit command before exporting
   4. Use a WHERE clause with the SELECT statement

   Explanation: By adding a WHERE clause to SELECT, only rows meeting the criteria are exported. There is no .limit command, and changing file extensions does not filter data. Deleting data manually from the file is inefficient and error-prone.

9. Backing Up a Database While Connected

   Which SQLite feature allows creating a backup copy even while the application is connected and in use?

   1. Database vacuum
   2. Offline export
   3. Online backup API
   4. Session closure

   Explanation: The online backup API enables safe backups during active connections. Offline export would require disconnection, session closure ends the connection entirely, and vacuum is used for database maintenance, not backup.

10. Recognizing a Common Export Mistake

    If a CSV export from SQLite contains only column headers but no data, what is a likely cause?

    1. The .output file path is incorrect
    2. The database is encrypted
    3. The SELECT query returns no rows
    4. The table is write-locked

    Explanation: An empty CSV with only headers usually means the query retrieved no data. An incorrect output path or encryption would result in errors or unusable files. Write-locked tables prevent changes but don’t affect SELECT, and hence don’t cause empty exports.