Tips on becoming an SQL guru

Question

While writing an application of mine, I realized I was being silly by querying the database several times in some functions. I know I can just pack everything into a single query, and wind up with this nice dict of all the information that function needs. So I set out to accomplish this in a function where a user can share his uploaded files with other users of my application. But I was instantly stuck. I asked this question and came up with a hack solution myself. I wish I was more proficient at SQL, and that I could write it with the same speed and knowledge that I do other programming languages that I use.

So I ask, what are some tips at becoming an SQL writing guru? I find that, for every problem I have, I just google that problem, and maybe get a solution that doesn't work for my particular DBMS (currently I'm using sqlite3, since it's out-of-the-box with Python).

I'm curious about all the treasures in SQL that a beginner might not even know exists. What books should I read? Are there any invaluable online resources for becoming awesome at writing SQL? Any that go through a variety of DBMSs? I want to know which features of SQL to use in which situations, which ones are more efficient, etc.

Answer 1

I am by no means a SQL guru, but I improve daily. I read a ton of blogs from well know, respected DB 'gurus'.

I also find that spending time just trying to help people with SQL motivates me to learn what I cannot answer.

I say go for the internals. Find out how it works and you will be much more prepared to pick best solution for your scenario.

Finally, learn with DB work it always DEPENDS!

Answer 2

I think that as with everything, you need to put in the hours trying to find the 'best' solution to every problem that comes your way - that is, researching it, perhaps trying the options. Fixing difficult performance issues and bugs will also lead you to do more research, try more things; what I'm trying to say is there's no easy way, no cheat sheet, just a few facts and a lot of experience to gain.

The best skill you can learn I think you already have (by asking that question) - you need to be driven to find the best answer, the correct way for achieving the same task in different circumstances. Then it's just putting the time in. IMO.

Answer 3

When it comes to books I would recommend anything by Joe Celko:

http://www.amazon.com/Joe-Celko/e/B000ARBFVQ/ref=ntt_athr_dp_pel_pop_1

In particular you might want to start with

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

It attempts to teach you how to think in Sets when programming in SQL, which will provide you with different ways to solve problems in SQL that are not apparent if you only look at programming SQL in a procedural manner.

I would also recommend reading as many blogs about SQL, the following is a list of Articles by Joe Celko:

http://www.simple-talk.com/author/joe-celko/

Answer 4

Think: All my database problems can be solved by a query. I never ever need to write procedural code. Somehow, it can be done.

And then by worrying over the hard problems and thinking of subqueries and derived tables and joins and all that sort of thing until your head explodes eventually you come up with a solution. Repeat until guru.

Answer 5

Work with a SQL guru!

It's always very important to have sql guru's near you.

Answer 6

I read hackMySQL blog. Of course the official reference are always very good too.

Answer 7

I can't speak for anything SQL other than Microsoft SQL Server - but if you're interested in that, follow blogs, read articles, ask questions here :-)

Blogs:

• Kimberly Tripp - Queen of Indexing!
• Paul S. Randal
• Bob Beauchemin - SQL Server from a developer's perspective
• SQL Blog aggregated blogs
• Simple Talk

Answer 8

I always try to understand what is happening in the background so that I can decide what solution is best for a situation. TRY A LOT measure/compare what you tried.

Things you might want to read:

• http://www.w3schools.com/sql/default.asp explains a lot about SQL commands
• http://en.wikipedia.org/wiki/View_(database) Views
• http://en.wikipedia.org/wiki/B-tree so you understand a little how most databases store their information
• http://en.wikipedia.org/wiki/Stored_procedure Stored procedures are useful
• http://en.wikipedia.org/wiki/Database_trigger So are triggers
• http://en.wikipedia.org/wiki/Index_(database) Indexes can help you speed things up a - lot
• http://en.wikipedia.org/wiki/Relational_model Your model is important. This will determine whether or not your database is going to be fast or not
• http://en.wikipedia.org/wiki/Database_normalization If you have a database model and want to improve it you might want to try normalization. But be careful not to do to much of it ;)

Dont stick with just one database. Try more. They all have their own dialect and strengths /weaknesses

Answer 9

Speaking as a non sql guru. I think you should narrow your ambitions to a single RDBMS.

One of the main reasons I don't classify myself as a guru is that I am constantly switching between DB2, Oracle, Sybase etc. etc. After you get past the basic ANSI SQL all the implementations are vastly different the ninja tricks for getting a DB2 system lighting fast have little effect on Oracle and vice-versa.

The db gurus that I do know have hitched thier carrers to a single DBMS and learned all thay can about it.