Yes, there are CTE and recursive queries, and various DBMS also offer views. There are even table-valued functions. These things are not widespread, and differ

Yes, there are CTE and recursive queries, and various DBMS also offer views. There are even table-valued functions. These things are not widespread, and differ by implementation, and the way these are used by clients are copy-and-paste. Something as thoughtful as ZetaSQL https://github.com/google/zetasql https://github.com/google/zetasql does not have mechanisms for structuring (modules, packages, interfaces). SQL will not, cannot evolve into such a direction (or, anything that evolves, will not be recognizable as SQL).