Where can I learn the SQL way to get data out

Question

There's a gap in my SQL knowledge I'd like to fill and I'm after recommendations on where to find resources, eg. websites, how-tos, books, etc.

I've been using SQL databases for a long time. I'm quite comfortable with: basic SQL and its syntax; creating tables and indexes; inserting data; and basic DBMS maintanenance.

Where I struggle is getting the data out for anything beyond basic SELECT statements. I've recently got my head around some basic INNER JOINs and GROUP BY so I can use commands like SUM and MAX. But the "SQL way", ie. set logic, eludes me. Is there a solid overview of this stuff so I can properly understand set logic, JOINs (all types), nested SELECTs, WITH, CROSS APPLY, ROW_NUMBER(), etc.

I've found a couple of good questions on SO (here and here). But the answers are a bit too general. I don't want a beginners guide to SQL or databases. I get that. I'm after stuff on the relatively narrow topic of smart queries. Vendor-agnostic stuff would be preferable. Many thanks.

In summary, my question is: can you recommend some good resources (online or print) to help me learn how to take my SQL queries beyond the basic level. That is, something other than introductory/basic texts. As I said, I'm comfortable with the basics, I need intermediate to advanced coverage of SQL queries.

UPDATE 2009-10-12 re Selected Answer: A wide variety of answers, mostly recommending books, rather than online resources, which was a bit of a surprise to me. Many authors were recommended (here and in other SO questions). The two most popular were Chris Date and Joe Celko. It was difficult to pick not having read either. What settled it in the end was DBA friend had a copy of SQL for Smarties I could borrow, but nothing from Date.

Answer 1

http://www.amazon.com/Simply-SQL-Rudy-Limeback/dp/0980455251

Answer 2

Jeff Atwood (one of the creaters of this site), has a visual explaination of the various SQL Joins you can do. Visualizing what the joins do is a big step to understanding how to use them.

Answer 3

"Vendor-agnostic stuff would be preferable."

C.J. Date, SQL and Relational Theory.

"If you merely want to know how to work within current implementation constraints without the larger picture, skip this book. If you want some fascinating, and sometimes difficult to grasp background and theory that will likely make you a better database wizard, this book is likely to help you get there."

"... what I would say is the "Code Complete" manual for SQL. Not for the layman, this work explains [in intricate detail] the whys and wherefores of SQL, and how to over its many idiosyncrasies."

C.J. Date, Database in depth.

"One warning, you wont be spoon-fed here. The material can be challenging, and Date expects you to use your brain. This isn't SQL For Dummies. The real advantage you will gain by reading a book like this is that you will understand the mathematical and logical reasoning behind practical design principles"

"Author concentrates too much on minute details which are in many ways irrelevant/implicit to many readers I feel."

Answer 4

Suggest you look for some performance tuning books for the database you are working with. They tend to cover advanced concepts. Something like http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr%5F1%5F3?ie=UTF8&s=books&qid=1255021788&sr=8-3

Answer 5

I'd say that the only real way of learning past the basics is to practice - Have a go at a small project that actually does something (preferably something useful) and just research on the best way to solve each problem you come across. That way you get the see the real world implications of what it is you do (performance, how difficult it is to maintain / debug / change) rather than just memorising theoretical "use UNION in this situation"

Answer 6

It's somewhat hard to judge your level from the information in your question, but if you are really familiar with the basics of SQL, then check out Joe Celko's SQL for Smarties. It's very good if you're interested in learning to think in a SQL-like way and tries to avoid vendor-specific references.

Answer 7

Your question is quite general, but if I understand correctly you're having a hard time finding the association between asking complex questions and writing a SQL query that will give you the answer.

I think you need to look back to relational algebra and relational calculus (on which SQL is loosely based). The reason is because relational algebra and calculus are directly connected not just to sets, but also to logic. C.J. Date has an excellent book "Logic and Databases" that helps bridge the gap between logic (the way you reason) and databases. The book is very accessible for someone interested in databases even if you have no background in logic at all.

After you have that basic knowledge, you will start seeing a lot more possibilities and complex formulations will become natural expressions of your own reasoning. Then take a look into the non-relational parts of SQL, like window functions, which operate on lists (or maybe a set of lists); and WITH RECURSIVE (a.k.a. CTEs) which make SQL computationally complete. As Date points out though, some of the non-relational parts of SQL (like multisets and NULLs) can make it more difficult to reason.