Tim Ingersoll once bubbled...
Little Cayman has everything except the big stuff. If you could import some sharks, whales, dolphins and eagle rays it would be perfect...
The Sister islands do have had their occasional pelagic visitors. They're just not omnipresent, like in the Galapagos.
On sharks, they're around...just not too frequently sighted. Best bet is to do a late afternoon dive on LC when the Agressor is "accidentally chumming" as they prepare dinner. Aunt Shaw's on the Brac also do an incidental feeding (guests watch from the balcony). Supposedly, Point of Sand on Little Cayman at night is another hot spot. Reef sharks are the most common, but there's the occasional hammerhead...
http://tinyurl.com/njde
Sightings of other shark types (Lemon, Whale) do exist, but are very rare.
On whales and dolphin, they're a rarity. The dolphin which had been hanging around the Brac is long gone. He got bored of the 356 and started hanging out further up at Stake & Spot bay, and reportedly started to accompany the Brac fishermen on their runs out to the south (towards Honduras); he apparently left that way. The islands are apparently on a migration route of a family pod of killer whales that come through seasonally, but since its a 1-2 hour event, they're not seen every year.
Around a decade ago, there were one or more Manta's. Stories differ as to what happened, but there was a fisherman who hooked one off the drop-off about the time that Molly disappeared. Here's a photo of Molly from around 1991:
http://tinyurl.com/njcv
What are common sightings are groupers, hogfish, various Angels and southern stingrays. Slightly less common are turtles, tarpon, eels, eagle rays. As a rule of thumb, I always see all of these on a trip, plus lots and lots of "normal" stuff (including Sea Cucumbers). Some stuff is seasonal too, so it can also depend on the time of year that you're visiting. For example, if you want caves full of silversides, go in the summer.
BTW, on Turtles, check out this webpage:
http://www.seaturtle.org/tracking/index.shtml?project_id=3
IMO, there's more "bigger" stuff on LC and the Brac than on some other destinations, for example, Bonaire.
-hh