The database management systems in common use are relational client-server databases. MySQL, Oracle, Microsoft SQL Server and many other popular RDBMS are built on the client-server model. SQLite is an interesting departure from that well known model. SQLite is a library that can be embedded within an application and become part of this application. You no longer need to setup, configure and manage a separate DBMS to do database transactions. You can do all this from within your own application without a need for any external system.
Of course SQLite would be suitable for specific purposes only. Most production web applications cannot rely on it. Yet the idea itself of having such a nice way to do database transactions in itself is interesting.