SQLite is very popular, but it is mainly used for handling local databases. When you use SQLite, the client is linked directly to the database engine; there is no client/server separation as there is with "traditonal" databases.
The most well known open source SQL servers are (none of which are Windows specific)
MySQL -- The world's most popular open source database
PostgreSQL: The world's most advanced open source database
Firebird: The true open source database for Windows, Linux, Mac OS X, and more
In addition, Microsoft, Oracle, and Sybase (and more, I presume) provide free versions of their servers. (In case you want to get experience with commercial systems)
SQL Server Express Edition
Oracle Database 11g Express Edition
Adaptive Server Enterprise version 15.7 Developer's Edition
When is comes to the API, in the case of SQLite you often talk directly to the database engine, so you using it's native API.
System.Data.SQLite is an ADO.NET adapter for SQLite. Being .Net, it is for use from C++/CLI code, not (regular) C++. You could write a mixed-mode app with the ADO.NET related code marked as managed and the rest of the code as native. But regular C++ code can use SQLite's normal C API, or a C++ wrapper if you really want to. e.g.
How to: Marshal ANSI Strings for ADO.NET (C++/CLI)
Regarding (other) database APIs available on Windows:
ADO.NET is the .Net way to talk to databases (Microsoft also provide LINQ which can be used to talk to SQL databases as well as other data sources.)
Before this there were/are:
* ADO - an older COM-based mechanism
* ADOX - a COM-based database management API (companion to ADO, which is for querying)
* OLE DB - another, lower-level, COM-based mechanism (now on the way out)
* ODBC - a C API
* DAO - a deprecated API
And then there are the various native APIs
* Connector/C (aka libmysql). for MySQL
* SQL Server Native Client API, for MSSQL
Note that while OLE DB was supposed to become the dominant API (ADO sitting on top of it), this position was revised not so long ago:
Microsoft is Aligning with ODBC for Native Relational Data Access
Also, SQLite has become of interest to developers working on Windows 8 Metro apps. Metro apps have no way to communicate with remote databases via the normal route (i.e. the usual ODBC, ADO.NET route); they have to go via the cloud, or they're on their own with local file storage, which is exactly what SQLite provides.
Correct way to use databases in Windows 8 and Windows Phone 8
Finally, I was not sure whether you were just interested in working with SQL API's or if you wanted to gain some basic DBA skills, too. But from a job point of view, this is the current demand according to http://www.itjobswatch.co.uk
Jobs overall (marked as developer)
SQL Server 19862 (403)
Oracle 11674 (429)
MySQL 6177 ( 45)
Sybase 982 ( 15)
And then there are the dialects of SQL to worry about -- Transact SQL aka T-SQL (Microsoft and Sybase), PL/SQL (Oracle), etc.
Windows Data Access Components SDK
What is a good OO C++ wrapper for sqlite
(The overall opinion was that it was better to use the SQLite C API directly)