Yes, the idea is that you first make a query that returns a single row with a "component" column, and potential other top-level parameters for the component. This is translated to some HTML that displays a piece of user interface on the page.
Then, you can make subsequent queries that can return multiple rows, and fill the component with data, displayed inside the piece of UI created by the component.
For instance, you can run
SELECT 'list' AS component, 'Popular websites' AS title;
This will open a list component. Then
SELECT
topic AS title,
'https://en.wikipedia.org/wiki/' || topic AS link
FROM interesting_topics;
will populate the list with entries from your interesting_topics database table.
About putting all the app logic in SQL queries: yes, there are some apps that would be very hard, or impossible to build in SQLPage.
But for most simple Create-Read-Update-Delete apps, you don't really have any complicated business logic, and not having ANY boilerplate to get started is I think what makes SQLPage compelling. And when your app later grows to have more complex business logic, you just move from SQLPage to a full-fledged backend framework, but you don't lose the work you have already done structuring your database schema and the core queries that your app will need.
The goal of SQLPage is not to replace something like Django, for instance. It is for django developers to be able to quickly test out their app idea, and for people who have no idea what "backend development" even means to still be able to create simple apps.