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 connect to over a network;...
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. Zero configuration — no setup, accounts, or connectio...
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 — the database engine is a librar...
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 can still be opened by the latest versi...
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 declared type. NULL INTEGER REAL TEXT BLOB Missing value. Si...
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 of five affinities (TEXT, NUMERIC, INTEGER, REAL, BLOB) bas...
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, email TEXT UNIQUE ); Running this via the sqlite...
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 , 'ada@example.com' ); Multiple rows can be inserted in a singl...
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 ORDER BY age DESC LIMIT 10 ; SQLite supports the ful...
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 = 'ada.new@example.com' WHERE id = 1 ; It's good practice to fi...
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 on disk — the freed space inside the file is m...
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 PRIMARY KEY , name TEXT ); A notable SQLite-specific detail: d...
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 — preventing key value reuse. CREATE TABLE perso...
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 that would create a duplicate. CREATE TABLE person ( id INTEGER PRIMA...
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 INTEGER PRIMARY KEY , person_id INTEGER, FOREIGN KEY ...
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 . db sqlite > . tables sqlite > . schema per...
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 ON person(email); CREATE UNIQU...
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), not TRUE or FALSE . SELECT * FROM person WHERE age = NULL ; -- returns noth...
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 actual text requires, since it uses dynamic ty...
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 your own primary key. SELECT rowid, name...
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 isn't needed at all. sqlite3_open(":memory:", &db); ...
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 capture an inconsistent snapshot. # simple file copy (only safe if...
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 foreign key enforcement PRAG...
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, name, age FROM person WHERE age >= 18 ; SELECT * FROM adult_pe...
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 anything. CREATE TRIGGER update_timestamp AFTER UPDATE ON person...
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. DELETE FROM table DROP TABLE Removes rows (optionally filtered by WHERE); the table structure remains. Removes the entire table, includi...
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; BEGIN lets...
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 overwriting it in place....
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 actually being enforced (and might ...
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 non-integer column (or declared with WITHOUT ROWID ), or a composite...
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 the unit of write locking. flowchart LR A[Multi...
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 Stores its own physical data. Stores only a query definition; no physical d...
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 INNER JOIN order_item ON person.id = order_item....
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 INTO person (id, name, ...
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 — without actually running the query itself. EXPLAIN QUERY PLAN S...
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 is a pair (or more) of...
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). flowchart LR A[UNLOCKED] --> B[SHARED - reader...
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 index to ...
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 all stay efficient even as the table grows large. ...
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 they'd be reading from is actively being changed. WAL...
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 or ROLLBACK?} C ...
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 scan for every search, which gets slower as the table grows and offers none of the...
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 index ( SEARCH ) or scanning the whole table ( SCA...
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 t...
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 a server....
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, after which the WAL file can be res...
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 behave by default. CREATE TABLE perso...
