This is arguably the most important page on this site, since Counter-Strike: Source is a truly competitive game. People don't usually play it for sheer enjoyment, they play it to show off and increase their skill. Here is a link to the best game-play tutorials I have found anywhere on the web.
https://www.youtube.com/watch?v=DtzAdanOj0M&list=PL-YVLXtSFI9hFKWmUP-_JB_CFBt7OPsLe&feature=mh_lolz
To see some pros in action, click on the links below. {Please note that the first video contains fowl language. If this is offensive to you, you might want to mute the sound, or not watch it.}
http://www.youtube.com/watch?v=3Ss7Bxp8bxI
http://www.youtube.com/watch?v=t5yw9LpPVGU
Here is a video with some helpful tips concerning how intermediate players should practice. {Please note that this video contains some fowl language.}
This is a good video teaching you how to fire more accurately with the M4A1 Carbine and the AK47. It contains no fowl language.
http://www.youtube.com/watch?feature=player_embedded&v=Y4TVegjgvXc