In your experience, how long does it take for a professional procedural coder to become a professional SQL coder? And what's the best way to make the transition? (Disregarding database design skills).
What percent of programmers are professional-level at both?
a. Self evaluation.
b. Unbiased evaluation.
(In my experience, one problem is that procedural programmers don't appreciate how much they don't know yet, and how much of what they do know doesn't apply.)
It takes a lot of effort and understanding to think in terms of relational sets. This impedance mismatch is a big reason why ORM is popular in companies with lesser SQL skills..
I know some of both. Professionally speaking I know them in depth. Technically speaking I feel I am often just touching the surface and barely making a dimple.
One of the best ways is the hard way, as in having to come up with some results. One piece of wisdom if you let me pass is that coding T-SQL is much more empirical than theory based. That is, there are often choices to tune that are hard to choose between in theory but if you run real data results it becomes obvious what fits.
One of the good things about SQL is that it has less keywords than many of the programming languages that you come across. Learning the syntax is pretty easy, it's the application, as dove alluded, that is pretty hard.
How long does it take for a professional procedural coder to become a professional SQL coder?
That depends on the programmer. I would say that you would need to have created at least a two each of these database objects against production data:
So, it's not really a matter of time, but a matter of knowledge.
I thought I knew how these work, but I'm now working with 100 to 800 million row sets, and what worked before is inadequate for these huge result sets. Thankfully, a lot of database management tools are a LOT better now days. That means that I spend less time scratching my head, and more time coding.
I think the hardest part of these are the database specific extensions. Those are all different between database vendors. I've only worked with SQL Server, Oracle, and Interbase/Firebird. After a while I gave up trying to keep up with all of them, and just concentrated on SQL Server, as that is what is most common in my metro area.
It depends on your definition of "professional".
If you mean "employable", then a couple of weeks' intensive study/practise would put you ahead of most practitioners. Normalisation, basic relational theory, ANSI SQL and the variations inflicted by at least one mainstream DBMS provider, plus acquiring some understanding of a platform's query optimisation and indexing functionality.
However, in many corporate environments (mine included) you'd be hard-pressed to claim you were any kind of development professional unless you had a reasonable understanding of SQL and relational databases; SQL is how these places talk to their data.
I'd go for at least one of Oracle, IBM DB2, MS SQL Server, MySQL, Postgres, all of which have free versions that you can use to start the learning process.
While self-study can teach you a lot, I'd also recommend attending courses if you can get them funded: the week I spent learning entity modelling has stood me in good stead for over (gulp) 20 years now, while a decent practical course in platform-specific performance and optimisation techniques would be a useful kick-start.
Grokking SQL is somewhat different from grokking most programming languages as you need to obtain a feel for set operations. Having said that it's really no more a difficult mind-set to understand than any of the major coding paradigms - such as moving from procedural code to oops for example (and for what I've seen so far easier than functional programming :-) )
A sign of someone who has not fully grasped SQL is the use of procedures and the like which operate by looping through records when set operations would be simpler and faster. One tends to come across that rather a lot. OTOH I find a good sign is the appropriate use of UNION and HAVING clauses - both a little outside the beginners statement set. I've seen code written by experienced programmers that displays poor understanding of SQL and the converse too.
A good beginners mind exercise, and one if the first 'aha' moments for me was when I understood the relationship between a cross join and a restricted join. I think this basic concept is actually easier to understand in the old SQL syntax, so for example we cross join tables a and b
select * from a, b
we get the Cartesian product of a and b. Putting in a join condition
select * from a, b where a.x = b.x
Can be conceptually viewed as processing the Cartesian product then filtering it on the where condition. Blindingly obvious you may say (although it's surprising how many people first coming to SQL don't understand what a Cartesian product is, which makes this exercise worth it for that concept alone), but it's a useful train of thought that once grasped makes understanding such things as self joins and complex multi-table constructs a little easier to get your head around.