Are these SQL concepts for beginners, intermediate, or advanced developers? [closed]

Question

I've been learning SQL recently and practicing with MySQL/Postgres and soon Oracle DB. I've also search the web for a 'road map' study of databases but couldn't find one, unfortunately.

I want to understand where and why particular database concepts fall on the scale from beginner to intermediate and advanced. I'm thinking about relational databases for the most part.

Please explain how to lay out skills listed below in a progression of Beginner -> Intermediate -> Advanced for what level of developer should know them:

• Where clauses
• Update syntax
• Joins
• Alter and Create statements
• Temp tables
• Cursors
• Indexes
• Foreign keys
• Constraints
• Transactions
• Subqueries
• Pivots
• Aggregate functions
• Profiling
• OLAP and OLTP
• Triggers
• Execution plans
• Execution hints
• Performance counters
• Normalization

Answer 1

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

Answer 2

Here is the scale I use while interviewing candidates. This is obviously my own personal opinion, scales can easily vary:

First, I am looking for database skills, not specifically "SQL".

Beginner:

• Knows basic SQL syntax
• Can select and filter data across multiple tables
• Understands inner/outer/cross joins
• Vague understanding of normalization, indexing, transactions, locking

Intermediate:

• Understands differences between procedural and declaritive styles for SQL
• Experience with at least one DBs SQL extensions (PSQL, TSQL, etc)
• Understands, and can design a database in, third normal form
• Understands and can implement indexing
• Understands transactions, and can use them in a single database
• Knows when and how to analyze an execution plan

Advanced:

• Understands when and how to denormalize a database
• Understands how to setup distributed transactions across a database and integrated systems
• Can setp a database for high availability, data recovery, etc
• Deep unuderstanding of each DB engine's internal workings (at least two)
• Extra points for significant exposure to data warehousing and reporting
• Extra points for significant exposure to variants of NoSQL, and a rational set of criteria for when to use what