3D effects does require some understanding to how 3D graphics are put together and how they are manipulated, but otherwise it is possible to put together most user interface elements with nice animations and transitions that may be key to attracting and maintaining the interest of visitors to your site. In the set of tutorials in the link above, there are excellent examples of Flash-like interactive applications. Depending on the site you are designing, not everything may come in useful and CSS3 performance still varies greatly between browsers, but otherwise it is something fun to experiment with.
For my website design analysis I’ll be taking a look at a site I just discovered recently. It’s a one-off product promo page at http://themble.com/bones/ for a bare bones HTML5 WordPress theme called “Bones”. The promo site was created by Eddie Machado and features illustrations by Melanie Matthews. It’s a great showcase for HTML5 and CSS3.