Chcek out _Clause and Effect_ by Clocksin and _The Art of Prolog_ by Sterling and Shapiro. CAE is sort of like _The Little Schemer_ for Prolog, but has a couple larger case studies as well. TAoP is one of my absolute favorite programming books - it's got as much substance as SICP. The newer edition (1994) is more expensive (though $35 used on Amazon ATM, which is a steal; it's usually more like $85-100), but adds quite a bit of new material.
_The Craft of Prolog_ by O'Keefe is also good, though disorganized. (I think it was written as commentary on something else, and without that, its structure seems a bit arbitrary.)
I mostly use Prolog for prototyping and exploratory programming. It would probably be more generally useful as an embedded language library, like Lua (another favorite of mine). It's a bit awkward as a fully standalone language, since things like IO don't always mix with backtracking. It's great as a rule engine, though, and would probably also be brilliant as a query language for a document-oriented database.
Fully agree. I'm learning prolog too, and it's really fantastic for the types of problems it was designed (e.g. resolving constraints and expressing relations), but I didn't enjoy doing IO in Prolog. My solution is to use lisp as my primary language and use an implementation of prolog-in-lisp (e.g. racketlog for racket). This way I have the best of both worlds.
I have a problem switching thinking between Prolog clauses as logic relations and thinking of them as a problem solving process. The towers of Hanoi problem mystifies me, for example. I also have trouble seeing how to use logic programming in a larger system, to get more than example problems done. Can you recommend a good resource?