Every SQL database uses its own implementation of the language that varies slightly. While basic queries are almost universal, there are notable nuances between MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, etc.
What’s particularly notable about SQLite is that unlike all the others mentioned above, this database software doesn’t come with a daemon that queries are passed through. This means that if multiple processes are using the database at once, they will be directly altering the data through the SQLite library and making the read / write data calls to the OS themselves. It also means that the locking mechanisms don’t deal with contention very well.
This isn’t a problem for most applications that where one would think of using SQLite — the small overhead benefits and easy data retrieval are worth it. However, if you’ll be accessing your database with more than one process or don’t consider mapping all your requests through one thread, it could be slightly troublesome.