Database / SQLite Interview questions

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) based on keywords in its declared type, and SQLite tries to convert an inserted value to match that affinity, but will still store the value as-is if conversion isn't sensible.

CREATE TABLE t (a INTEGER);
INSERT INTO t VALUES ('hello');   -- succeeds! 'hello' has no numeric form, so it's stored as TEXT

This flexibility is a deliberate design choice for SQLite's typical embedded use cases, but it's also a common surprise for developers coming from strictly-typed databases, who expect a type mismatch to raise an error rather than being silently accepted and stored in its original form.

Take quiz

What does a column's declared type represent in SQLite? A hard constraint that rejects any non-matching value

✗ Not quite. Try again.

A preference (affinity) for storage, which SQLite tries to honor but doesn't strictly enforce

✓ Correct! Well done.

Nothing; declared types are ignored entirely

✗ Not quite. Try again.

What happens if you insert a non-numeric string into an INTEGER-affinity column? The insert fails with a type error

✗ Not quite. Try again.

SQLite stores it as TEXT anyway, since it has no numeric form to convert to

✓ Correct! Well done.

It's silently converted to 0

✗ Not quite. Try again.

Invest now in Acorns!!! 🚀 Join Acorns and get your $5 bonus!

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Earn passively and while sleeping

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.

More Related questions...

What is SQLite? What are the key features of SQLite? How is SQLite different from client-server databases like MySQL or PostgreSQL? What is the file format of a SQLite database? What are the supported data types in SQLite? What is dynamic typing (type affinity) in SQLite? How do you create a table in SQLite? How do you insert data into a SQLite table? How do you query data from a SQLite table? How do you update a record in SQLite? How do you delete a record in SQLite? What is a PRIMARY KEY in SQLite? What is AUTOINCREMENT in SQLite? What is a UNIQUE constraint in SQLite? What is a FOREIGN KEY in SQLite and how do you enable it? What is the sqlite3 command-line shell used for? How do you create an index in SQLite? What is a NULL value in SQLite? What is the difference between CHAR, VARCHAR, and TEXT in SQLite? What is the ROWID in a SQLite table? What is an in-memory SQLite database? How do you back up a SQLite database? What is the PRAGMA statement used for? What is a VIEW in SQLite? What is a TRIGGER in SQLite? What is the difference between DELETE, TRUNCATE, and DROP in SQLite? What is a transaction in SQLite and how do you use BEGIN/COMMIT/ROLLBACK? What is the difference between WAL mode and rollback journal mode? Why does SQLite recommend enabling foreign keys explicitly, given they're off by default? What is the difference between INTEGER PRIMARY KEY and a regular PRIMARY KEY in SQLite? How does SQLite handle concurrent writes? What is the difference between a SQLite VIEW and a TABLE? How do you perform a JOIN in SQLite? What is an UPSERT in SQLite (INSERT... ON CONFLICT)? How do you use the EXPLAIN QUERY PLAN statement? What is a composite primary key in SQLite? How does SQLite handle database locking? What is the difference between VACUUM and ANALYZE in SQLite? Explain the internal working of SQLite's B-tree storage structure? How does WAL mode improve concurrency compared to the default rollback journal? Explain the lifecycle of a SQLite transaction from BEGIN to COMMIT? Why might full-text search (FTS5) be needed instead of a LIKE query? How do you optimize a slow SQLite query? What are the limitations of SQLite for large-scale, high-concurrency applications? How does SQLite ensure ACID compliance without a separate server process? Explain how SQLite's write-ahead logging (WAL) checkpoint process works? What isolation level does SQLite provide, compared to other RDBMS? What is the STRICT keyword used for in SQLite table definitions?

Show more question and Answers...

Integration

	Interviews Questions Java Spring Hibernate Maven Testing API BigData Web Erlang AI Database Integration Cloud Python Scala Tools Golang	About Javapedia.net Javapedia.net is for Java and J2EE developers, technologist and college students who prepare of interview. Also this site includes many practical examples. This site is developed using J2EE technologies by Steve Antony, a senior Developer/lead at one of the logistics based company.
	contact: javatutorials2016[at]gmail[dot]com
Kindly consider donating for maintaining this website. Thanks.
	Copyright © 2026, javapedia.net, all rights reserved. privacy policy.

Database / SQLite Interview questions

What is dynamic typing (type affinity) in SQLite?

Comments & Discussions

Recently added...