As a programmer I would like to get a strong hold on writing queries. In my college years I've read a few sql books and rest I've just learned working as a programmer for last couple of years. But as those queries were work related...they weren't that 'hard' or complex.
what would you guys suggest ? Is there a good advanced sql book that would teach and then test the knowledge by giving some questions?
Thanks!
IMHO SQL skill, more than any other programming skill, requires mentoring.
There are three primary reasons.
It's quite possible (often easy) to write a SQL statement that gives you the right answer. So developers often end up telling themselves "Hey, it works, (and all in one statement,) I'm done." Usually not so, and the only efficient way to take the next step is to have your work reviewed and get suggestions (and reasons for the suggestions).
Skills aren't nearly as transferrable from "regular" programming as you might expect. Principles like decomposition, subroutines, etc. are usually blind alleys.
Real SQL skill requires just as much testing skill. You can only truly evaluate SQL execution by knowing your SQL query analysis tools inside out, and using them without fail on almost every query.
So the answer to your question is: get as much help as you can, early and often. And don't be reluctant to ask "Why".
I've found reading the Ask Tom site has helped me to understand what you can do with advanced SQL. It is Oracle specific, but bitingly sarcastic and horrific!
Also, talk to your database administrators (if you have any) they have plenty of experience you can make use of.
I find the easiest is wondering if you can find a better or easier way and the looking for that way (like on MySQL website) or asking questions (like on SO). These 2 ways have been how I have learnt the majority of SQL. But this was only after getting a base knowledge. I also found that the more complciated the database becomes, the more you learn, even if it's by trial and error.
The MySQLGame could also be a fun little detour and/or test of your skills.
I have always felt that SQL was something you had to learn by doing. I found my self on a project that forced me to write SQL not stop for several weeks to analyze every aspect of a fairly large database. In many isntances I wrote many SQLs that all resulted in the same results but went about getting them in different ways. Using Sql Server Management Studio's execution path tool I was able to review the query options and improve each and finally pick the "best" for the situation. If you can find a reason or purpose to write a bunch of SQL I beleive that will be the best catalyst you can have for truely learning the art of milking data from the DB.