Are there any examples of songs that employ a Circle of Fourths that I might be able to practice along to? How does the progression tie in with different scales?
The idea behind the "Circle of Fourths" is that every 7th chord resolves to a I chord a fourth above it. For example, a G7 chord resolves to C, because C is a fourth above G (although truthfully, I actually think of this relationship in reverse: the chord that resolves to C is the one a fifth above it, namely G7).
Probably the most widely-played example of a chord progression employing this Circle of Fourths concept is the bridge from "I Got Rhythm", which goes D7-G7-C7-F7-Bb (each chord is played for two bars). Each chord resolves to the chord a fourth above it, until it finally concludes in the home key of Bb. Another example I was just thinking about is the blues song "Salty Dog", which I've heard played as E7-A7-D7-G7. Same idea, different chords.
One of the reasons these chord progressions are so popular is because dominant chords (i.e. 7th chords that resolve the chord a fourth above them) are rich with potential substitutions and alterations. Two of the most common include:
So a chord progression like in "I Got Rhythm", with its Circle-of-Fourths progression of dominant chords, is like a blank canvas of harmonic possibility. You can really go nuts exploring all the different opportunities it presents.
Update Regarding Tritone Substitutions
This is inspired by VarLogRant's comment below, which I think is close but not quite on the mark (I'm including this here because I ran out of characters in the comments section).
In functional harmony, a chord's role is not, in fact, determined by its root. Of the three main diatonic types of chords---major 7th, minor 7th, dominant 7th---all three have the same interval relationship between the root and the 5th; if I play just a C and a G, no one can tell if that's a CM7, a Cm7, or a C7 chord. Not until I add both the 3rd and the 7th can the listener distinguish between the type of chord (and therefore its functional role). But once I play the 3rd and the 7th, the chord's functional role becomes clear.
In fact, so long as I play the 3rd and the 7th of the chord, I can actually leave out the root and/or the 5th and the listener will still hear the harmony. So a chord's functional role in the harmony is determined by its 3rd and its 7th. Consequently, any two chords that have the same 3rd and 7th notes can be used interchangeably.
(Aside: In fact, my teacher taught me to avoid playing roots and fifths of chords when comping. He reasoned that the bassist would play roots and fifths, and if I avoided playing them, that left me with spare strings and fingers with which I could play chord extensions and alterations, i.e. color tones. But he also insisted I always play the 3rd and the 7th unless I was intentionally trying to be ambiguous.)
In a Dominant 7th chord, the interval between the 3rd and the 7th is a tritone. This is important because a tritone is symmetric, i.e. if you invert the notes in a tritone, you still get a tritone. A tritone is the only non-octave interval that has this property, and it means that if you play the 3rd and the 7th of a dominant chord, the listener has no way of knowing which of the two notes is the 3rd and which is the 7th. So you get one dominant chord in which the lower tone is the 3rd, and a different dominant chord in which the lower tone is the 7th.
For example: say I play an F# and a B as the 3rd and 7th of a chord. The interval between is a perfect 5th in one inversion and a perfect 4th in the other. Either way, the B must be the 3rd and the F# must be the 7th.
But say instead I play an F and a B. Which is the 3rd and which is the 7th? It's not clear, because in either inversion, the interval between them is a tritone. If the B is the 3rd, then the root is G, but if the F# is the 3rd, then the root is Db. Since the 3rd and the 7th are the two tones which determine the functional role of the chord, either the G7 or the Db7 will have the same functional role in the harmony and thus can be used interchangeably.
Just to extend Alex's answer with regards to choosing scales to play in your lead lines over any sort of progression really.
Modes of the scale break down as follows:
Using these a good rule of thumb is:
Major/7th/9etc: Major Scale / Lydian Mode
Minor/7th/etc: Minor scale(aeolian) / Dorian Mode / Phrygian
Minor/7th/9etc: Minor scale / Dorian Mode
Minor/7th/b9etc: Minor scale / Phrygian
7 chord(dominant(V)): Mixolydian
Pentatonics can pretty much always be thrown into the mix, and a good way to move around progressions is using tones from the chords being played, eg arpeggios.
This hasn't covered the harmonic/melodic minors, which in turn lead to other dominant modes, highly recommended to learn.