kiat, the main advantage of prepare over do is most apparent when executing multiple queries. Quoting from the DBI doc:

The do() method can be used for non repeated non-SELECT statement (or with drivers that don't support placeholders).

I have used both, but find prepare more versatile, especially when using placeholders. And because I want to be as consistent as possible in my coding style, I just stick to prepare for DBI queries. I'll be curious to see how other more XP'd monks reply--always educational.

Check out Paul Dubois's book MySQL and Perl for the Web as he does discuss this topic a bit (I'd quote it, but it's at the office).

Update:
Okay, I stopped by the office, and with apologies to Dubois and New Riders, here's the take on do vs prepare:
• you do have to use the undef as an argument when using placeholders (see jeffa's node above)
• prepare, because of it's reusable query plan, works best when inserting data into a table via a loop
• also because of it's reusable query, prepare can give a performance boost
• use of prepare makes transporting code to other databases easier and more efficient.

But buy the book for all the details--it's invaluable--the title tells it all.