The SQL is declarative, and non-procedural
This is a widespread myth. SQL is an imperative language whose domain is (mostly) relations rather than lower-level objects. It is only "declarative" in the sense that C is declarative: you tell the compiler what you want done, and it chooses an execution plan for doing it, namely a machine language program.
As for "procedural", it seems nowadays to be a synonym for "imperative".
Isn't all
declarative, 'non-procedural' code usually actually turned into
something procedural behind the scenes?
Yes, if it is compiled; no if it is interpreted. This distinction is meaningful but relative: languages are in all cases compiled into something lower-level which is then interpreted, whether by hardware or software. We call an implementation an interpreter if it does only a small amount of compiling; we call it a compiler if the interpreter is hardware or very low level.
Isn't declarative,
'non-procedural' code actually an illusion, wrapping procedural code
under the hood - smoke and mirrors?
On modern pipelined CPU the so-called "machine language" is also smoke and mirrors. "You don't fool _me_, young man. It's turtles all the way down."
