1) I think a simple new kind of query hint could go a long way: Declare a table as being "infinitely large" from the perspective of the query planner.

So, if a query caused a full table scan or similar against the table, it's an error, regardless of the actual content.

A lot of "typical" backend code would then simply require you to make the right indices you need to support your queries, and the query planner would be forced to use those indices in all situations.

2) You can already sort of do what you say; that "higher level imperative language" is available simply by breaking your query up into smaller chunks.

At least in MS-SQL (which is what I know), simply do

    declare @tmp1 as table (...)
    insert into @tmp1 ...
    declare @tmp2 as table (...)
    insert into @tmp2 ... from @tmp1 join ...

and so on; if you make each of those steps small enough you pretty much have the imperative language.

3) That said, I think SQL is a wonderful idea, I am also super-productive in implementing business logic in it; but it is a horrible language; the learning curve is so much higher than it needs to be and so many silly pitfalls. So fully support pushes for better languages.