SQLite Interview Questions and Answers

What is SQLite?
  • SQLite is a lightweight, serverless, self-contained relational database engine widely used in mobile apps, browsers, and embedded systems.
What are the key features of SQLite?
  • Serverless, zero-configuration, transactional, cross-platform, and single-disk database with a small footprint.
Where is SQLite commonly used?
  • Mobile applications (like Android and iOS), web browsers (e.g., Firefox), embedded devices, and IoT systems.
Is SQLite open source?
  • Yes, SQLite is open source and in the public domain, free for commercial and private use.
Does SQLite require a server to run?
  • No, SQLite is serverless and runs directly from the application accessing it.
What file extension is used for an SQLite database?
  • Common extensions include `.sqlite`, `.db`, or `.sqlite3`.
What data types does SQLite support?
  • SQLite uses dynamic typing and supports: NULL, INTEGER, REAL, TEXT, and BLOB.
What is dynamic typing in SQLite?
  • In SQLite, values are associated with storage classes rather than column types, allowing flexibility in data types.
How do you create a table in SQLite?
  • CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
What is the default primary key in SQLite if not specified?
  • If not explicitly declared, SQLite uses the `rowid` column as a unique identifier.
How do you insert data into an SQLite table?
  • INSERT INTO users (name, age) VALUES ('John', 25);
What is the SQLite command to fetch data?
  • SELECT * FROM users;
How do you update a record in SQLite?
  • UPDATE users SET age = 30 WHERE name = 'John';
How do you delete a record in SQLite?
  • DELETE FROM users WHERE name = 'John';
Does SQLite support joins?
  • Yes, SQLite supports INNER JOIN, LEFT JOIN, CROSS JOIN, etc.
Can SQLite handle multiple tables?
  • Yes, SQLite supports multiple tables within a single database file.
What is the SQLite command to create an index?
  • CREATE INDEX idx_name ON users(name);
What are transactions in SQLite?
  • Transactions ensure that a group of operations are executed as a single unit of work, either fully completed or fully rolled back.
How do you begin and end a transaction?
  • BEGIN TRANSACTION; ... COMMIT; or ROLLBACK;
Does SQLite support foreign key constraints?
  • Yes, but they must be explicitly enabled using PRAGMA foreign_keys = ON;
What is the maximum size of an SQLite database?
  • The maximum size is about 281 terabytes (247 bytes).
What is the WAL mode in SQLite?
  • Write-Ahead Logging mode improves concurrency by writing changes to a log file before committing to the database.
How do you enable WAL mode?
  • PRAGMA journal_mode = WAL;
How can you backup an SQLite database?
  • Copy the `.db` file directly or use the `.backup` command from the SQLite CLI.
How to check the version of SQLite?
  • SELECT sqlite_version();