Besides the school of HARD KNOCKS, I'd like to have recommendations from our SQL folks on what would be the best way, school, videos, or whatever else to learn and fully understand Transact SQL. The basic stuff I understand, but when it comes to complex queries, where you have a couple nested subqueries, derived tables, etc, or even Common Table Expressions (CTE), I am a bit befuddled right now.

I am in a T-SQL class at a local junior college and until now, I've been okay, We are almost halfway through our classes and I and many of the students are struggling to understand some complex queries; no matter what we write it isn't what the professor wants. The instructor was not in class for 2 out of 6 classes so far, once for the recent Cesar Chavez Day holiday, and once to attend some Microsoft conference in Florida. In addition, the course time in class once per week is only for 1.5 hours, hardly enough time to do a 3 semester hour course justice. They call this "DISTANCE LEARNING". Well, the instructor is so distant that we cannot learn what we need to learn. I am 56 years of age and am mature enough to see that a problem exists with this scenario.

Anyway, back to the original request. I do not want recommendations for companies whose sole intent is to bootcamp me into a certification for SQL Server. I want to learn T-SQL as best as I can in all aspects of it. I don't care about grades, pride, certifications, but only to learn T-SQL properly. I want explanations, but what I mostly see in the books on the market are lots of examples, with almost no explanations, or the WHY.