Database / SQLite Interview questions
1. What is SQLite?
SQLite is a lightweight, serverless relational database engine that stores an entire database — tables, indexes, and data — in a single ordinary file on disk. Unlike most database systems, there's no separate database server process to install, configure, or...
2. What are the key features of SQLite?
SQLite's design centers on being small, self-contained, and zero-configuration, while still providing a genuinely full-featured SQL engine. Serverless — no separate database process; the engine runs in-process with the application.
3. How is SQLite different from client-server databases like MySQL or PostgreSQL?
MySQL and PostgreSQL run as standalone server processes that clients connect to over a network (or a local socket), meaning multiple applications on different machines can share the same database concurrently through that server. SQLite has no server at all...
4. What is the file format of a SQLite database?
A SQLite database is stored as a single, ordinary binary file on disk, using a well-documented, stable file format that SQLite guarantees to maintain backward compatibility with indefinitely — a database file created by SQLite version 3 many years ago...
5. What are the supported data types in SQLite?
SQLite uses a small set of storage classes rather than the rigid, fixed-width types found in most other database systems — each value stored is tagged with one of these classes based on the value itself, regardless of the column's...
6. What is dynamic typing (type affinity) in SQLite?
SQLite uses type affinity rather than strict column typing: a column's declared type is a preference for how to store a value, not a hard constraint that rejects mismatched data the way most databases enforce. Each column is assigned one...
7. How do you create a table in SQLite?
Tables are created with standard SQL CREATE TABLE syntax, specifying column names and their declared types (which determine type affinity), along with any constraints. CREATE TABLE person ( id INTEGER PRIMARY KEY , name TEXT NOT NULL , age INTEGER,...
8. How do you insert data into a SQLite table?
The standard INSERT INTO statement adds a new row, specifying which columns you're providing values for (columns left out get their default value, or NULL if none is defined). INSERT INTO person (name, age, email) VALUES ( 'Ada' , 34...
9. How do you query data from a SQLite table?
Standard SELECT syntax retrieves rows, optionally filtering with WHERE , sorting with ORDER BY , and limiting results with LIMIT — the same core SQL syntax used across most relational databases. SELECT name, age FROM person WHERE age > 30...
10. How do you update a record in SQLite?
The UPDATE statement modifies existing rows matching a WHERE condition — omitting the WHERE clause updates every row in the table, which is a common and dangerous mistake if done accidentally. UPDATE person SET age = 35 , email =...
11. How do you delete a record in SQLite?
The DELETE FROM statement removes rows matching a WHERE condition — like UPDATE , omitting the condition deletes every row in the table. DELETE FROM person WHERE id = 1 ; Deleting rows doesn't automatically shrink the database file's size...
12. What is a PRIMARY KEY in SQLite?
A PRIMARY KEY uniquely identifies each row in a table, and SQLite enforces that no two rows can share the same primary key value (unless declared otherwise for special cases like WITHOUT ROWID tables). CREATE TABLE person ( id INTEGER...
13. What is AUTOINCREMENT in SQLite?
AUTOINCREMENT is an optional modifier on an INTEGER PRIMARY KEY column that guarantees newly generated key values are always strictly larger than any value ever used before in that table, even after rows with high key values have been deleted...
14. What is a UNIQUE constraint in SQLite?
A UNIQUE constraint ensures no two rows in a table share the same value in that column (or combination of columns, for a multi-column unique constraint), raising a constraint violation error if you try to insert or update a row...
15. What is a FOREIGN KEY in SQLite and how do you enable it?
A FOREIGN KEY constraint links a column in one table to a primary/unique key in another, ensuring referential integrity — you can't insert a row referencing a value that doesn't exist in the referenced table. CREATE TABLE order_item ( id...
16. What is the sqlite3 command-line shell used for?
sqlite3 is SQLite's official interactive command-line tool for opening a database file, running SQL directly, and inspecting schema — the most common way to explore or debug a SQLite database without writing any application code. $ sqlite3 mydata ....
17. How do you create an index in SQLite?
CREATE INDEX builds a separate B-tree structure over one or more columns, letting SQLite look up matching rows directly instead of scanning the whole table — the same fundamental purpose an index serves in any relational database. CREATE INDEX idx_person_email...
18. What is a NULL value in SQLite?
NULL represents a missing or unknown value — it's distinct from an empty string ( '' ) or zero ( 0 ), and it follows SQL's three-valued logic: comparing anything to NULL using = yields NULL (neither true nor false),...
19. What is the difference between CHAR, VARCHAR, and TEXT in SQLite?
Functionally, all three are treated identically in SQLite — whatever length or width you declare ( CHAR(10) , VARCHAR(255) ) is completely ignored for storage purposes; SQLite stores the string exactly as given, taking only as much space as the...
20. What is the ROWID in a SQLite table?
Every ordinary SQLite table (unless explicitly declared WITHOUT ROWID ) has a hidden rowid column — a 64-bit signed integer that uniquely identifies each row and serves as the key of the table's underlying B-tree, regardless of whether you've defined...
21. What is an in-memory SQLite database?
Instead of writing to a file on disk, SQLite can create a database that exists purely in RAM for the lifetime of the connection, using the special filename :memory: — useful for temporary data, testing, or scenarios where disk persistence...
22. How do you back up a SQLite database?
Because a SQLite database is just an ordinary file, the simplest backup is a straightforward file copy — but that's only safe when you're certain no write is in progress at the same moment, since copying a file mid-write can...
23. What is the PRAGMA statement used for?
PRAGMA is SQLite's mechanism for querying or modifying internal engine settings and behavior — configuration knobs that aren't part of standard SQL but control how SQLite itself operates for the current connection or database. PRAGMA foreign_keys = ON; -- enable...
24. What is a VIEW in SQLite?
A VIEW is a saved, named query that behaves like a virtual, read-only table — querying the view runs the underlying query fresh each time, rather than storing its own separate copy of data. CREATE VIEW adult_person AS SELECT id,...
25. What is a TRIGGER in SQLite?
A TRIGGER is a piece of SQL logic that runs automatically in response to a specific table event — INSERT , UPDATE , or DELETE — either before or after that event occurs, without the application needing to explicitly call...
26. What is the difference between DELETE, TRUNCATE, and DROP in SQLite?
SQLite doesn't actually have a TRUNCATE statement at all — that's a notable difference from many other SQL databases. The remaining two behave distinctly....
27. What is a transaction in SQLite and how do you use BEGIN/COMMIT/ROLLBACK?
A transaction groups multiple statements so they either all take effect together, or none of them do — the standard atomicity guarantee. In SQLite, every statement outside an explicit transaction is actually wrapped in its own implicit, single-statement transaction automatically;...
28. What is the difference between WAL mode and rollback journal mode?
Both are journaling strategies SQLite uses to guarantee atomicity and crash recovery, but they take different approaches to how changes are recorded before being made permanent. Rollback journal (default) WAL (Write-Ahead Log) Copies original data to a journal file before...
29. Why does SQLite recommend enabling foreign keys explicitly, given they're off by default?
Foreign key enforcement was added to SQLite well after the file format and basic engine were already established and widely deployed, so turning it on by default risked breaking existing applications that had databases with foreign key declarations that weren't...
30. What is the difference between INTEGER PRIMARY KEY and a regular PRIMARY KEY in SQLite?
Declaring a column as exactly INTEGER PRIMARY KEY makes it a direct alias for the table's internal rowid , meaning lookups by that column hit the B-tree's key directly — the fastest possible access path. A PRIMARY KEY on a...
31. How does SQLite handle concurrent writes?
SQLite allows many simultaneous readers, but only ever one writer at a time for a given database file — there's no concept of row-level or table-level locking granularity the way a full client-server database offers; the whole database file is...
32. What is the difference between a SQLite VIEW and a TABLE?
A TABLE physically stores its own rows on disk; a VIEW stores no data of its own at all — it's just a saved query definition that's re-executed against the underlying tables every time you select from it. TABLE VIEW...
33. How do you perform a JOIN in SQLite?
SQLite supports the standard SQL join types — INNER JOIN , LEFT JOIN , and (more recently) RIGHT JOIN and FULL JOIN — combining rows from two or more tables based on a matching condition. SELECT person.name, order_item.total FROM person...
34. What is an UPSERT in SQLite (INSERT... ON CONFLICT)?
An upsert inserts a new row, or updates an existing one instead if the insert would violate a uniqueness constraint — a single statement covering both the "create" and "update" case, avoiding a separate check-then-insert-or-update sequence in application code. INSERT...
35. How do you use the EXPLAIN QUERY PLAN statement?
Prefixing any query with EXPLAIN QUERY PLAN shows, in human-readable form, how SQLite intends to execute it — which indexes (if any) it will use, whether it needs to scan the whole table, and how joins will be processed —...
36. What is a composite primary key in SQLite?
A composite (or compound) primary key spans more than one column, with uniqueness enforced across the combination of those columns rather than any single one alone — useful for tables representing a many-to-many relationship, where the natural unique identifier really...
37. How does SQLite handle database locking?
SQLite uses a small set of lock states applied to the whole database file to coordinate access between connections: UNLOCKED , SHARED (for reading), RESERVED (a writer intends to write soon but hasn't yet), PENDING , and EXCLUSIVE (actively writing)....
38. What is the difference between VACUUM and ANALYZE in SQLite?
Both are maintenance commands, but they address different things: VACUUM reclaims unused disk space left behind by deletes/updates by rebuilding the database file; ANALYZE gathers statistics about table/index contents that SQLite's query planner uses to make better decisions about which...
39. Explain the internal working of SQLite's B-tree storage structure?
Every table and index in SQLite is stored as a separate B-tree within the single database file — a balanced tree structure where each node is one fixed-size page (typically 4096 bytes), organized so that lookups, insertions, and range scans...
40. How does WAL mode improve concurrency compared to the default rollback journal?
In rollback journal mode, a writer modifies the actual database file directly (after saving the original data to a journal for potential rollback), which means readers have to wait during the brief window a writer is committing, since the file...
41. Explain the lifecycle of a SQLite transaction from BEGIN to COMMIT?
Calling BEGIN starts an explicit transaction, and every statement afterward operates within it until either COMMIT makes the changes permanent or ROLLBACK discards them. flowchart TD A[BEGIN] --> B[Statements execute, journal/WAL records original or new page state] B --> C{COMMIT...
42. Why might full-text search (FTS5) be needed instead of a LIKE query?
A LIKE '%word%' query has to scan every row and check the pattern against each one, since a leading wildcard prevents SQLite from using a normal B-tree index at all — on a large table, this means a full table...
43. How do you optimize a slow SQLite query?
Optimization generally follows a consistent sequence: understand what the query planner is actually doing, then address the specific gap that's causing it to do more work than necessary. Run EXPLAIN QUERY PLAN to see whether the query is using an...
44. What are the limitations of SQLite for large-scale, high-concurrency applications?
SQLite's design choices that make it excellent for embedded, single-application use become real constraints once an application needs many independent processes hammering the same database with heavy concurrent writes. Single-writer limitation — only one write transaction can proceed at a...
45. How does SQLite ensure ACID compliance without a separate server process?
ACID guarantees are typically associated with a server process coordinating access, but SQLite achieves the same guarantees entirely within the library linked into the application, using file-level locking and journaling (rollback journal or WAL) as its coordination mechanism instead of...
46. Explain how SQLite's write-ahead logging (WAL) checkpoint process works?
In WAL mode, committed transactions accumulate as appended entries in the WAL file rather than being written into the main database file immediately. A checkpoint is the process that periodically copies those accumulated WAL entries into the main database file,...
47. What isolation level does SQLite provide, compared to other RDBMS?
SQLite effectively provides serializable isolation for its transactions — the strictest standard isolation level — rather than offering a configurable choice of levels (read committed, repeatable read, serializable) the way many client-server databases do. flowchart LR A[Transaction b
48. What is the STRICT keyword used for in SQLite table definitions?
Adding STRICT to a CREATE TABLE statement opts that specific table out of SQLite's usual flexible type affinity behavior, instead enforcing that inserted values must actually match the column's declared type — much closer to how most other SQL databases...
