Here's a rough synopsis.
The most basic possible output from a console (or other device) is RF-out using the TV's antenna connection. The earliest TVs and consoles required you to take forked connectors and attatch them to screws on the TV, but this was replaced by the now ubiquitous coaxial cable and connector in the US, and (as I understand it) the Belling-Lee connector in Europe. This is the absolute worst option - not only is the signal fairly poor to begin with, but getting a solid connection is very difficult and you tend to get a lot of noise interfering with it.
One step up from that is COMPOSITE video - the yellow plug. This is superior in every way to RF, due to having a cleaner signal to begin with and because the connector is much better. You still get a lot of artifacting and color bleed, so it isn't perfect. This is the "default" connection in the modern era.
The next step is COMPONENT video. This is usually very similar to a COMPOSITE connection, except that where a COMPOSITE cable has three plugs (video, left audio, right audio), the COMPONENT cable has five (three video, two audio). This splits the signal into three parts (exactly what those parts are is a very complex subject) to prevent too much bandwidth going through one cable. This results in a superb picture, but the downside is that not all that many consoles actually have support for it - it was a fairly late arrival, and the advent of digital standards such as HDMI obsoleted it fairly quickly. This is supported by PS2/3, Xbox/360, Wii, and sometimes Gamecube.
S-video is a simpler form of component video, with three data wires sharing a common ground (instead of the three data wires and three grounds in the case of component). The picture isn't quite as nice as three-wire component due to the common ground, but it was supported out of the box on a ton of systems (SNES, PS1, PS2, N64), needing only the correct cable. There are times when you do not want to use it, particularly with PS1 - you can result in a picture that is much too sharp, and several games look really bad this way.
The final connection type is RGB, which transmits the data over many wires to get the maximum bandwidth. Very few consoles support it without modding - IIRC most SEGA stuff does (via SCART cable on the Genesis, VGA on the Dreamcast, and I'm not sure about the Saturn), but not much else.
Where SCART fits in is tricky. SCART is just a connector, and can carry any of these signal types. There's been plenty of disappointed retrogamers that shelled out hard cash for a "RGB cable kit" only to find that it was just composite running over SCART.
As an aside (because I feel obliged to spread this information), original Xboxes suicide due to a cheap capacitor that will rupture and spray the motherboard with acid. If you have an OG xbox, you need to open it up and cut the clock capacitor. Tutorials are available.