SQLite is a great database when you don't need the benefit of a full server-based database. You can use it in desktop or mobile applications, for small or medium sized web applications, or anywhere else you can think of. It's simple, but still has a rich feature set.
Some of its behavior is a little bit quirky, but once you learn how to use it, you can use it in your application basically the same way as you would SQL Server or any other database.
This video will walk through some of the basics, showing how similar it is to any other database when using .NET.