SQLite is a small SQL database engine and it makes a good fit for iPhone apps. You can create a SQLite database and its tables using Base app: http://menial.co.uk/software/base/. Base is a lightweight Mac OS X application.

In order to use a SQLite database in an iPhone app you need to add it as a resource into the project: right click on Resources, then “Add > Existing Files …”. You will probably use the SQLite C API, so you need to add the corresponding Framework too: right-click Frameworks, then “Add > Existing Frameworks …” and select libsqlite3.0.dylib (/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.sdk/usr/lib/libsqlite3.0.dylib). Don’t forget to include the necessary header file in every class that will use the SQLite C API: #include <sqlite3.h>.

When your iPhone application launches for the first time you’ll need to copy the SQLite database to the Documents directory (under your application’s path).

Before performing any query on the database you have to open it with sqlite3_open() (applicationDidFinishLaunching: method is a good place for this). The database should be closed with sqlite3_close(), when the application ends (applicationWillTerminate: method).

Functions that you will often use are: sqlite3_prepare(), sqlite3_step(), sqlite3_column_…(), sqlite3_finalize(). For a complete list of functions see http://www.sqlite.org/c3ref/funclist.html.