Hi there daringtakers. I myself am using pathauto module google sitemaps /xmlsitemap module image module imce module print module google analytics global redirects alinks pagetitle Most of those modules are meant to make the site more SEO friendly.
you can see lot of drupal themes (both drupal 4.7 themes and drupal 5 themes) at Drupal Theme Garden. It's a live drupal themes demo, so you can see those themes in action.
There are tons of modules, but hopefully they are all placed on drupal.org so it won't be that hard to find 'em. I use mostly these: SEO related modules: globalredirect pathauto meta tags gsitemap (now xml sitemap) Content and classification: Views (this module simply rocks) CCK (+ many field type modules like imagefield, date, link, etc) Panels TinyMCE (+IMCE) or Textile (I love textile) User Management: Logintoboggan Registration Role Community Stuff: Event Signup (these two really work great together) Maintenance and Spam management: Update Status (this is really handy) Spamspan Akismet Blogging: Multiping Trackback Service Links I could list more, but the use of modules mostly depend on what goal you want to achieve. It's good to know at least the popular modules role and when to use them.