What are the best resources for getting better with SQL?

Question

I can write basic queries in MySQL, PostgreSQL, and MSSQL, but that's about it (as you can see from some of my questions here). But as soon as you get anything more advanced than a basic query, I fall apart. So what are some good resources to read to improve my SQL skills? I'm looking for books, websites, PDFs...anything. Free would be best, but I'm willing to look at anything.

Answer 1

The Art of SQL was a good read.

Answer 2

SQL for Mere Mortals will take you from beginner to moderate.

The SQL Cookbook will take you from moderate to advanced.

SQL Tuning will make you a master (Disclaimer: I am not a master).

Also check out Databases in Depth if you just want to get a really solid foundation in how relational databases work. It's not heavy on SQL, though.

Answer 3

The best thing you can do to learn is first to specialize. Learn one of the SQL variants, and learn it to the best of your ability. The others will come by identifying their similarities.

Check out the documentation on left, right, equi, inner and outer joins as well as group by, having and where clauses. Learn a thing or two about subqueries, views, and stored procedures. Once you've hit all the goodies there, start to delve into the DBMS specific functions like date_format, to_char, and coalesce. If you get really daring, you can start looking into triggers and views.

You can learn a lot just from the documentation. I know the Oracle and MySQL docs are pretty extensive, and I'm certain the MSSQL documentation is quite good as well. I've had less luck with PostgreSQL, but the community fills in the blanks there. The key is to know what to look for.

If you want information from two or more tables that are related by one or more fields, check out joins.

If you want to make something happen after an event in the database, triggers is it!

If you need to roll up some data for reporting in an average or stddev you can use aggregate functions and group by clauses to get a great result.

If you need to filter data, where, exists and having are just what you need.

Hope that helps a little. I know that if I knew what to search for, it would have made my life 10x easier. Luckily, there were other developers and a university professor to help me out when I needed it most. :)

Answer 4

I tend to have two types of references that I use for SQL. Practical and theoretical.

The practical references help you get the immediate task done, but leave you hanging when it comes to real knowledge. The theoretical texts help you learn about the science of data, but do little for helping with any specific problem.

Practical Referecees

The Guru's Guide to Transact-SQL by Ken Henderson

Trees and Hierarchies in SQL for Smarties by Joe Celko: Nice discussion of a frequently difficult topic in SQL

MySQL Cookbook by Paul Dubois

and of course MS Books Online

Theoretical References

Practical Issues in Database Management by Fabian Pascal: great stuff, almost implementable theory.

Database in Depth: Relational Theory for Practitioners by C.J. Date (warning: this is a difficult read)

And if you really want to drink the KoolAid, Rel

Answer 5

In order to really get good with SQL, you should become familar with the internals of databases - indexing, optimizing, paging, etc.

As a solid explanation of how databases work and sql relates to them, the best books I've ever read are the ones by Robert Vieira.

They are all on MSSQL Server, but still a really great explanation of the basic internals of databases, and the knowledge I have carried with me though a number of platforms.

http://www.amazon.com/exec/obidos/search-handle-url/ref=ntt_athr_dp_sr_1?_encoding=UTF8&search-type=ss&index=books&field-author=Robert%20Vieira

Answer 6

There are a lot of good books on the subject which will get you moving in the right direction, although it's sometimes hard to find one that goes beyond the simplest of queries.

Others have made some good suggestions here, so I won't list any more books, but in my opinion the best way to get good is through practice. Start searching through this website and when someone posts a SQL question about a query, try to figure it out (write an actual script and test it in an actual database). See if you got close to what others have answered. You can also do the same with the various SQL newsgroups on Google groups. comp.databases.sql and comp.databases.ms-sqlserver are pretty good. Although the second one is vendor specific, you can skip those posts and zero in on the generic SQL query questions.

Your goal is to eventually think in a set-based way rather than a normal procedural, which is where most people struggle.

Good luck!

Answer 7

Go to www.SqlTeam.com and start answering your questions. Give it your best shot in solving the problem. Remember that more experience people will correct you and this way you will learn from them.

Answer 8

For me, SQL became a lot clearer once I understood how much it is rooted in set theory.

SELECT statements are just clever ways to describe Venn Diagrams. Once I grokked the idea that everything in SQL is just returning a dataset (even if it is a subquery, or a view, or a table), then things started to fall in place for me.

Of course, different things are going to be the eye-opener for different people... but that was it, for me.

Answer 9

I would recommend SQLServerCentral

Its primary focus is on MS SQLServer but you can learn a lot about general TSQL as well. The site is free to use but the registration is required.

Answer 10

I totally agree with anopres in that there are two pieces to SQL mastery, theoretical and practical.

I use the Practical SQL Handbook for the theoretical side and any number of O'Reily Oracle books like Mastering Oracle SQL for the practical, but this is entirely dependent on which database platform you're implementing for. They're all different enough that you really do need a native reference for the DB you're working on, although for this bit you could easily default to the usually pretty good online documentation offered by the vendor whose database you're using.

A few years back I landed at an organization that was extremely Oracle heavy and found myself feeling very much in over my head - I knew how to string together a basic query, but things like foreign keys, constraints and outer joins made my head spin.

That's when going back to the basics really helped. Re-reading the first few chapters of the handbook gave me a much more fully grounded sense of what was happening and brought me up to speed on some of the more advanced concepts I was stuck on.

Good luck and hope this helps!

Answer 11

Anything written by Joe Celko. He's also got old columns out on the net you can read.

Answer 12

The book suggestions by other posters were all great ones, some of those titles are on my bookshelf. You will learn the most by being hands-on. Initially I learned the most by spending time in newsgroups and reading as much as possible. Some answer-posters will start to stand out to you and you may end up looking those people up each day just to see what they've written. Stackoverflow seems like you've already gotten started in a great place. Stick with it.