Where can I find out the SQL way to get data
There's a gap in SQL knowledge that I would like to fill, and I get guidance on where to look for resources, for example. websites, instructions, books, etc.
I have been using SQL databases for a long time. I really like: basic SQL and its syntax; creating tables and indexes; data insertion; and basic DBMS maintenance.
In cases where I am struggling to get data for anything other than basic SELECT statements. I've recently embraced some basic INNER JOINs and GROUP BY so I can use commands like SUM and MAX. But the "SQL path", i.e. a logical setting eludes me. Is there a general overview of this stuff so I can correctly understand the set logic, JOINs (all types), nested SELECT, WITH, CROSS APPLY, ROW_NUMBER (), etc.
I found a couple of good questions on SO ( here and here ). But the answers are a little too general. I don't want a beginner's guide to SQL or databases. I understand. I am after material on the relatively narrow topic of smart queries. The seller's agency would be preferable. Many thanks.
In conclusion, my question is, can you recommend some good resources (online or print) to help me learn how to accept my SQL queries outside of the basic level. That is, something other than introductory / basic texts. As I said, I like the basics, I need intermediate and extended coverage of SQL queries.
UPDATE 2009-10-12 re Selected Answer: Wide range of answers, mostly recommending books rather than online resources, which came as a surprise to me. Many authors have been recommended (here and in other SO questions). The two most popular were Chris Date and Joe Celko. It was hard not even to read. What settled it was in the end the DBA friend had a copy of the SQL for Smarties that I could grab, but nothing from Date.
source to share
It is somewhat difficult to judge your level from the information in your question, but if you are really familiar with the basics of SQL then take a look at Joe Celko SQL for Smarties. This is great if you are interested in learning to think in a SQL way and trying to avoid referring to specific vendors.
source to share
This should serve as a good introductory text:
Sams teach yourself SQL in 10 minutes
I was also very pleased with the Head First series for introducing new concepts:
source to share
Jeff Atwood (Jeff Atwood) (one of the creators of this site) clearly explains the various SQL joins that you can perform. Visualizing what unions do is a big step towards understanding how to use them.
source to share
I would say the only real way to get to know the basics is to practice - go to a small project that actually does something (preferably something useful) and just research the best way to solve every problem you come across. This way you see the real implications of what you are doing (performance, how difficult it is to maintain / debug / change), rather than just memorizing the theoretical "use UNION
in this situation"
source to share
Your question is quite general, but if I understand correctly, you are having a hard time finding a connection between asking difficult questions and writing a SQL query that will give you the answer.
I think you need to look back at relational algebra and relational calculus (which SQL is loosely based on). The reason is that relational algebra and calculus are directly related not only to sets, but also to logic. CJ Date has an excellent book, Logic and Databases, which helps bridge the gap between logic (how you do it) and databases. The book is very accessible for those interested in databases, even if you have no background in logic at all.
Once you have this basic knowledge, you will begin to see many more possibilities, and complex formulations will become natural expressions of your own reasoning. Next, consider the non-relational parts of SQL, such as window functions that operate on lists (or maybe a collection of lists); and WITH RECURSIVE
(aka CTEs) that make SQL computationally complete. As the date points out, some of the non-relational parts of SQL (like multiset and NULL) can make reasoning difficult.
source to share
The Takahashi and Azuma Manga Database Guide is surprisingly helpful in getting the basic concepts across. I've been working with SQL for years and read this to see if it might help prepare one of my sons for a database course this semester. I was very impressed with the amount of material as well as the clarity and effectiveness of the presentation. Goofy, but pretty good.
source to share
"Component agnostic would be preferred."
CJ Date, SQL and Relational Theory.
“If you just want to know how to deal with the current implementation constraints without magnifying the image, skip this book. will probably help you get there. "
"... what I would say is a guide for Code Complete for SQL. Not for the layman, this work explains [in complex detail] the whys and wherefores of SQL, and how with its many features."
CJ Date, Database Depth.
"One word of warning, you won't be spoon-feeding here. The material can be tricky and Date expects you to use your brain. This is not SQL for dummies. The real benefit you get from reading a book like this is that you will understand the mathematical and logical rationale behind practical design principles "
"The author concentrates too much on the smallest details, which in many ways are irrelevant / implicit to many of the readers that I feel."
source to share
We suggest you search for a few books on performance tuning for the database you are working with. They tend to cover cutting edge concepts. Something like http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_3?ie=UTF8&s=books&qid=1255021788&sr=8-3
source to share