New to SQL, so please excuse imprecision in the question.
For "normal" queries, is SQL syntax mutually intelligible between dialects? To take a concrete example, would SELECT * FROM [Pending Scans] be valid in all common dialects?
Not looking for an exhaustive list!
No. This would be:
select *
from pending_scans;
Square braces are non-standard for escaping identifiers. The standard escape character for identifiers is the double quote, and most databases now support that.
I should note that for all but the simplest queries -- such as the one you have written -- slight differences between dialects make it a fool's errand to try to write complete portable code. For instance, function names are different, such as len() versus length(), and date/time operations are quite bespoke.
If you are writing an application that needs to support multiple different database types, a typical method would be to define the API as a set of views and use the views with simple SELECT queries (as in your example).