In Call of Duty 4: Modern Warfare, there's a really great sniping level in the single player game. Probably one of the best sniping levels of all time. You have to stealth your way into an enemy city, scout out a shooting location, then take down a target with a 50 cal sniper rifle at something like a mile, compensating for the Coriolis effect and all kinds of crazy crap. A nice bullet cam as well.
There's also a few other places in the game with great sniping opportunities. Def recommended.