I would say there are two types of things to know in regards to SQL (this is true of many technologies really), there are specific technical things like joins, subqueries, unions, etc which you understand or don't, and then there's things like database design and data modelling which has a gradient of skill in it, like art. You get better over time with those softer things, but never "know" them because there's no "they work this way" with them.
That said, here's a general layout I would go with, and am absolutely open to comments/thoughts from others where I may be wrong.
Beginner
- Where clauses (in, between, etc)
- Update syntax
- Inner vs left vs right join understanding and usage
- Syntax for altering and creating structures
- Temp tables and their usage
- Cursors
- Basic idea what indexes are for, though not how they work
- Understanding of what foreign keys are for and how to work around them (cascading deletes etc)
- Understands basics of transactions
- Understands constraints
Intermediate
- How indexes work, difference between clustered, non-clustered, etc, what a page is and how they layout
- Understanding of subqueries, and can think through using them in joins and wheres
- Pivots
- Can think through joining a table on itself when relevant
- Can generate complex data reports via group bys with aggregate functions
- Can do basic profiling just in a monitoring/debugging capacity like reading a log
- Understands the difference between OLAP and OLTP and when/where to use OLAP structures
- Knows how to use triggers and not to use them
- Understands transactions and can layer them handling failures up the stack
Advanced
- Can read an execution plan, and understand how the different parts of the query effect it
- Can tune queries with execution hints without screwing up performance (parallelism hints, index hints, loop hints, et al)
- Can profile and use traces for identifying and understanding statistics of executions under real-world load
- Knows what the data structures are on the disk
- Can use performance counters and understand what the database load and behaviour is from monitoring them
- Knows how to design an OLAP cube and do advanced data mining with one
- Knows how to use triggers and how to use them safely, with minimal risk
- Knows how to use distributed transactions even with layers
That's all I could come up with off the top of my head. Please leave comments mentioning others I missed or if I put something in the wrong place. I'm not advanced enough to know a huge list of advanced techniques to put down heh
