There are two types of frogs commonly sold at pet stores that stay completely submerged in water their entire life.
There is the African Dwarf Frog, which is a small aqautic frog, usually grayish brown in color. They get to be about 2 inches long. These guys are more readily sold than the African Clawed Frogs. The ACFs are much larger, capable of getting 8-10 inches long when stretched out. They are also usually sold as albinos.
If you get either frogs, keep in mind that they well eat basically anything that fits in their mouth. That means any smaller fish, smaller frogs, etc. So be careful with what you keep the frogs with. They CAN be kept in community tanks with similar sized, or bigger fish, as long as one animal can't be eaten by the other. Obviously, you will need to be sure you have enough room for them too.
They well eat just about anything. The larger ACFs may eat feeder fish, various insects, fish flakes, sinking pellets, bloodworms, etc. The ADFs may be feed fish flakes, sinking pellets, small crickets, or bloodworms.
And yes, they produce waste just like anything else, so be sure not to add too many frogs or fish for whatever tank size you have.