Snesboy's Guide to HDTV retro gaming
Many of you may be concerned about how terrible your 8-bit, 16-bit, 32-bit, and 64-bit gaming consoles fair with these fancy new HDTVs. RF is something many of you are familiar with, however, later gaming consoles use AV input for those TVs that have the Red, Yellow, and White connectors on the front. Ugh, I'm straying off the subject. Here are the optimal connections for gaming consoles post-1983. Brands included are Atari, Nintendo, Sega, Sony, and Microsoft.
Atari
Atari 2600 & 7800 - An excellent guide can be found here: http://www.youtube.com/watch?v=38os4_ama08
Nintendo
NES - default connection: RF; optimal connection: composite
Super NES - default connection: RF, composite; optimal connection: S-video*
Nintendo 64 - default connection: composite; optimal connection: S-video*
GameCube - default connection: composite; optimal connection: S-video*, component**
Wii - default connection: composite; optimal connection: component
Sega
Master System - default connection: RF; optimal connection: composite
Genesis - default connection: RF, composite; optimal connection: S-video via mod: http://www.youtube.com/watch?v=VlPgQ8UDDGc
Saturn - default connection: composite; optimal connection: S-video
Dreamcast - default connection: composite; optimal connection: S-video, VGA***
Sony
PlayStation - default connection: composite; optimal connection: S-video
PlayStation 2 - default connection: composite; optimal connection: S-video, component
Microsoft
Xbox - default connection: composite; optimal connection: S-video, component
I don't think I need to explain that the best connections for PlayStation 3 and Xbox 360 are HDMI. You "core" gamers should know that 
Next subject is resolution. Keep in mind most systems before 1996 didn't support widescreen. And all systems didn't support it until 2006 (Wii, Xbox 360, and PS3 all support 16x9). Before that, it was on a game by game basis. For a list of games that support widescreen, please use this list:
Sega Saturn 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/p,23/
PlayStation 1 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/p,6/
Nintendo 64 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/p,9/
Sega Dreamcast 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/p,8/
GameCube 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/p,14/
PlayStation 2 16x9 games: http://www.mobygames.com/attribute/sheet/attributeId,278/offset,0/p,7/so,0a/
Xbox and PlayStation 2 have a widescreen setting in their system menus. All you have to do is turn it to "widescreen" or "16x9" and change the output of your TV to "wide" or whatever your button thing is on your remote. Pretty sure all games made for these systems support widescreen.
Retro Gaming
Here is the tricky part. Older systems do not support widescreen. They have a screen format of 4x3. So if black bars appear on the sides of you game, it's fucking normal dammit! Making it fill the screen just STRETCHES out the game. True widescreen adds to the viewing area. If you want those old retro games to look their best, try using some sort of download service: Wii Virtual Console, Xbox Live Arcade, or the PlayStation Network. Games downloaded from the Wii Virtual Console are even bumped up to 640x480 resolution so they look better than they did 15 years ago. And on top of that, they look even better when using component 
Now I just typed all that, hopefully someone read it. Now where do you get all these fancy cables? The internet:
S-video cables for SNES, N64, GCN, PS1, PS2, PS3, and Xbox 1: http://www.amazon.com/Xbox-PSone-PS2-PS3-N64-SNES/dp/B002ZKSZG4/ref=sr_1_6?ie=UTF8&s=videogames&qid=1270546263&sr=8-6
S-video cables for Sega Genesis: http://www.amazon.com/Sega-Genesis-Nomad-9-PIN-CABLES-Video/dp/B001U23RBG/ref=sr_1_2?ie=UTF8&s=videogames&qid=1270546389&sr=8-2
S-video cables for Sega Saturn: I was unable to find them on Amazon. I did find this video on YouTube though, so you should ask this guy: http://www.youtube.com/watch?v=sfr5t2DLvMI
Composite cables for NES: Also couldn't find them on Amazon though I'm pretty sure they came packaged with the original NES.
VGA cables for Dreamcast: http://www.amazon.com/Sega-Dreamcast-Adapter-Adaptor-DCVGA-S-Video/dp/B000SL4IIG/ref=sr_1_1?ie=UTF8&s=videogames&qid=1270546954&sr=8-1
S-video cables for Dreamcast: http://www.amazon.com/InterAct-Stereo-V-Cable-Sega-Dreamcast/dp/B00000K13H/ref=sr_1_2?ie=UTF8&s=videogames&qid=1270546989&sr=1-2
I think that covers everything. Hope I've been of help to those of you who are technologically challenged.
EDIT: For those of you living in PAL regions, replace S-video with SCART.
EDIT 2: In case you were wondering, Component is always better than S-video. Spring for Component if it's available.
EDIT 3: Since I've never been able to test if all PS2 games are widescreen or not, I have added a list of PS2 games supporting widescreen. Special thanks to alekth for bringing it to my attention.
EDIT 4: If you live in the PAL regions, go get RGB cables.
EDIT 5: http://retro.nintendolife.com/news/2011/04/guides_upscaling_your_retro_consoles_for_hdtv_sets
*SNES, N64, and GameCube all use the same audio/video input so S-video cables purchased will work on all 3 systems.
**Component only supported on consoles manufactured before May 2004. If your system is compatible with component, look on the bottom at the serial number. The number you are looking for is DOL-101. You can also look on the back for a "Digital Out" port.
***VGA is the best connection for Dreamcast, providing a hi-res image. Even higher than S-video
