Left 4 Dead, TF2 and CS:S would be pointless because of the dial up and that the main feature of them is the multiplayer side, without which, TF2 and CS:S especially, are pointless.
Few good RTS games:
Setlers: Herritage of Kings
Civilization 4 + expansions (Warlords can be very fun and very tacticle if you do the warlord thing, it is very difficult but very fun)
C&C3 + Kane's Wrath
Some good single player RPG Games:
Fallout 3, which can potentially take a little while to get in to, but once you get into the swing of it, it is one of the best games ever made and there are thousands of useful and fun mods.
Borerlands, I didn't particularly like this, but the vast majority of people seem to, it is very stylised but quite fun, I just found it repetative after a short while.
Mass Effect, which is SO good, if you get it now you can carry your character on in Mass Effect 2 when it comes out.
Dragon Age: Origins, if you have the system for it, this looks like an amazing game, but the specs are a little hefty for budget gaming machines, but still check it out, looks well good
Also try Half Life if you haven't already, it is by far, imo and in the opinion of many many others, the BEST series of games to have ever been made