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 clausesUpdate syntaxJoinsAlter and Create statementsTemp tablesCursorsIndexesForeign keysConstraintsTransactionsSubqueriesPivotsAggregate functionsProfilingOLAP and OLTPTriggersExecution plansExecution hintsPerformance countersNormalization
Are these SQL concepts for beginners, intermediate, or advanced developers?
learning;database;skills
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.BeginnerWhere clauses (in, between, etc)Update syntaxInner vs left vs right join understanding and usageSyntax for altering and creating structuresTemp tables and their usageCursorsBasic idea what indexes are for, though not how they workUnderstanding of what foreign keys are for and how to work around them (cascading deletes etc)Understands basics of transactionsUnderstands constraintsIntermediateHow indexes work, difference between clustered, non-clustered, etc, what a page is and how they layoutUnderstanding of subqueries, and can think through using them in joins and wheresPivotsCan think through joining a table on itself when relevantCan generate complex data reports via group bys with aggregate functionsCan do basic profiling just in a monitoring/debugging capacity like reading a logUnderstands the difference between OLAP and OLTP and when/where to use OLAP structuresKnows how to use triggers and not to use themUnderstands transactions and can layer them handling failures up the stackAdvancedCan read an execution plan, and understand how the different parts of the query effect itCan 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 loadKnows what the data structures are on the diskCan use performance counters and understand what the database load and behaviour is from monitoring themKnows how to design an OLAP cube and do advanced data mining with oneKnows how to use triggers and how to use them safely, with minimal riskKnows how to use distributed transactions even with layersThat'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