{array "..."} runs its parameter as a query and makes every row it answers an occurrence of the tag: the content renders once per row, with the row's columns as fields - {$name}, {$email}. It is the template form of db ( "ARRAY ..." ); the tag name is the command word, so the parameter starts after the SELECT.

A query that answers no rows renders nothing, or the @else@ branch when the content has one. Every handling option works on the rows as on any data - first=, sort=, where=, page= with a {pager} after it - and the loop properties are there: {first@array}, {current@array}. Sorting and limiting are usually cheaper in the SQL itself.

Most pages fetch their rows in PHP instead - $orders = db ( "ARRAY * FROM orders" ); and then {orders}...{/orders} - which keeps SQL out of the template and lets a request value go in through a placeholder. {array} is handy for a quick listing. With name= the rows can come from the page's sample in the designer preview.