First, databases are not "just" about persistence, and I don't mean that in a pedantic way. The point of a database management system is to manage your data over time and allow you to make use of it. This includes things like stating constraints to ensure that the data in the database is always correct and providing methods for querying your data at varying levels of complexity.
Second, there are a number of in-memory databases. Oracle sells one. SQLite is often used as one. The fact that they are in-memory does not make them not databases.
Third, relational versus non-relational is not really an argument about "how the data is stored on disk" or about being "standards compliant." A database traditionally consists primarily of three things: a data model, a query language, and an implementation. A data model, like "everything is a table with column headers and rows" can be written to disk in many different ways and queried in many different ways. (It's an abstraction.) Indexing structures and data formats may be mostly the same even when the data model is different. The (current) "relational vs non-relational" debate, such as it is, is usually about whether it makes sense to change the data model and query language in order to ensure that the implementation is scalable on "cloud-like" platforms with specific data access needs. (That said, there are many different types of non-relational models and query languages designed for different purposes.)
Fourth, there isn't one SQL. There are in fact a number of standards, SQL-86, SQL-89, SQL-92, and so on. It makes sense to learn the standard first (at least up to 92 or so), rather than learning particular implementation specifics. Most databases implement SQL-92 or above, though there is somewhat wide variance in additional features like indexing structures and functions and in data modeling languages.