Scart carries bidirectional stereo audio, bidirectional composite video (which can be just sync for RGB use, but that's not official specification), unidirectional RGB video, and two control signals (pin 8 "slow blanking"/device selection, and pin 16 "fast blanking"/transparency/RGB mode selection);
later, official and unofficial extensions, often with questionable commercial application, were added, such as:
- half voltage on pin 8 for 16:9
- device control (not to be confused with the video/satellite buttons on most TV remotes having them, which just output different signals for such devices of the same brand)
- S-video over RGB pins
- YPbPr over RGB pins
The rather curious list of features is because the connector was too ahead of its time (and place):
- A significant design goal was the ability to build tuner-less VCRs, external teletext cards, and pay tv decoders, using the composite output from the tuner/IF
- RGB is not really there because of a quest to obtain maximum picture quality, but to unify an Europe split by PAL and SECAM (576i and its timings were already in agreement) while simplifying the design of the above add-on devices (transparency of RGB subtitles over composite video? that's exactly why it's called fast blanking! as in fast enough to change between pixels)...
Look at the French NES: having it output only composite or s-video but converting it to RGB - you can be sure quality wasn't the reason!
For most practical purposes, the "types of scart" you may encounter are two: typically, when you have multiple ones, only the first is guaranteed to support RGB (the others are up to the seriousness of the manufacturer - and one, typically the last, might support S-video but it's unlikely)
(I would also guess there may be TVs missing on RGB input and/or audio/composite return, but you can't argue with corporate cost cutting at the expense of the consumer)
As for console cables, yes, I have seen composite cables (sometimes even without the appropriate adapter!) misadvertised as "SCART"...
As
@SG854 said, PS2/PS3 cables for RGB and Component ought to electrically be the same (of course, YPbPr is not typically delivered over SCART connectors, and RGB isn't over RCA; but that's a solveable problem). Furthermore, official PS2/PS3 RGB cables (which are the same as PS1 ones) include 220 microfarad capacitors on the RGB lines (which are "required" for PS1 use, but useless on the newer consoles which have them built in)
If you have access to free broken PC CRT monitors, go collect the tails (nonremovable video cables) and you will quickly find 3-coax individually shielded ones; combined with the connectors from a cheap PS2 RGB/YPbPr cable and any 21-pin SCART cable, there comes your cheap, DIY, high-quality RGB kit
I do have an 8 € cheap unshielded RGB cable though, and apart from introducing audible noise on bright screens, it's already pretty good - any differences between RGB and "Component" you may encounter are most likely the consequence of different choice by the programmers of your TV's software!