I have been on the Cayman Aggressor three times. There are plenty of large Nassau grouper, a few Caribbean reef sharks, a fair amount of nurse sharks, plenty of larger snapper, a few eagle rays and southern stingrays. I once saw a HUGE Goliath grouper off of Little Cayman. I have never seen a whale shark or any other shark species except the two I mentioned above.
I had a GREAT time on all three trips. I especially love the Little Cayman diving. You can go to Aggressor's site and read the Captains' logs (they have them on here, too). That will give you a good idea what to expect. Plenty of former passengers have posted videos on Youtube, as well.
Cheers and have an awesome time!