There’s a few different applications, but they can be grouped in to the major categories of water movement and/or aesthetics. In the most basic form, an air stone is connected to the pump through flexing tubing and placed in the tank. This produces a continuous stream of tiny bubbles that rise to the surface. This pulls water from the bottom of the tank up to the surface where both the bursting bubbles and associated current agitates the surface, encouraging gas exchange. This prevents stagnation and keeps the tank water well oxygenated. In more elaborate setups they’re redundant as the primary pump(s) from the filtration system do the same thing with a lot less mess, but an air pump connected to some sort of a battery backup can be the difference between a big, heavily stocked tank making it through a power outage or not. They can also be used to drive certain types of filters, like under gravel filters or some of the older style protein skimmers from the salt water side. And also some people simply like how a curtain of bubbles in their tank looks.