"If you have a background in database management ..., don't worry: if you use a SQLite store, Core Data automatically creates the intermediate join table for you."
- Core Data Programming Guide > Relationships and Fetched Properties > Many-To-Many Relationships
You've been hacking on iOS, and now you need persistent storage. You know SQL, you want to use SQLite, but everything in xCode and advice from the web recommend Core Data. What do you do?
Core Data, xCode and Cocoa provide an ORM (Object Relational Mapping) that assists developers with mapping stored data into Objective C objects. This powerful tool is at once extremely helpful, incredibly seductive and a bit confining. A judicious use of the provided technology allows for rapid development that nears a finished feel for your application. Yet, if you wish to grab the database connection and perform a complex outer join with a group-by - no dice. At least, not with the information held by Core Data.
In researching Core Data, you'll find a lot of advice telling you that "Core Data is not a Database". Although simple to say, that's not quite correct. Core Data, when used with the xCode Data Model Builder and class generation is a (very convenient) alternative implementation to all of the work a developer would otherwise have to do. You are saved from the need to write client/server code, open cursors, perform partial data reads, create Primary Key/Foreign Key relations, create Primary Key sequence generators (select emp_sequence.nextval from dual;), build Intersection (Join) Tables, link up object graphs, etc.
Be sure to build your Data Model using xCode's modeling tool: File > New > Resource > Data Model. DO NOT CREATE PRIMARY KEYS. xCode creates these automatically when modeling relationships between entities (tables). If you really do need a monotonically increasing sequence generator, try this advice from Apple's Mailing List. If you require Primary Key like behavior, read more about uniqueID, a unique handle to an object (row), from Stack Overflow postings here and here.
Inside your program, NSManagedObjectContext provides access to stored data. Use this context to create (insert) objects, fetch (query) objects (full table rows) and properties (columns). The web contains pages of advice for how to use this context. A word of caution - don't make this object a singleton (global variable). Pass it around to the objects that need it. The two most important reasons are (1) the context cannot be used on more than one thread and (2) the context provides rollback capability, so edit screens can be cancelled easily. Read this Stack Overflow Question for some advice.
It turns out that a simple select statement when performed in Core Data requires 13 lines of code! Thanks to Matt Gallagher for this explanation. Look for subsequent posts here that build upon what Matt did.
Matt has also written a great article on the differences between Core Data and a Database. Brent Simmons discusses when it might be appropriate to switch away from Core Data - Spoiler Alert: it rarely is. Derrick compares and contrasts Core Data and SQL. So does Alex in his answer at Stack Overflow.
If you decide you just cannot handle Core Data and want to roll your own solution, there are plenty of middle level APIs to help in your effort. Github holds the source code for FMDB (Flying Meat DB), a SQLite cover, written by Gus Mueller. You could also try Google's SQLite wrapper: Plausible Database.
May your queries by quick and your columns be indexed.