For me core tSQL are joins & nesting, understanding where clauses, the DML statements, the use of views and stored procedures, grouping as well as knowing how nulls work and are handled. Some constructs like CTEs, pivot and windowing functions are nice to have and make life easier. Later on comes database design, the importance of datatypes, constraints and referential integrity.

What is more important for me is being able to handle complexity and being able to think in sets. If given a complex query that is running slowly, they can rewrite it so that it runs more quickly or with much less overhead. When given a complex task, they can break it down into its logical constituent parts.