I have been to a bunch of aquariums. They pretty much all focus on larger specimens but they all have some amount of home aquarium sized fish and corals as well.
The best single tank I have seen is at the Georgia aquarium in Atlanta. Their coral reef tank has live corals and anemones in a tank that is something like 15 feet deep. I can’t imagine their electricity bill for the lighting needed to keep a bubble tip anemone healthy at the bottom of that tank. You can see a fair amount a home aquarium fishes like clowns hosting the nems, tangs, and even quite a few wrases. It definitely sounds like it best meets your criteria for home aquarium type stock.
But that’s just one tank. For best overall aquarium I have three contenders. The first is probably a big surprise. It’s the Wonder of Wildlife aquarium in Springfield, Missouri. You are probably wondering how such a small city can have one of the best aquariums. The answer is that it’s right next to where BassPro was founded. It’s multibillionaire owner has put serious cash into it. The layout is terrible but the displays are excellent.
A second great aquarium overall is the Shedd Aquarium in Chicago. This one is a must see just for it’s historical importance. It’s aquariums have an incredible array of aquatic animals too though. It’s not just interested in the good looking species either. They have all types. If you are also into fresh water fish keeping, they have excellent displays for those too. Not much in terms of live coral though.
The third great aquarium surprised me. It’s the Ripleys Aquarium in Toronto. I know that it sounds like a complete tourist trap being a Ripleys owned for-profit property right next to the CN tower. Trust me though; it’s excellent. They have incredible displays and even a tank where you can let a cleaner shrimp clean your hand. I highly recommend it.
So those are my picks. It’s sort of funny, all my favorites are so far from the ocean.