You can use create table, select, insert SQL statements to create table, select data from a table and insert data to a table. There are some internal commands to view tables and schema such as .table .schema. There are more information in the SQLite web page's document.
I prefer to use a UI tool to manage my database. There is one called as SQLite Database Browser. It is very simple but provides all the functions for creating tables, browsing data with editing and deleting rows, and modifying tables. I really like this tool. I think this one is also available for Windows.
SQLite's database is file based database. You can use SQLite console or browser to create a database. The file is binary file. Apple's Objective-C provides framework APIs to SQLite database and it is also available for iPhone development SDK called as libsqlite3.0.dylib. I think .Net must provide framework classes for SQLite as well.
I can see there are many great potential uses for SQLite. The database file is a SQL database. It can handle massive data with great performance. I think I'll use it in my applications.