I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and energy on it ?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.
While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.
It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.
SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.
None of them. You can learn standard (-ish) SQL for any RDBMS.
Standard SQL isn't used in the real world for paying jobs on successful projects...
Saying that, MySQL is a good place to start and here is a tutorial
All major RDBMSs support the different versions of the SQL spec to different degrees, with the older versions of the spec more fully supported. Not all take conformance equally seriously (for example, Oracle started supporting 'ansi joins' in 2001, nearly a decade after SQL-92), and as @gbn already said, in practice you need to know the flavour of SQL used by each product you use.
Postgres is an exception in that it "prides itself in standards compliance". For "learning and reference purposes", you need hands-on practice with a database, not just knowledge from a book or standard, and postgres is an ideal platform for learning the ropes with because:
Agreed with Jack and gbn, there is no standard and you need to make a choice. To make this choice, you need to select RDBMS first. I'd recommend to think about following things: 1) do you want to be DB-developer or DBA? 2) Wich operation system(s) do you want to work with? I agree, this question is little strange, but when I talk with students, they say that it is important.
fo example (just example, maybe I'm wrong) if you want to be a DBA and don't want to work with Winodws, you don't need to think about MSSQL. And if you want to be DBA and you like to work with command-string and config-files - maybe Oracle is waht you need. If you want to be developer want to use your knowledge in freelance projects, maybe you need MySQL?