16

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
|improve this question

closed as off-topic by Ixrec, GlenH7♦, user22815, user40980, durron597 May 19 '15 at 4:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions seeking career or education advice are off topic here. They are only meaningful to the asker and do not generate lasting value for the broader community. Furthermore, in most cases, any answer is going to be a subjective opinion that may not take into account all the nuances of a (your) particular circumstance." – Ixrec, GlenH7, Community, durron597
If this question can be reworded to fit the rules in the help center, please edit the question.

20

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

|improve this answer
  • Its interesting just to see our two lists side by side – Chris Pitman Jan 5 '13 at 21:33
11

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
|improve this answer
  • Funny, your list is pretty similar to mine; though I put execution plans one step up otherwise we have basically the same. I almost put the 3rd normal form into intermediate, but i really think data modelling can't be ascribed to beginner/intermediate/advanced, it's simply too much of an artistic talent that is so gray. Knowing how to do 3rd normal is a skill, knowing when is an art. The how is relevant though and I almost put it into intermediate as well for that heh – Jimmy Hoffa Jan 5 '13 at 21:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.