This is one of the most interesting articles I have read in a while. SQLite has a lot of very detailed documentation, and I found this part very interesting.
This describes how the query planner for SQLite works, so, essentially, how to access the indexes and what indexes when the user write a specific query on SQLite (similar query planners are also in other database projects).
I found this article extremely well written, I would probably use it if I would be a professor, because it is extremely clear and it shows many concrete examples of queries, from the easier ones to the most complex ones.
After reading this article you will have a much better understanding of how a database works and personally, I would be very curious to see a similar article of a code-walkthrough of SQLite for the same examples.