The Practical SQL Handbook: Using SQL Variants (4th ed.)

Continuing the grand tradition of reviewing computer texts sent to us by publishers, Slashdot author chrisd has read the book The Practical SQL Handbook: Using SQL Variants and written up a review. If you are interested, read on ... The Practical SQL Handbook, 4th Edition, SQL Variant author Judith S. Bowman, Sandra L. Emerson, Marcy Darnovsky pages 512 publisher Addison Wesley rating 9 reviewer chrisd ISBN 0201703092 summary An indispensable introduction to SQL. This is probably one of the best books I've read for those new to SQL. It's the kind of book I wish I had many years ago when I was first learning about databases. The only problem is the SQL variants are not really for me, a developer uninterested in Oracle, MS-SQL, Sybase or Informix. That said, this is a minor part of the book and doesn't really detract from it, and I can even come up with any number of reasons why a developer would want to have that information. A comparative text would be useful by itself, but I think that trying to teach SQL and several SQL variants is too big a job for any one book.

The books introductory text on SQL is clear and concise. I also found its treatment of normalization to be as close to perfect as can be with one exception: It doesn't tell when you can go too far with normalization. In an introductory text this is acceptable, and perhaps wise considering what many new to relational databases consider acceptable database design.

And while the introductory chapter is great, the chapters on selects and joins is so clear and useful that I would even call it exciting. The terrific thing about this book is when you have finished reading it you should come away with a feel for how the underlying DB actually works and what it is doing to produce the data for you.

I personally found this book very useful, even though I am using MySQL for the application I'm writing. But the feature set that MySQL chooses to support will logically limit the usefulness of the this book for the MySQL user. Programmers developing for Postgres, Firebird, and others will obviously get much more out of the book and its treatments on subqueries and views than will MySQL users.

One thing that did turn me off is the inclusion of a CD-ROM. The CD has a copy of Sybase for the user to work with. I don't need to explain that the internet is a superior place to put such things. That said, at least it wasn't glued to the back cover (a pet peeve) and was instead bound into the book like a magazine reply card. Many publishers perceive that they can charge more for a book that has a CD, but I just find it annoying and wasteful. But that's hardly a reason not to buy this book and place it on your bookshelf in a prominent position, not on the bottom ghetto shelves next to the stack of paper for your printer.

In short, those looking for an book about SQL, that won't teach them bad habits would be well served by this book (and likely by its sister book, The Practical SQL Handbook: Using Structured Query Language by the same authors) and those who think they know SQL will find it a useful text to have handy as well.

You can purchase The Practical SQL Handbook: Using SQL Variants from bn.com. Slashdot welcomes readers' book reviews -- to submit yours, read the book review guidelines, then visit the submission page.

Comments

Is the sequel better?

[tomknight]

I note that this is the 4th edition - is this one of those times that the sequel is better than the original?

Tom.

Re:Is the sequel better?

[alapalaya]

the sequel is better than the original

or, maybe:
the SQL is better than the original

(OMG, I need an holiday....!) :)
Cheers.

Re:Is the sequel better?

[tomknight]

At least someone got it. Okay, maybe I should have added a smiley, but I really didn't think that was necessary. It seems I was wrong....

Tom.

Re:Is the sequel better?

[Deosyne]

We're supposed to be smart, not bright. ;) I guess all code and no play makes Slashy a slow boy.

Re:Is the sequel better?

[wackysootroom]

No, But the SQL is much better!

Re:Is the sequel better?

[sphealey]

Funny. But please note that it wasn't until 1992 or so that the US English pronunciation of "SQL" changed from "ess-que-ell" to "see-quill". And I have never understood why that happened.

sPh

Re:Is the sequel better?

[bhsx]

It's actually the first book, then they're releasing the 5th and 6th versions. We'll see the 1st, 2nd and 3rd in about 25 years.

Re:Is the sequel better?

[zootread]

I hear a lot of people call it sequel, but I just can't draw myself to do it. I just say the letters S-Q-L. Sure that's an extra syllable, but it just sounds better. Saying "sequel" just seems silly somehow.

Re:Is the sequel better?

[MrCreosote]

Personally, I prefer just one syllable - squeal.

Well-done book

[lseltzer]

I read an earlier edition and have a very high opinion of it. It's not an intro to database programming, but it will get you from nothing to very far into SQL.

Re:Well-done book

[randalf]

I still use the second edition, purchased in 1990. It has served me well in many SQL variants -- and has kept me away from writing non-portable code. Great book!

Re:Well-done book

[bshort404]

Indeed, I also have the second edition and its great.

Re:Well-done book

[Skweetis]

Have to agree with you there. This book, along with a pretty sad looking copy of "Programming Perl", never makes it from my desk back to my bookshelf because I refer to it regularly for the obscure bits of the syntax that I can never remember. Now that the fourth edition is out I'll have to upgrade, since edition three is in about four pieces because the binding broke on the frequently used pages.

Not much different.

[Anonymous Coward]

I have the 3rd edition and was actually flipping through the 4th edition a couple of days ago at the bookstore. I honestly didn't see any big differences at all, certainly not enough to justify buying another copy.

Re:Not much different.

[Anonymous Coward]

ISn't there a dodgy pun there somewhere?

Very controversial book

[PhysicsGenius]

I found this book very helpful in building the database I'm working on but I was surprised at the vehemence the author displayed when discussing MySQL and PostgreSQL. I use those two workhorses all the time but the author said that they were "only good for crappy websites and e-commerce wannabees". I thought he was referring to the well-known stability issues but later on he goes into great detail on how each of them has broken the SQL standard pretty badly.

It came as news to me, but the author is a SQL god so I guess it must be true.

Re:Very controversial book

[Entrope]

Accusing MySQL and PostgreSQL of "breaking" the SQL standard is a pretty weak accusation. Commercial databases have used non-standard syntax (both for features described by the standard and features that are not) and have not supported all of the standard-defined language year in and year out. Such failings are not limited to open source databases. MySQL's lack (until fairly recently) of transactions and sub-selects is a much more interesting criticism, as would be PostgreSQL's lack of clustering or replication support. Heck, even a performance critique would be more valid than saying that MySQL and PostgreSQL are not conformant with every bit and every optional feature of the SQL standard.

Re:Very controversial book

[gazbo]

Shit! PostgreSQL can't do replication? Oh no, the database we've had replicated for the last 12 months must have been an illusion, and in fact the project was never finished. Argh!

Re:Very controversial book

[Betcour]

I'm curious : where did you find stuff about PostgreSQL replication ? Never found it in the doc. Can you point some URL ?

Re:Very controversial book

[Anonymous Coward]

Uh... have you tried this?
http://www.google.com/search?hl=en&ie=UTF-8 &oe=UTF 8&q=postgresql+replication

Re:Very controversial book

[Entrope]

What third-party software did you add to PostgreSQL to make it do that? The core distribution does not support it, and there is no replication solution that is particularly blessed by the PostgreSQL core developers.

While it is possible to add layers to support replication of a database, until the replication is provided or endorsed by the main distribution, it can hardly be said to be supported by PostgreSQL itself.

Re:Very controversial book

[gazbo]

Although you can do some replication work yourself, I'm happy to concede that if you want the work done for you you need thrid party software. To say that Postgres doesn't support it is a bit misleading though.

Postgres doesn't support automated replication in the core code but there are open source plugins that will handle this. Equally, PHP does not support gzip functions as part of the core language, but should this be highlighted as a shortcoming of the language? No - just install zlib et voila!

I don't care whether the automated tools come with the core download or not - if they're freely available and work cleanly with the code (not dirty hacks) then there is no problem. Nested subqueries in MySQL is a problem as there was no (as far as I could google) patch I could apply that would enable this functionality. This is not true for pg replication.

A chapter on the shortcomings of Postgres wrt replication would be half a page long and consist of a list of URLs, saying "install one of these".

Re:Very controversial book

[jomagam]

I love how MySQL describes itself as implementing an extended subset of the SQL standard.

ps: Why was the parent post a troll ?

Re:Very controversial book

[Luyseyal]

you mean like:
http://dmoz.org/Computers/Software/Databases/Pos tg reSQL/Replication/
http://techdocs.postgresql.org /oresources.php#repl ication

None of these appear to have Master-Master replication support.

-l

Re:Very controversial book

[Anonymous Coward]

This message is a troll because "PhysicsGenius" has never read the book and is making stuff up. Look at his record, he does this all the time.

Re:Very controversial book

[Anonymous Coward]

Try this: PGReplicator -- which claims to support:

Master/Slave, Update Anywhere , Workload Partitioning (and more) data ownership models are supported.

It's written in TCL and runs outside the database, but it should be worth a look until the "proper" replication support is finished.

Re:Very controversial book

[mbogosian]

I'll grant that I have yet to read the text, but what database implementation hasn't ``broken'' SQL by providing extended APIs & semantics wherever (in)appropriate?

At least with Postgres, each SQL command's documentation has a dedicated Compatibility section indicating it's relative standards compliance (often times the functionality of a command will, in Postgres, be a superset of that defined by SQL92, so if you just stay within the bounds of SQL92, you should be fine for most nonesoteric commands). I've found Postgres's documentation to be quite refreshing in this respect. I know exactly where I'm going off the beaten path and how far before I even try to use the command in an implementation. I wish Oracle's docs were that lucid.

I'll admit, you probably wouldn't want to build and enterprise on-line retail site which has millions of transactions per month with a few instances of Postgres, but the author's comments makes me wonder how much time he's actually spent with these tools.

Re:Very controversial book

[Anonymous Coward]

What the fuck are you on about, you stupid cocksucker?

GO TO THE FUCKING POSTGRESQL WEBSITE AND LOOK UP REPLICATION. Better yet, post an "Ask Slashdot" like so many other lazy ass munching wanna-be's around here.

Fucking moron.

SQL books

[pizza_milkshake]

Has anyone ever read the SQL for Smarties series (I think there are two)? I 've heard some good things about them and am curious what your reactions are.

Re:SQL books

[jeffphil]

I've only read the first one, but it is excellent. It goes deep into Normalizing data, tells you how to optimize queries to minimize table scans, and goes in depth to how to get the most out of SQL.

It was based more on SQL-89 standard at the time and aluded to SQL-92 changes, which this is probably where the new one picks up.

Re:SQL books

[bill_guts]

I've read the scond edition and i think it's the most important technical book i've ever read. [Disclaimer: All the work i do centers around databases.] I highly recommend it, in fact, buy it now. I had just begun working with RDBMS's at the time that i read it and it improved my understanding of the technology in general. From that i also learned a lot about proprierty features based on what i learned about the SQL standard(s). I still refer to it whenever i need to.

The great thing about the book is it has common sql problems and solutions which have helped me come up with some really creative and *simple* sql statements that have saved time (my own and the server's).

Re:SQL books

[EastCoastSurfer]

Both SQL for smarties books rock. Joe Celko does a excellent job at showing why SQL is not as easy as everyone thinks. Showing the 3 different queries that get the same data, but are more or less efficient is very helpful in broadening your understanding of the language and how it works.

Keep your pet hates to yourself

[gazbo]

I'd rather it came on a CD with the book. Why? Because I don't want to spend x hours downloading shite just so that you don't see a CD in your copy. And I've got DSL - just imagine what it would be like for those on dial up.

Honestly, that is the most pathetic argument I've ever heard in a review - it would be more reasonable if you had said "they didn't provide a CD but made it available for download. This will be a major irritation for modem users, and there is no reason why they couldn't have shipped it with the book."

Personal Opinion

[Cinnibar CP]

As pretty much the local DBA-by-default among the developers here, I would say that having this manual, or an earlier edition similar to it, in the hands of the average programmer is invaluable. It gives them the basics of SQL theory across the multiple databases we work with and reduces the number of SQL-related questions I have to deal with.

For DBAs and advanced SQL programmers, however, I would recommend database-specific manuals that give greater insight than an overview text such as this, as this type of manual is unavoidably poor in the more important aspects of query optimization. Jack of all trades and master of none, as the case usually is.

Decent review, BTW (+1 INTERESTING, article moderation)

Re:Personal Opinion

[kpharmer]

I would rather see programmers develop expertise in standard SQL than in any one variant for the vast majority of cases.

Sure, query optimization is a possible downside to placing such a priority upon standardization, but then again query optimization isn't everything - nor is it necessarily best addressed through proprietary SQL extensions. Portability, maintainability, adaptability, future-proofing against changes to the optimizer in later versions - are all additional reasons to avoid proprietary extensions when standard SQL will work fine.

Excellent!!!

[baldass_newbie]

The CD has a copy of Sybase for the user to work with.

A free coaster!

Re:Excellent!!!

[jcoy42]

A free coaster!
Microwave it and convert it into a clock!

Or better yet, if you have a tesla coil you can impress all your friends!

Re:Excellent!!!

[baldass_newbie]

Sweet. Gotta get me one of them.

Re:Excellent!!!

[Anonymous Coward]

Gotta get you one of what? A friend? Good luck!

eh?

[Mr_Silver]

Continuing the grand tradition of reviewing computer texts sent to us by publishers

As opposed to doing what with them?

SQL Limitations ?

[smak]

I'm just getting into SQL myself - at least I've got perl talking to a mysql database :)

During a web-search for help with SQL, I came across a discussion, which said that SQL had many limitations (I don't have the link anymore.)

I've found SQL reasonably powerful so far, but obviously I'm new to this stuff.

Can anybody point out the areas that SQL is lacking in ? (and maybe where new progress is being made.)

Just interested.
Cheers.

Re:SQL Limitations ?

[Entrope]

SQL is particularly weak if you have definite but flexible hierarchy -- for example, say that you have relations like this between objects:
Obj1 (type A) "owns" Obj2 (type B) and Obj3 (type C)
Obj2 (type B) "owns" Obj4 (type C)
There is no particularly good way to model this relationship in SQL; you need at least four tables (one to establish ownership relations and act as object identifiers, and three to define the traits for types A, B and C) where you would ideally want only three.

Another weakness is when implementing "business logic" -- rules that define whether or not particular changes are allowed, or what else must change to keep things consistent. In the past (I believe SQL99 improves this, but is not widely supported yet), there was no standard for defining smart triggers, constraints or stored procedures, and some database systems did not support such things at all. One common solution to this problem is to have a layer of code in front of the database that performs all of the transactions and reports business logic violations to its clients -- the classic three-layer database system, but not as efficient or clean as if the business logic could be handled by the database system itself.

There are some other application-specific weaknesses; for example, a full inverse text index cannot be stored both efficiently and portably in SQL. This has impact on things like DNA sequencing as well as text searches.

On-line analytical processing is also somewhat limited within the standard; this partly goes back to the lack of a standard trigger language, and partly to the traditional table/row model of a SQL database.

SQL is a very powerful and very useful standard, and its existence as a standard has done an incredible amount of good. It does not solve every problem -- but given how complex the standard already is (and has to be), that may be a good thing.

Re:SQL Limitations ?

[Anonymous Coward]

Only a one-legged hermaphrodite hack and slash artist would put business logic in the data tier.

Re:SQL Limitations ?

[Chacham]

Obj1 (type A) "owns" Obj2 (type B) and Obj3 (type C)
Obj2 (type B) "owns" Obj4 (type C)

There is no particularly good way to model this relationship in SQL; you need at least four tables (one to establish ownership relations and act as object identifiers, and three to define the traits for types A, B and C) where you would ideally want only three.

Please explain this example. I didn't really catch it. I don't understand what you mean by "owning".

Another weakness is when implementing "business logic" -- rules that define whether or not particular changes are allowed, or what else must change to keep things consistent.

This has nothing to do with SQL. A database, is a base for data, not logic. The structure of the database defines objects, and SQL is used to logically retrieve data.

Business logic is separate from the database. It is more of a manipulation of the data before or after it is INSERTed. Which is why it is amazingly correct to use a trigger for it. Triggers should not be used to make the database work, per se, rather it should modify data, as defined by various rules.

As for not having a SQL standard on triggers, that's fine. Triggers are a deviation from the database. While it is certainly nice to have, it will broaden the application of SQL, into areas where it should not be. Triggers should be able to *use* SQL, but not *be* SQL.

One common solution to this problem is to have a layer of code in front of the database that performs all of the transactions and reports business logic violations to its clients -- the classic three-layer database system, but not as efficient or clean as if the business logic could be handled by the database system itself.

But, as I mentioned before, it is the "better" solution. We do not want the database to handle business logic. The database should be completely for data, and structured relationships. As soon as business rules are implemented, the database itself loses integrity.

The rest of your comment applies to databases, not SQL. Unless you want everything implemented in SQL, which I hope never happens.

Re:SQL Limitations ?

[reemul]

Not such a good example - you can model that with just three tables. One each for types A, B, C, and add an "owned by" column to the B and C tables. It's many-to-many relationships that need separate tables to map out how items interrelate, a simple "is owned by/child of" just needs a column.

Business logic should be separate from the database, with triggers and stored procedures used primarily for data integrity issues. (Which is why the poor-to-nonexistent support for transactions and foreign key relationships make MySQL a sad also-ran for many purposes compared to the expensive proprietary options. But I still hope...) You can get some significant performance benefits to putting some often re-used procedures into the database, but that doesn't make it a best practice for all circumstances. It's overused by both lazy front-end programmers who can't be depended upon to validate the input they are accepting and bored DBAs who are trying to look busy. And such items are some of the least portable code you can write for different database systems, whereas table creation and select/insert/update commands work pretty much anywhere. Doesn't mean SQL is perfect, but if your problem comes from trying to get it to do things that properly should be done somewhere else, the failure is in the design, not SQL.

Re:SQL Limitations ?

[Entrope]

In my first example, take "owning" to be whatever you mean: the hierarchical structure of a document, organization of a company or device, and so forth. Many real-world relationships have the required trait (having the same relation between distinct tuples of types). A related case is when the hierarchy is so rigid that a general SELECT from the whole table is less efficient than the search needs to be: for example, an IRC chat channel has many bans. Bans cannot be moved between channels, and there are many more channels than there are bans per channel, so it is inefficient to do "SELECT * FROM bans WHERE channel_id=(whatever)". But the original poster asked for limitations of SQL. For what it tries to do, as I said, it does a very good job. And as you implied, making it do everything would be an awkward thing to try. My entire comment was intended to address the limitations of the language and not whether SQL could somehow be "more SQLish."

Re:SQL Limitations ?

[rocjoe71]

The only thing limiting SQL is competent implementers/programmers. SQL is an excellent means of selecting or creating sets of data, it's based on algebraic theorems (remember your Venn diagrams from highschool? SQL begins right there), some of which have been around for about a century. What SQL is incapable of doing is overcoming shortsighted bottom-up database modelling. In other words, it doesn't matter how good the engine is, if the car's got square wheels, it ain't going anywhere.

Re:SQL Limitations ?

[Entrope]

Contrary to your assumption, some database have more than one front-end. For such databases, good design dictates that business logic is kept in one place, and does not have to be replicated to every front-end or batch processing script for the back end. As I said in my original post, that could be in a layer sitting in front of a SQL database, or it could be enforced by the database itself. If it is well-integrated with the database, it allows for additional optimizations.

But I digress: The original poster asked for limitations of SQL. I pointed out several limitations. Your reply is essentially "But your assumptions are wrong if you are using SQL!" -- and that is entirely the point. Using SQL as the only interface to the database prevents the user from making certain reasonable assumptions.

Re:SQL Limitations ?

[Entrope]

In the "owned by" column, how do you know what the Table C "owned by" column refers to? It may be an A, it may be a B. Enforcing consistency (cascading deletes or updates of the owner) must also be done by business logic instead of using the normal SQL support for such things.

Re:SQL Limitations ?

[Chacham]

for example, an IRC chat channel has many bans. Bans cannot be moved between channels, and there are many more channels than there are bans per channel, so it is inefficient to do "SELECT * FROM bans WHERE channel_id=(whatever)".

No it isn't. With an index on channel_id, it would be the quickest method.

With Oracle, an EXPLAIN PLAN, will easily show the optimizer chosing the index for a FAST FULL SCAN. You can't get too much quicker than that.

Re:SQL Limitations ?

[Anonymous Coward]

Oh, I would love to be able to mod this up.

Re:SQL Limitations ?

[deltavivis]

now if only all the object database folks weren't going bankrupt, or being mismanaged into the ground...

Just about any example you can think of can probably be solved with an rdbms (and maybe some code interacting with it), it just might be really slow and overly complex because you're trying to smash your "real world" model into tables. Slow because the optimizer is most likely pretty stupid and can't make a good plan that has more than a handful of joins (which you'll end up having with a complex design) and it can't do a damned thing about outside code that fiddles with SQL results.

Re:SQL Limitations ?

[Saint Stephen]

Yes, there is a very good model for representing hierarchies in SQL. It's called a parts explosion table, and you can read about it in a book called "SQL For Smarties" by Joe Celko. This lets you answer queries like "what objects does this object and all its subobjects contain" and any sort of query like that.

Your database has to require triggers if the data isn't slowly changing though.

Re:SQL Limitations ?

[Anonymous Coward]

And I would like to mod it way down. What he's saying is - SQL is the ANSWER. If it doesn't work well for you - your not asking the right question.

Re:SQL Limitations ?

[Anonymous Coward]

There are some other application-specific weaknesses; for example, a full inverse text index cannot be stored both efficiently and portably in SQL. This has impact on things like DNA sequencing as well as text searches.

A database is probably not the best solution for DNA. A Generalized Suffix Tree is better suited for most searches of DNA strings. Check out Google under Generalized Suffix Tree and Bioinformatics.

Re:SQL Limitations ?

[Tablizer]

(* In my first example, take "owning" to be whatever you mean: the hierarchical structure of a document, organization of a company or device, and so forth. *)

I think the concept of "owning" is anti-relational in philosophy. "Own" is only one possible viewpoint (relationship) among *many*. One should not hard-wire such absolutisms into software designs. (OO often does this with overbearing IS-A relationships.)

(* Many real-world relationships have the required trait *)

Example? IMO, trees are over-used in computer-science. They conceptually are (initially) pleasent to work with, but fail to capture the multi-facetted nature of most real things (besides animals and shapes) well over time. You end up getting deeper and deeper into your speggetti vine. If you follow any man-made change pattern over time, it will almost certianly digress from a true tree. (Technically, you can force just about anything into a tree, however, it becomes artifical over time, often having to duplicate nodes or at least a vast majority of a node.)

Even in a "hierarchical document", you may want to search for things in a non-hierarchical manner. The "hierarchy" is just one viewpoint among many. (Plus, some complex documents cannot be easily reduced hierarchically.)

True, some niche domains can take advantage of its limited pathways. CAD may be one.

Re:SQL Limitations ?

[42.5]

Exactly. Poor design is not an excuse to blame SQL. SQL is math. Can math solve bad design?

Re:SQL Limitations ?

[Slime-dogg]

The real area where SQL is weak is recursion. There is no allowance for recursion in '92 and there is little I believe in '99.

An excellent language to examine is Datalog, which uses rules to do queries. The structure of the Datalog language, as well as it's queries, falls very close to the definition of a finite state machine.

You can show the flow of data, and how it fits within the bounds of the rules you give it, all by drawing the states that you define within the language. The query ends when it reaches a point where it can no longer process any data.

SQL is great for database work, but I honestly believe that the future lies in a rules-based language like Datalog.

Re:SQL Limitations ?

[Anonymous Coward]

Which is why the poor-to-nonexistent support for transactions and foreign key relationships make MySQL a sad also-ran for many purposes compared to the expensive proprietary options. But I still hope...)

FYI, PostgreSQL supports transactions and foreign keys very well, as well as many other "advanced" RDBMS features. The entire foreign key spec is supported, with the exception of MATCH PARTIAL. If you're looking for a free, open-source alternative to the expensive proprietary database systems, PostgreSQL is definately something to consider.

Re:SQL Limitations ?

[alienmole]

SQL is excellent as far as it goes, but it doesn't provide a complete solution to all data processing requirements. (If you've ever written stored procedures that are just hundreds of lines long, you have an idea of what I mean).

Which means that for complex processing, there has to be an interface at some level to a more flexible programming language. The dominant programming model in business today is object-orientation - not without some good reasons - but unfortunately, there are some problematic issues in mapping between object and relational systems.

So the problem with relational systems is really simply that (a) in themselves, they are not appropriate for all data transformations and (b) they don't easily lend themselves towards integration with more flexible systems.

That said, I agree the most developers don't seem to be aware of what SQL can do. Having personally developed systems that included tens of thousands of lines of SQL, I'm pretty familiar with what it can do, but also with how restrictive and inflexible it is compared to some other approaches. One of its biggest problems is that it lacks a powerful enough reuse mechanism, and the various proprietary extensions don't do a very good job of correcting this.

Re:SQL Limitations ?

[mbogosian]

Business logic should be separate from the database, with triggers and stored procedures used primarily for data integrity issues.

Okay, this is wildly off-topic, but before we start a design war here, perhaps this could use some clarification/extension.... It is widely agreed that, for maintenance purposes, business logic should be implemented in as few disparate systems as conveniently possible. It might, in fact (at least with PL/SQL, triggers and other constraints), be entirely possible to implement the business logic of an application in the database itself. However, as many of us know, stored procedures may be fine (if not sometimes a little performance poor), until you have to change them.

It is pretty much generally considered a pain in the ass to have to change certain aspects of business logic when implemented at the database layer. This being the case, if you move some functionality to a higher level, you should either move the rest of it or make damn well sure you have the best development documentation in the history of commercial software development. I've lost count of the number of inexplicable erroneous behaviors I've come across which were the result of duplicate/forgotten functionality in the database that needed to be altered for one release or another.

A database is for data storage and retrieval. Features which help ensure data integrity are welcome and necessary. Features which try and convince me of the values of executing Java code inside the database are highly suspect IMO.

Glad they emphasis SQL-92

[pmancini]

SQL-92 has much better syntax than SQL-89. I just wish more of it was implemented. MS SQL-Server actually does a better job of it than even Oracle. Compare

Select A.*
From A,B
Where A.MayorName is not null
and A.CityID = B.CityID
and B.TaxRate > 5

vs.

Select A.*
From A JOIN B
ON (A.CityID = B.CityID)
Where A.MayorName is not null
and B.TaxRate > 5

The major difference is that the join is explicityly removed from the filtering done in the where cluase. This makes queries much easier to read. Queries can get extreamly complex and when you have something like 6 joins you will soon appreciate the new syntax.

This book sounds interesting so I will be checking it out!

--Peter

Re:Glad they emphasis SQL-92

[eric2hill]

"MS SQL-Server actually does a better job of it than even Oracle."

Oracle 9i now supports the SQL-92 syntax including natural joins. If you want to join two or more tables that have properly named keys (like key names for PK/FK relationships), then you can use the following:

select *
from A
natural join B
where A.MayorName is not null
and B.TaxRate > 5

It's a very nice way to join a lot of properly normalized tables with little to no WHERE clauses. SQL Server can probably do the same thing as well.

Re:Glad they emphasis SQL-92

[irix]

This makes queries much easier to read

Really? As a programmer who has used Oracle for years, I find the SQL-92 style join syntax more confusing. I spend a lot of time getting it correct when writing SQL for RDBMS who use that join style.

I suppose maybe it is just what you are used to.

Re:Glad they emphasis SQL-92

[roccothegreat]

SQL-92 has much better syntax than SQL-89. I
just wish more of it was implemented. MS SQL-
Server actually does a better job of it than
even Oracle. Compare

Select A.*
From A,B
Where A.MayorName is not null
and A.CityID = B.CityID
and B.TaxRate > 5

vs.

Select A.*
From A JOIN B
ON (A.CityID = B.CityID)
Where A.MayorName is not null
and B.TaxRate > 5

The major difference is that the join is explicityly removed from the filtering done
in the where cluase. This makes queries much easier to read. Queries can get extreamly
complex and when you have something like 6 joins you will soon appreciate the new syntax.

This book sounds interesting so I will be
checking it out!
--Peter

This Seems To work Better for me!

Select A.Moron, A.Geek
From A JOIN B
ON (A.Geek = B.Nerd)
Where A.Geek is not null
and B.Nerd > 12

Yes, I do like this way so much better!

Re:Glad they emphasis SQL-92

[Anonymous Coward]

I find the "new" syntax incredibly difficult to read. I hate the "ON" paradigm, expecially when you are using a drag and drop query designer that generates them for you.

Granted my experience is with Access and its use of "ON" and Oracle.

The one thing that ins't easy (impossible in access) is to make an outer join using a constant against one of the columns, or to put an NVL in the necessary side of the outer join.

The way I format my SQL, and putting all my join criteria at the top makes my SQL statment a lot easier to read, and figure out, than trying to figure out

FROM
table_a LEFT JOIN
(table_b LEFT JOIN table_c
ON table_b.key = table_c.key)
ON table_a.key = table_b.key
WHERE table_a.criteria = search_criteria

It could be just me, but I think the following is much easier to read, and allows greater flexibility:

FROM table_a,
table_b,
table_c
WHERE table_a.key = table_b.key (+)
AND table_b.key = table_c.key (+)
AND table_a.criteria = search_criteria

Re:Glad they emphasis SQL-92

[mal3]

Nope, no Natural Join in MS SQL Server 2k. Wish it were though.

Re:Glad they emphasis SQL-92

[DRO0]

It's not just an issue w/ readability as far as OUTER JOINS are concerned. After banging my head several times against my desk to solve one hell of a bug, I'm now 100% in the habit of using INNER JOIN and OUTER JOIN clauses rather than = and *=.

From SQL Server Books Online

"In earlier versions of Microsoft® SQL Server(TM) 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax."

If I changed your query to an outer join,

Select A.*
From A,B
Where A.MayorName is not null
and A.CityID *= B.CityID
and B.TaxRate > 5

This could be interpreted as

"Give me all rows from A and B where the CityID's match and TaxRate > 5. and MayorName is not Null"

OR

"Give me all rows from A where MayorName is not null and only join rows from B where the CityID's match and the TaxRate > 5".

But if you write it as

Select A.*
From A
LEFT OUTER JOIN B ON
and A.CityID = B.CityID
and B.TaxRate > 5
Where A.MayorName is not null

OR

Select A.*
From A
LEFT OUTER JOIN B ON
and A.CityID = B.CityID
Where A.MayorName is not null
and B.TaxRate > 5

...you avoid this ambiguity.

Re:Glad they emphasis SQL-92

[d3xt3r]

but in oracle a muti-column inner select is much better:

select a,b,c,d,e
from foo, bar
where foo.a = bar.a
and (foo.a, foo.b) in (select a, b from zee)

You just can do it in SQL Server without doing some ugly casting like:

select a,b,c,d,e
from foo, bar
where foo.a = bar.a
and (foo.a + '|' + foo.b) in (select a + '|' + b from zee)

Which really hurts query performance

BTW: SQL Server isn't half the dbms that Oracle is

Re:Glad they emphasis SQL-92

[DRO0]

Damn, that's what I get for copy/paste. Let me try again. There shouldn't be an "and" after the LEFT OUTER JOIN ON clause. Time to wake up. :)

Re:Glad they emphasis SQL-92

[FearUncertaintyDoubt]

As far as readability, I think it's a matter of preference (i.e., what you were "raised" on). FWIW, I like the JOIN because it separates your joins from your filtering.

The one big reason to use SQL-92 (why we do it at my job) is because it effectively prevents an accidental cartesian query:
SELECT * FROM table1, table2

if table1 and table2 each have 1000 rows, you just selected 1,000,000 rows. 10,000 rows each, that's 100,000,000, and you get the point. You can easily crush a server by making a typo.

While the above query is a trivial example, easy to overlook that you didn't join properly, especially when you start complicating it with > 2 table joins with lots of WHERE critieria. If you always use explicit SQL-92 JOINS, your SQL won't run if you don't join properly.

Re:Glad they emphasis SQL-92

[Caractacus Potts]

I'm actually a fan of the JOIN clause. You might think that it's easier to read when you're writing it, but things change when you have to deal with more complex queries or decipher a long unformatted one that you or someone else wrote long ago.

I'm currently coding up a SQL "pretty printer", and moving the joining criteria from the FROM clause where it makes sense to the WHERE clause where it's just convenient does not look pretty.

Re:Glad they emphasis SQL-92

[PureCreditor]

I'm new to SQL. What's the correct way to present the above syntax without accidentally taking the cartesian product of such? Thanks.

Re:Glad they emphasis SQL-92

[cruachan]

I agree with many here. SQL-92 JOINS look good from a theoretical computer science type viewpoint, and indeed for joins on two or three tables they are pretty.

Trouble is when you have a complex database with a load of table decodes and your doing a complex set of joins on a dozen tables then deciphering a query written 6 months ago with SQL-92 syntax is hell. In effect SQL-92 just doesn't scale anywhere near as well as SQL-89.

Re:Glad they emphasis SQL-92

[Anonymous Coward]

As Triumph would say:

Your little joke is very good...

For me to POOP ON!

Re:Glad they emphasis SQL-92

[FearUncertaintyDoubt]

SQL-89:
SELECT * FROM table1, table2
WHERE table1.id = table2.id

SQL-92:
SELECT * FROM table1 JOIN table2
ON table1.id = table2.id

Re:Glad they emphasis SQL-92

[jabbo]

You know, that may well be, but I always thought that the "where a.id = b.id" was pretty logical and explicit. I still can't quite bring myself to use the new syntax except for outer joins (where, at least in Postgresql, it's the only way I know of to do it).

I have worked on and developed on MySQL, Oracle, Sybase, PostgreSQL, and a tiny bit of MS-SQL (7.0) so I have a few bad habits stemming from my Oracle days. I do like Postgres best of all, though.

Re:Glad they emphasis SQL-92

[wrt2]

or:

select a,b,c,d,e
from foo join (bar join (select a, b from zee) bat
on (bar.a = bat.a and bar.b = bat.b) )
on (foo.a = bar.a)

unless I'm missing something...

Re:Glad they emphasis SQL-92

[Fulcrum of Evil]

Did they fix the bug where circular joins crash the DBc connection?

SELECT A.id FROM A, B, C
WHERE A.id = B.id AND B.id = C.id AND C.id = A.id;

Re:Glad they emphasis SQL-92

[eric2hill]

Works dandy all the way back to Oracle 7.3.4...

Re:Glad they emphasis SQL-92

[deputydink]

even better ... in sql92 you can use an explicit left outer join and remove that extra where clause.

Select A.*
From A LEFT OUTER JOIN B
ON (A.CityID = B.CityID)
Where
B.TaxRate > 5

Re:Glad they emphasis SQL-92

[Pfhreakaz0id]

As a guy who learned SQL on MS-SQL (the -92 variant), going to Oracle now, this has been my biggest hurdle. I have to mentally translate it back. I don't see how anyone could argue it is clearer to separate the joins from the filtering. In practice, I think most people puts joins first, then filters, but I've read some stuff that it might not always be the most efficient query.

I understand 9i now supports JOIN, but we're still running 8. SIGH.

Re:Glad they emphasis SQL-92

[Anonymous Coward]

you misspelled 'extremely'.

hope this helps.

Re:Glad they emphasis SQL-92

[Anonymous Coward]

You would simply use nested queries in SQL Server to handle this, as another post already pointed out. Works great, and the query engine seems to have no trouble optimizing them.

Most of the people I've met who bad mouth SQL Server have not actually tried to use it for anything real. Perhaps they see that all of the configuration and tuning and complexity that they are accustomed to appears to be missing and therefore conclude that the product is not powerful.

And indeed SQL Server may not be as powerful as Oracle. Oracle is becoming increasingly integrated with many things that aren't database (the application server, for example). If you have the desire and the cash to buy into Oracle's expansive, powerful, and complex application architecture, then 9i is the database for you. But for the majority of database projects, it is overkill.

With today's hardware, unless you have a BIG database, you can use any crappy database and get decent performance. (Unless you are stupid about data modeling, in which case you are also too stupid to use Oracle.)

Given that peformance and everything-but-the-kitchen-sink features will probably not be the problem, I want the database is easy to work with, both for developers and dbas. When you consider how much staff time costs, SQL easily pays for its comparatively modest price.

So while I wouldn't run multi-terabyte databases on SQL Server, it is great for small to mid-size projects where you need a full-featured database and don't have much time to spend on it.

You can design your database using the built-in tools (unsophisticated, but handy if you have nothing else), run the maintenance wizard to set up backups and housecleaning according to your needs, and then check your email periodically to make sure it is still alive.

In our shop we have one oracle 8i server, and half a dozen SQL Servers. None of our databases are large. Our DBA spends the vast majority of his time fussing with Oracle. The SQL servers just sit there and work.

(Except for the one running version 6.5! Can't wait to upgrade the damn thing. SQL Server got a major rewrite with 7.0. Before that, much of it was crappy hacked up Sybase code. The first version of SQL Server shared its codebase with Sybase. )

Re:Glad they emphasis SQL-92

[Anonymous Coward]

that may be true that youre oracle dba is fussin alot.

but I have administrated both products and for samller databases (1 G) you realy dont have to fuss very much with oracle either.

Re:Glad they emphasis SQL-92

[CySurflex]

The difference is actually beyond a pretty syntax, when you're dealing with an outer join. The two join syntaxes function in a subtly different way.

The SQL-89 outer join syntax using *= performs the rest of the WHERE clause after the join, whereas the SQL-92 version, using the LEFT OUTER JOIN syntax performs the WHERE clause before the join. This can result in a different result set in certain cases - and if you know what you're doing, the difference can be used for your benefit.

SQL Security

[mrkitty]

www.cgisecurity.com/lib Has some good papers on sql security.

Re:my question

[typedef]

Structured Query Language

Another good book

[Giant Robot]

The only deadtree book I've read on SQL is:

A First Course in Database Systems (2nd Edition)
- Jeffrey D. Ullman, Jennifer D. Widom

I found that it covers almost everything I needed, with a no-nonsense approach (no "CheckPoints", long pointless blurbs, or long code listings).

Although written for the academic, it didn't stop me from reading mostly the second half of the book first (the SQL stuff), and reading some theory when I wanted to.

The SQL it covers is pretty standard stuff that works with most databases (except for MySQL at the time I read it, some ACID principles couldn't apply). The specific details for each databases can be picked up by reading online docs.

If you visit SE-asia, check out their bookstores where you can find tons of "mainland china" editions of these classics that cost a tenth of the price as the real deal.

Re:Another good book

[Anonymous Coward]

If you visit SE-asia, check out their bookstores where you can find tons of "mainland china" editions of these classics that cost a tenth of the price as the real deal.

Thanks for the tip, I'll pick one up on my next trip to Los Angeles or Garden Grove.

Looks sweet

[WellHungYungWun]

Thanks for an excellent review, I was looking for a beginner to expert book to compliment my "from the ground up" sql book, and this may have a place on my shelf.

MySQL again

[fm6]

I don't want to start the "is MySQL a real RDBMS" debate again. Well, maybe. Anyway, it seems a little strange for a discussion of advanced SQL to center around MySQL. The only serious defense I've heard for MySQL is that it handles very simple queries more quickly than other engines. If you're a serious doing a database app that requires you to think about normalization, you probably need a database that's smart enough to optimize a complicated query.

Re:my question

[JohnHegarty]

SQL
/S Q L/ An industry-standard language for creating, updating and, querying relational database management systems.

SQL was developed by IBM in the 1970s for use in System R. It is the de facto standard as well as being an ISO and ANSI standard. It is often embedded in general purpose programming languages.

The first SQL standard, in 1986, provided basic language constructs for defining and manipulating tables of data; a revision in 1989 added language extensions for referential integrity and generalised integrity constraints. Another revision in 1992 provided facilities for schema manipulation and data administration, as well as substantial enhancements for data definition and data manipulation.

Development is currently underway to enhance SQL into a computationally complete language for the definition and management of persistent, complex objects. This includes: generalisation and specialisation hierarchies, multiple inheritance, user defined data types, triggers and assertions, support for knowledge based systems, recursive query expressions, and additional data administration tools. It also includes the specification of abstract data types (ADTs), object identifiers, methods, inheritance, polymorphism, encapsulation, and all of the other facilities normally associated with object data management.

The emerging SQL3 standard is expected to be complete in 1998.

According to Allen G. Taylor, SQL does _not_ stand for "Structured Query Language". That, like "SEQUEL" (and its pronunciation /see'kw*l/), was just another unofficial name for a precursor of SQL. However, the IBM SQL Reference manual for DB2 and Craig Mullins's "DB2 Developer's Guide" say SQL _does_ stand for "Structured Query Language".

That's because

[brokeninside]

SQL, being an acronym, is properly pronounced ess cue el. Mispronouning SQL as sequel shows the lack of pedantry needed to be a good computer scientist.

Re:That's because

[Anonymous Coward]

No, there are plenty of computer scientists who are able to stop being anal enough/pedantic to joke. On second thoughts though.....

Re:That's because

[Chacham]

It is not a mispronunciation.

Do you call DOS Dee-oh-es?
Do you call CMOS, or cee-em-oh-es?
How about arr-ay-eye-dee?

There are many others. All are fine, however. Acronyms, in the techical world, are used so the user does not have to say a bunch of words. Wouldn't it then be silly to have to spell each letter out?

If the use of an acronym is for ease of use, its pronunciation should follow suit.

Some acronyms are easier spelled than pronounces. Exampli gratia, PC-MCIA*. Also, there those that are easier pronounced than spelled. For example, RAID. There are even those that are better as a mixture. Such as MS-DOS.

But some, stand on both sides of the line. They are both easy to spell, and to pronounce. Both are easy, and people are free to use whichever one they are more comfortable with. I think the most common one of those is SQL.

PostgreSQL, is spelled, post-gress-que-ell, because post-gress-sequel, is harder to pronounce, and doubles the "s".

What really ticks me off, is people who say dah-tuh(-base), as opposed to day-tuh(-base). IMNSHO, "Dah" is incorrect, and is a clear sign of a loser.

* People Can't Memorize Complex Industry Acronyms

Re:That's because

[dillon_rinker]

There's a big difference between SQL and DOS or RAID...VOWELS!

Re:That's because

[sphealey]

In fact, until about 1992 or so "SQL" was pronounced "ess-que-ell" in US English. About the time Microsoft "SequelServer" was released it suddenly became "Seek-kwell". Don't know why, but it does tend to grate on those who used it before change.

sPh

Re:That's because

[Chacham]

Good point. I didn't think of that. Let me try again.

Do you call it scuzzy or es-cee-es-eye?
Do you say idd, or eye-dee?

The former has no vowels in the first syllable, yet it is still pronounced as a word.

The latter starts with a vowel, though it is spelled out. Granted it is not a real acronym, but I think it makes the point.

Re:That's because

[edhall]

SQL != SEQUEL

Although SQL is largely derived from it, SEQUEL was the query language of IBM's first Relational Data Base Management System, System/R, dating back to the mid-1970's. (IBM's second --and current -- RDBMS was creatively named DB2.) So pedantic old farts like me are careful to distinguish between the two and pronounce SQL as ess kyoo ell to avoid confusing it with its more primitive predecessor, SEQUEL (though it's not like there is any real chance of confusion these days).

-Ed

Re:That's because

[Anonymous Coward]

What really ticks me off, is people who say dah-tuh(-base), as opposed to day-tuh(-base). IMNSHO, "Dah" is incorrect, and is a clear sign of a loser.

Would you pronounce the Latin word "Datum" as Day-tum? Were the Romans all "losers"? Language is different in different places, and it also changes. SQL used to be pronounced as three separate letters, now it's mostly changed. It's not such a big deal - get over yourself.

Re:That's because

[adamy]

NONONONO

It is not Scuzzy...it is

SEXY

(remeber that one?)

Re:That's because

[jafuser]

Some acronyms are easier spelled than pronounces

According to many dictionaries, it's not really considered strictly an acronym unless it is easily pronouncable; otherwise, it's just an abbreviation. Personally, if it's an abbreviation where the letters are the first letters of most of what it's abbreviating, I call it an acronym whether it's pronouncable or not.

As far as what to call "SQL", I think most of us are intelligent enough to know what someone means if they say either "ess cue ell" or "sequel".

Re:That's because

[Anonymous Coward]

Some acronyms are created with a pronunciation in mind (RAID). Some are just letters put together (PCMCIA). It's not Mr(s). Shops4me Dot Com who's supposed to make the pronunciation rules, it's the people who make up the acronym. SQL was intended by IBM, its creators, to be pronounced "ess-cue-ell" to differentiate it from their earlier effort SEQUEL.

Also, "database" is pronounced differently in different locales. It's a word, not an acronym. Go look it up. Webster's lists both pronunciations as acceptable.

By the way, you use far too many commas to be correcting others' language. You should learn what commas are for and where they're appropriate, because you don't come off as intelligent as you appear to think you are.

But I think the most telling mistake you make is misspelling "exempli gratia." There's nothing funnier than some arrogant asshole misspelling the latin they use to appear intelligent.

Re:That's because

[Chacham]

By the way, you use far too many commas to be correcting others' language.

I believe you mean "comments". I was not correcting their language. As opposed to this comment, in which I am correcting your language.

You should learn what commas are for and where they're appropriate,

I know what they are for. Regarless of that, I use them when I think a pause is needed to understand the statement.

because you don't come off as intelligent as you appear to think you are.

I never intended to come off as intelligent. Maybe its just a by-product. :-)

I do mean to come off sounding sure of myself, because I am.

But I think the most telling mistake you make is misspelling "exempli gratia."

Good catch! When I re-read the comment before posting it, I didn't see that. The only mistake I made was a typo.

There's nothing funnier than some misspelling the latin they use to appear intelligent.

Well, I used it because I used to get mixed up in the way EG and IE were used. When I learnt what they meant, I found it "fun" to spell them out.

I must say, that your comment is rather arrogant. "Mister...." You ought to calm down before posting comments.

Also, people do not have to pronounce words in any canonized form. They pronounce words merely to get the point across. I correct (some) others when they use an adjective instead of an adverb. Some people like it, other's just repeat the incorrect word. They are trying to make a point that people speak to get the point across, not to speak correctly. Though it still bothers me to hear the incorrect usage.

Re:That's because

[wdr1]

SELECT * FROM accurate WHERE SQL != SEQUEL
No rows returned.

Actually, what most everyone knows as SQL and Sequel are the same thing, at least in the same sense that SQL-86, SQL-92, etc. are the same thing. (I.e. there are obvious enhancements, etc. to the language as time passed and it evolved, but the language itself is still what we think of as "sql".)

IBM changed the name from SEQUEL to SQL in the late 70's as SEQUEL was found to be an existing trademark.

-Bill

Re:That's because

[plugger]

Unless you are watching 60's StarTrek, then the correct pronunciation is dah-ta-bays'

Re:That's because

[alienmole]

The shift began before that - I remember first hearing it in the late '80s, and being incredulous and vowing never to use it. (I use it now without a second thought - there go my principles.)

But I think you're right about the Microsoft connection - the original Microsoft/Sybase SQL Server version was released for OS/2 in 1988. So Microsoft actually succeeded in changing the global pronunciation of an acronym - now I *really* feel had!!

Re:That's because

[PacoTaco]

Here's the real question. Why is SCSI pronounced "scuzzy" while HSSI is pronounced "hizzy"? Both have three consonants and end with an i. Why not "scizzy" and "huzzy" instead?

I guess it's probably because "fetch me that huzzy cable" sounds pretty stupid. :)

Re:That's because

[edhall]

Like my post said, SQL is largely derived from SEQUEL, and the latter is the "more primitive predecessor" of the former; that's essentially the relationship you describe. And you're right about the reason for the name change (though the odd thing is that the conflict was with the name of an airplane -- IBM's lawyers must have been paranoid in those days). But we were discussing the name, and the pronunciation of the name. Check out the following document on the history of SQL:

• The 1995 SQL Reunion: People, Projects, and Politics

You'll note that the originators of SEQUEL/SQL are very careful to use one term or the other depending upon which point in time they're discussing. That the name change corresponded to some pretty significant additions to the language is probably why in circles outside of the creator's group SEQUEL and SQL are often treated as separate but related entities. But whether or not they are the same language misses the point: they are two different names, and as the above article shows, SQL was named in the style of other three-letter-languages of the era like APL (by "squeezing the vowels out of SEQUEL"), and was pronounced accordingly. This is why old farts (especially IBMers and ex-IBMers) are quick to correct whippersnappers who pronounce it SEE kwell.

-Ed

Re:That's because

[Anonymous Coward]

No, I meant "commas." You shouldn't be sure of yourself, because you're wrong.

I use them when I think a pause is needed to understand the statement.

Oh, like this? "PostgreSQL, is spelled, post-gress-que-ell, because post-gress-sequel, is harder to pronounce, and doubles the 's'."

Putting 4 commas in a sentence that should contain 1 at most doesn't clarify the sentence, it just makes you sound like an idiot. If you can't understand your own writing, inserting random commas won't help you. Instead, go read a basic grammar book. You might want to look up "plurals vs. possessives" while you're in there. Here's a little exercise to help you out. (It's "others just repeat," not "other's just repeat.")

For someone so bothered when others mispronounce things (even when you're wrong about it like "database,") you don't have a very good grasp of the language yourself. And don't worry, there was no danger of you sounding intelligent, accidentally or otherwise. Yeah, I'm pedantic about English, but I also know how to use it.

By the way, it's much easier to "get the point across" when you use the language correctly. I don't see how you couldn't understand that.

Re:That's because

[Chacham]

You shouldn't be sure of yourself, because you're wrong.

I believe I am correct. And since, even if I am wrong, I believe I am correct, I can easily be content in feeling sure about myself.

Putting 4 commas in a sentence that should contain 1 at most doesn't clarify the sentence, it just makes you sound like an idiot.

Not to most people. I would say, that the vast majority of people do not know how to speak English correctly. And even from those, most don't care to.

Many times, I write in a way that resembles my speaking habits. So, if I would pause in a given spot, such as here, here, or here, or even here, I put in a comma. The "official" use of a comma is not so, but I don't care.

(It's "others just repeat," not "other's just repeat.")

You are correct. I didn't proofread it very well.

For someone so bothered when others mispronounce things (even when you're wrong about it like "database,") you don't have a very good grasp of the language yourself.

I have an excellent grasp of the language. Probably better than you. I think it comes with maturity.

Also, my being bothered by something, has no relation to my speaking habits. I have certain likes and dislikes.

Yeah, I'm pedantic about English, but I also know how to use it.

No, you're pedantic period. You are only using English because it was convenient.

By the way, it's much easier to "get the point across" when you use the language correctly.

That is amazingly incorrect. Only a fool would think that.

Considering that most people don't understand the difference between an adverb and an adjective, and may even try to correct you when you speak correctly, it is a wild claim to say that speaking correctly is better.

The purpose of language is to communicate a feeling verbally. It has also become the choice for the written expression, though not as dominant. Communicating correctly, is not always the same as communicating efficiently.

I don't see how you couldn't understand that.

Don't worry, in a few years you may actually mature, and it will all become clear.

Anyway, thanx, I needed a good laugh.

Re:That's because

[tgrigsby]

Ed, get a life. I call it "sequel" every chance I get and I teach my customers to do the same because it's easier for them to remember. As for SEQUEL, the language, who the heck still uses, much less *remembers* that fossil besides old pedantic farts... what a minute...

Re:That's because

[Anonymous Coward]

You do that, sweetie.

CDs everywhere

[fm6]

The CD has a copy of Sybase for the user to work with. I don't need to explain that the internet is a superior place to put such things. ... Many publishers perceive that they can charge more for a book that has a CD, but I just find it annoying and wasteful.

Well perhaps. Certainly most "FREE BONUS!" CDs are a total waste. But having a large DBMS server on a CD can be a real boon for those with slow internet connection. You'll not that many sites that provide software for free download also offer the same software on CD for a nominal fee.

Also, increasing the "perceived value" of the book is porbably only part of the story. Undoubtedly AW got some kind of consideration from Sybase for advertising their product this way.

Re:CDs everywhere

[Anonymous Coward]

One reason the dbms on the CD is Sybase is the authors are employees of Sybase. Or at least they were when I read an earlier edition.

A good reason for providing a dbms on the CD, even a toy one, is to give the reader something to practice with. This is bigger issue for Windoze users. I read the first edition before a CD was provided, and it was difficult setting up a practice environment, even at work.

It has been more than 10 years since I read the first edition, but because of it, I still feel comfortable with SQL. This is the book I have always recommended.

Compare to /Visual Introduction to SQL/ ?

[sphealey]

How does this compare to A Visual Introduction to SQL? The first edition of that book is the best into to SQL and (indirectly) relational databases I have seen. The first edition didn't cover outer joins, though, and before I buy the second edition I was wondering how the reviewed book compares.

sPh

This is SQL-99, not SQL-92, and it's in Oracle 9i.

[emil]

According to Daniel K. Benjamin's "Oracle 9i New Features For Administrators Exam Guide," Oralce 9i introduces:

1. The JOIN keyword with its variants
2. CASE statements (a subset of which were supported in 8.1.6)
3. NULLIF
4. COALESCE
5. MERGE
6. Various analytic functions

Oracle has a lot of problems, but standards conformance is not one of them. Oracle is one of the few databases to have certified with NIST for SQL-92.

my suggestion

[rnd()]

If you want to learn SQL, I suggest the following:

Get a copy of whatever database you're going to use. Microsoft SQL Server is actually really easy to configure and use and has some great graphical tools. If anyone knows of some similar graphical tools for an OSS database, please let me know.

Next, look at a database that someone else has written and attempt to manipulate the data through queries.

The best book I've actually purchased for SQL is "Transact-SQL Programming" by O'Reilly. If you are working on a Microsoft database, this book is a great companion to the built-in help system in Query Analyzer.

Re:my suggestion

[wizardguy]

A great GUI tool which works with Oracle/MS/IBM/Syabse is the dbartisan tool from Embarcadero (remove the space before x.asp) http://www.embarcadero.com/products/dbartisan/inde x.asp

Re:my suggestion

[rodgerd]

TORA is open and supports MySQL and PostgreSQL as well as Oracle.

Titles? Editions? Sequels?

[fm6]

its sister book, The Practical SQL Handbook: Using Structured Query Language by the same authors.

Uhm. Despite the change in the title, I believe this is an earlier edition of the same book. No idea why it's still in print -- perhaps some people prefer to focus on SQL-89 and forget about SQL-92. Anyway, you're probably thinking of Practical SQL: The Sequel

Hands on, limits of

[fm6]

Get a copy of whatever database you're going to use....Next, look at a database that someone else has written and attempt to manipulate the data through queries.

I suspect most database programmers learn that way. Which is actually a bad thing. Not that hands-on experience isn't important. But a lot of databases seemed to be designed by folks ignorant of the most basic concepts of relational theory. Many such programmers could stand to do a little reading. If Practical SQL Handbook is a decent mixture of theory and practice (I'm certainly gonna give it a look), it's probably sometime all those self-taught database designers should be reading.

Re:Hands on, limits of

[3am]

Yeah, there are too many programmers in the world that feel they are excellent dba's, while in fact they couldn't begin to talk about schema refinement, functional dependencies, attribute closure, or normal forms.

I would plug 'Database Management Systems' by Ramakrishnan and Gehrke for a more theory oriented approach to databases on an advanced undergraduate to 1rst year grad student level.

Re:Hands on, limits of

[fm6]

I would plug 'Database Management Systems' by Ramakrishnan and Gehrke for a more theory oriented approach to databases on an advanced undergraduate to 1rst year grad student level.

Problem is with books like that: they're basically useless unless you're (a) in some kind of academic program, and have good instructors and smart fellow students to help you get some kind of real-world context or (b) your name is Doogie Houser!

Another great book

[Stultsinator]

Joe Celko's SQL for Smarties

This thing covers all the basics, theory, and advanced topics I have ever asked of it. It will really take your SQL to a whole new level.

Why Sybase?

[puppetman]

Closed source, proprietary.

Why not Postgres 7.2 for the Linux crowd, and Firebird (Open Source version of Borland's Interbase db) for the Windows crowd.

Lots of graphical tools available, and not that difficult to set up (compared to Oracle, anyway).

Both implement all features that a modern relational database are supposed to support.

Re:Why Sybase?

[bad-badtz-maru]

Postgres 7.2 implements all of the features a modern relational database is supposed to support? How about master-master replication? Even master-slave replication is only supported through third-party patches that do not scale well. The open source databases (postgres, mysql) are poorly scalable and this lack of scalability makes it impossible to even start to compare them to their commercial counterparts.

maru

Re:Why Sybase?

[stoolpigeon]

Open source databases do not have all the capability of commercial dbs.

Books like this probably target people who will go to work for some company that will be using a closed database since they need that added functionality. (and can afford it)

That would answer your rhetorical question.

.

Re:Why Sybase?

[$0 31337]

Oracle isn't hard to setup.... I suppose if you didn't have any arms in which to control a mouse to hit the "Next" and "Finish" buttons then you may have a bit of difficulty, otherwise the graphical installer in easy to use.

Re:Why Sybase?

[ChannelX]

Can't speak for Linux but Oracle on Windows is damn easy to set up. Not sure how much easier it could be. As for administration 9i has even more auto-admin features.

Re:Why Sybase?

[wrt2]

From the wayback machine, an answer to your question: Master-master replication.
Behold the power of Google...

Re:Why Sybase?

[mesocyclone]

Why not Postgres 7.2 on Windows? I use it all the time. You don't need to go to Firebird.

Re:Why Sybase?

[ed1park]

Many of the financial companies on Wall Street use Sybase. They are very conservative with tech and are slow to adopt new technologies. Thus there is a large user base that can and does shell out big bucks to maintain/customize these systems.

Also, Sybase runs on Unix, Linux, and Windows.

So, Postgresql is a minor player in the real world. In the future, this will change of course.

Re:Why Sybase?

[$0 31337]

My previous employer, Jackson Laboratories - Mouse Genome Informatics Department, uses Sybase to store genetic sequences found in mice.... Sybase is quite robust if you can afford it.

Re:Why Sybase?

[puppetman]

Postgres under Cygwin is slow, and can be more difficult to setup and maintain (so says the people posting to the PostgresQL.org lists).

Re:Why Sybase?

[puppetman]

I'm an Oracle DBA, and while it's easy to setup, it's difficult to set up correctly.

And last I heard, Oracle is not free. We pay $14,000 per CPU every 2 years.

Re:Why Sybase?

[puppetman]

"Books like this probably target people who will go to work for some company that will be using a closed database since they need that added functionality. (and can afford it)"

The book is on SQL, not tuning, or database administration. As most databases are mostly SQL 92 compliant, it should be a non-issue.

Re:Why Sybase?

[puppetman]

Replication is not a feature of a relational database (modern or otherwise). It is a fail-over/load-balancing feature that exists in many products (networks components, application servers, message queues, webservers, etc).

For learing SQL, I fail to see how master-master replication is going to help.

Re:Why Sybase?

[Sxooter]

And which part of the SQL-89/SQL-92/SQL-99 standard defines and requires replication?

I'd rather have a rock solid database like postgresql running in a setup with manual failover and nightly backups than a dodgy, can't keep it up server like MSSQL server running on a cluster.

In 3 years of HEAVY postgresql usage we have NEVER had 1 data corruption issue, 1 failure, or even more than a few minutes of down time.

If it wasn't so damned reliable, I'm sure they could get more people to work on the replication packages for it (which, by the way, there is one that now supports master to master replication.)

But of course, to stay on thread here, we're talking about a database distributed with a book so people can learn about SQL, not replication, or drive management, or how to get the frosty cold beverage machine working.

For that, Postgresql is an excellent choice.

Re:Why Sybase?

[Sxooter]

Actually, postgresql under cygwin is quite fast, except at creating connections, which require a fork(), which in windows means SLOOOW. Once the backend is forked it runs quite well from what I understand.

Running multiple backends can be a problem, but that's more a windows issue than a cygwin issue.

Re:Why Sybase?

[bad-badtz-maru]

What do you do with your postgresql database when you need to accept more than 300-400 concurrent connections to the backend? I've used postgresql for four years, extremely heavily and without issue, and don't have an answer to that question.

But yes, you are right. Postgresql is one of the most standards-compliant databases.

maru

Re:Why Sybase?

[bad-badtz-maru]

Replication is certainly a feature of the majority of the commercial RDBMSs and essentially none of the open source RDBMSs. Replication is essential in applications where you need to make more connections to the database backend than can be accomplished with one machine.

As for learning SQL, since the book specifically deals with the Sybase variants and doesn't deal with any postgresql variants it makes more sense for the book to include Sybase than Postgres.

maru

maru

Re:Why Sybase?

[ChannelX]

I'm an Oracle DBA also and I dont see how Oracle on Windows is difficult to set up. v8.0x maybe but not 8i/9i.

As to cost I'm not sure what that has to do with my comments about ease of setup. IMHO its worth $14k/CPU every 2 years.

Re:Why Sybase?

[bad-badtz-maru]

The information in that post is flat-out wrong, there is no master-master replication for postgresql. There is barely master-slave, as in master-slave can be done via a really kludgey set of patches but there are serious caveats with regards to stored procedures.

The postgres developers (who are not responsible for any of the replication patches and have really just recently started to discuss the issue) do have some seriously good ideas with regards to replication. They plan on writing it around an existing open-source IP multicast framework. This innovation could propel postgres to the head of the RDBMS pack, however, it's "years away". Until that point, if you need more than 200-400 connections to the backend, you are going to have to either kludge or look elsewhere.

maru

maru

Re:Why Sybase?

[Sxooter]

Like everyone else, we use oracle.

But seriously, I'd like to try running it on something like a 16 way USparc with a stack-o hard drives running debian and see how that would work.

I can handle about 600 backends on a machine with 1 Gig of ram and a pair of 10k UScsi drives, but the reponse time if more than a 100 or so are active is just too slow at that point. And it's all I/O bound. The CPUs are still at 50% or more idle.

There are several replication options, by the way, but we haven't had need for them, as we generally have fewer simos that are doing heavier lifting, not many simos doing little work, which is where clustering becomes a necessity.

Re:Why Sybase?

[Sxooter]

Actually there are two things that are likely to show up around the 7.3 to 7.4 time frame that look quite useful.

One is time travel, wherein the Write ahead logs will be kept for a pre-determined amount of time, and allow you to basically set the way back machine to last month and look at your database from an historical perspective.

The other feature would also use the WAL method for replication (master - slave to start, master - master later). Basically, by piping the output of the WAL logs to a slave database, you could have replication running in a way that the two databases were EXACTLY identical, down to OIDs and what not. That looks like it may be such an easy win it might just show up overnight so to speak, when one of the developers gets a bug inzee hiney.

Re:Why Sybase?

[stoolpigeon]

The title of the book tells you that it is about not only SQL 92 but the variants out there in commercial systems.

That the book comes w/a commercial database bundled in backs this up. The original post asked why sybase?

I was answering him. Got modded down for giving the guy a little info. YES! I'm a little bitter right now.

.

Re:Why Sybase?

[bad-badtz-maru]

The time travel feature is odd, because the origins of postgres are from a grad student's work where the feature you describe was the entire point. He was writing an RDBMS that could service queries relative to what the database looked like at a particular time. The article I read about this didn't mention what had become of this, I figured the guy had finished it (this was like 15 years ago) and the feature had been long since removed. Maybe in reality the feature was never finished.

I think the issue with WAL piping might be in transactions. There is a huge thread somewhere on google where the primary postgres developers were starting to discuss replication and I remember WAL duplication as being one of the methods someone came up with holes in. They essentially tore holes in every method that everyone was coming up with. Then, of course, the idea they came up with was absurdly advanced. The idea of using unicast for rdbms replication is, I would say, groundbreaking.

maru

Re:Why Sybase?

[mpeppler]

Note that there is a free version of Sybase for linux. See linux.sybase.com for details.

Re:Why Sybase?

[Anonymous Coward]

It's also worth noting that there is a native Win32 port of PostgreSQL in development (or at least, several people have expressed interest in such a project on pgsql-hackers). So with any luck, you should be able to run PostgreSQL on Win32 without cygwin in the near future, and enjoy the same kind of stability and performance you see on UNIX (to the extent that Windows allows it, anyway :-) ).

Re:Why Sybase?

[moooooooo]

i was going to post the same info :)

hey mr peppler, (or should i say mr SybPerl ;p ) i use 11.9.2 on linux and it works very well.

i have millions of rows of stock market historical data and even on a PIII 500 with 256mb RAM it is quick - but it's a well designed schema...and yes i use SybPerl :)

Re:Why Sybase?

[Anonymous Coward]

Replication is essential in applications where you need to make more connections to the database backend than can be accomplished with one machine.

And how many applications is that? In the vast majority of situations, you can run all your database traffic off a single dedicated machine. If your application is doing so much database work that a single machine can't handle it, by all means: use DB2, Oracle, or another commercial DB. But I'd wager that fewer than 1 in every 100 database applications has those kinds of requirements.

But otherwise, I agree with you. Replication is nice, and I'd like to see it in PostgreSQL soon. I just think it's not that essential, since 99/100 people are fine with a single machine.

Where's the spoiler alert?!

[lor3]

Thanks for warning mr. lone gunmen.

Hmmm....

[Anonymous Coward]

Continuing the grand tradition of reviewing computer texts sent to us by publishers, Slashdot author chrisd has read the book The Practical SQL Handbook: Using SQL Variants and written up a review.

chrisd can read?

Resources for learning SQL

[ThadMan]

I am curious about what books or resources other people have used to learn SQL. I got my start in SQL using the book LAN Times guide to SQL. It was a really good book.

awwwwwwwww

[Anonymous Coward]

"...at least it wasn't glued to the back cover (a pet peeve) and was instead bound into the book like a magazine reply card."

Awwwwwwwwww, does that get your panties in a bind?

Inadequate.....

[Anonymous Coward]

Actually, I have read this book. Don't waste your money. I have been looking for years for a SQL book that can come even close to showing me the advanced features that I am required to work with everyday. This book still does not exist. Maybe I will have to write my own.

Re:Inadequate.....

[Anonymous Coward]

Mod this up moderators.

I haven't found any good SQL resources either. Anybody know of any very advanced SQL books out there?

Re:Inadequate.....

[bill_guts]

Have you read "SQL for Smarties" by Joe Celko?

No good

[fm6]

Most of us have matched set coasters with an AOL theme.

Re:No good

[baldass_newbie]

Most of us have matched set coasters with an AOL theme.

Do you ever wonder if you'll see a collection of ALL of the AOL coasters, er, uh, I mean, CDs some time in the future - selling as a set for outrageous money and think, "I could have done that and it would have cost me nothing."

Good Suggestion for T-SQL

[stoolpigeon]

And learning T-SQL is a good idea if you want to work for someone who uses SQL Server. (Which is a lot of people- so more power to you)

But lets say you want to run a database for yourself or you are a smaller company. Then I would not recommend worrying too much about learning a variant of SQL tied to an expensive propietary system. (SQL Server only runs on MS NT or 2000- and so you've got server licensing, db server licensing and then seat licenses for everyone who will connect to the db)

At my small company we looked at expanding a product so we priced a new server- and then SQL Server licenses for that server and 200 users. The licensing on the software was much more expensive than the hardware we wanted to buy.

Our solution? We are going w/PostgreSQL. It has some very nice visual tools for management. It has good ODBC support. And it has most of the capability that SQL Server has. Enough to justify taking advantage of the monetary savings.

And Oracle? Forget it- more expensive than SQL Server.

Granted there are businesses out there where the cost of Oracle or SQL Server is more than justified- but those huge companies are a minority of the business world. There are many more like us- not huge but we need good RDB systems.

.

Re:Good Suggestion for T-SQL

[rnd()]

excellent... i'll have to look into postgres in more detail... do you know if there is a tool out there similar to MS query analyzer and enterprise manager that will work with postgres?

I just like to be able to go in and modify tables and look at recordsets easily...

Re:Good Suggestion for T-SQL

[stoolpigeon]

I'm just getting into this myself.

At this point I've been using PGAdmin II. It is open source written in VB (yeah - I use VB and I'll admit it). It gives you the ability to do quite a bit of what you can do in enterprise manager.

There are other tools that I have not used yet that will work on multiple platforms. The postgreSQL has great documentation and links to many of the useful tools. This is one project where it is very, very easy to find what you need to get the ball rolling.

PostgreSQL can not do everything SQL Server and Oracle can do. As of right now you cannot back up transactions. I believe it is also limited in regards to replication and some other features that the big boys handle pretty well.

But in many cases, like ours- we don't need that stuff. I just need a dependable rdbms that doesn't cost an arm and a leg and isn't a piece of crap like MS Access.

.

Re:Good Suggestion for T-SQL

[Anonymous Coward]

im not sure its ready yet

but the guy who makes Tora for oracle http://www.globecom.se/tora/

added postgres support for their sql worksheet. not as good as query analyzer, but its pretty good. works on linux as well.
why oracle cant seem to make a decent query tool
is beyond me.
this guys product is better and he didnt have to use java bloat to make it cross platform.

Re:last post bitches

[Anonymous Coward]

Man, if that could only be made to stick, it could improve slashdot discussions 1000%.

You call that a Review?

[bill_guts]

This is horrible - no sample chapter link, no table of contents...just a lot of MySQL disclaimers. That doesn't cut it. How well this book's material covers Oracle, MS SQL Server, Sybase, DB2 (and the above mentioned DBs) is important. What's in the book, stupid?

Don't give away the ending!

[lobotomy]

Don't pull another "Lone Gunmen" -- the book hasn't made it to this time zone yet!

MASTER!

[Anonymous Coward]

Can't I put this in my .sig?

Become one with the google

[gazbo]

No point in me re-writing what this guy wrote.

WTF ?!?

[Petronius]

a developer uninterested in Oracle, MS-SQL, Sybase or Informix

OK, so you're not interested in the DB of choice of 90% of the dev. population. Fine. But then it begs the question: why are you writing this review?

I think we'd all could benefit from the opinion of someone that has used at least 2 or 3 RDBMSs!

Your review reminds me of a Woody Allen quote: I took a speed reading course, then I read War and Peace. It's about Russia.

Better luck next time!

"Elmo loves you" posted by Elmo

[Anonymous Coward]

Posted by chrisd...Continuing the grand tradition of reviewing computer texts sent to us by publishers, Slashdot author chrisd has read the book The Practical SQL Handbook....

Continuing the grand tradition started by Elmo (the muppet) of speaking the 3rd person about oneself, chrisd introduces his own review this way.

Speaking in the third person can indicate mental illness, such as split-personality disorder. Imagine this scene on Sesame Street:

"Hey Elmo, how are you feeling today?"
"ELMO WANTS TO HURT PEOPLE!"

Or how about this?:

"Hey chrisd, what do you think of Nvidia's latest video card offerings?"
"CHRISD WANTS TO HURT PEOPLE!"

Watch out taco!

Joe Celko kicks ass

[Anonymous Coward]

I admint he is the mannnnnn. check out microsoft.public.sqlserver.programming for his bits of wisdom and wit.

SQL is a different monster than procedural programming.

I am one with google. I also bother to _read_.

[Luyseyal]

I did google for those. I also bothered to read the project statuses for each one. PostgreSQL-R critically lacks:

• support for replicated DDL
• support for version 7.2.x

Whether they support a Master-Master configuration or not (which, you'll note, is not even mentioned on the site), it's not near finished enough to compete with Sybase et al. in this particular, but important area.

-l

Re:I am one with google. I also bother to _read_.

[mi]

Does Sybase support master-to-master replications? I don't think so :-( Master-to-slave -- yes, though...

-mi

Re:I am one with google. I also bother to _read_.

[Anonymous Coward]

Sybase has supported master to master for at least 8 years. It is a super flexible, reliable, and relatively easy to manage system.

When I started out..

[Colossus]

The first ime I was tasked with creating a more complex database I read a book called "Database Design for Mere Mortals" it really helped out.

I recommend it to anyone getting into database work.

http://www.amazon.com/exec/obidos/ASIN/020169471 9/ qid=1024686302/sr=8-1/ref=sr_8_1/102-8984857-99665 49

Naw

[fm6]

Such thoughts are not healthy. They lead you to accumulating useless crud on the off chance that it's "collectable". I'm not even going to talk about those "commemorative editions"...

I do wish I had bought California real estate when it could be had for 5 figures. But I thought it was too high....

Why even need explicit joins?

[Tablizer]

As long as you specify the tables involved, the DB engine can easily figure out the primary join paths (if you set it up right). It seems to me that is better factoring than to repeat the relationships over and over.

Only when you are doing something different or odd should the joins need to be explicitly stated, and only incrimentally. (I know some DB's already support something like this, but it should be standardized IMO.)

Then again, if we are going to change SQL, then perhaps overhaul it completely (another thread below).

Re:mod this up

[Splork]

why did some moron mod this down and give it a troll rating? he makes a perfectly good observation asking a perfectly good question.

SQL Aternatives (was: SQL Limitations ?)

[Tablizer]

(* Exactly. Poor design is not an excuse to blame SQL. SQL is math. Can math solve bad design? *)

But there are different ways to "math" the same thing. For example, Relativity and Quantum Physics may solve certain problems, but sometimes old fashion Newtonian physics can do it quicker, have a shorter learning curve, and be 99.99999999 percent as accurate.

If I replaced SQL, here is some draft suggestions:

http://geocities.com/tablizer/relat2.htm

My biggest complaint is that SQL is too nested-based, whereas, I would rather see it be reference-based. Graphs are more general-purpose than trees. Plus, being able to isolate the name-space into smaller chunks would be helpful IMO.

This is NOT SQL-99

[Anonymous Coward]

JOIN, CASE, NULLIF and COALESCE are all in SQL-92. SQL compliance is measured in levels. Oracle 8i met some minimum entry-level compliance, but is quite poor at implementing SQL 92 features. Oracle 9i is considerably better.

SQL Server is pretty extensive in its SQL-92. They got concatenation wrong, which seems like "embrace and extend."

Postgres is probably the best popular ANSI92 SQL engine out there right now.

Re:Why Mods Suck?

[stoolpigeon]

I don't complain about moderation much but sometimes you can only take so much.

I would love to here from whoever moderated my post as Flamebait and have them explain some reason for that. There's nothing I said that isn't accurate and parts of it are posted all over this thread.

I guess I committed the cardinal sin of posting something that did not toe the party line. How freaking pathetic.

I like the moderation system and I like to moderate- but some times I just get pissed when some idiot who knows absolutely nothing mods someone down.

I think modding down should burn 2 points and modding someone up should burn 1. Too many people are way too free w/off topic, redundant, troll, etc.

So to the faceless, ignorant moderator of my post let me just say - You Suck.

(yeah - its friday I've got some time on my hands and I do feel better now. That's worth a little karma)

.

Recommendation...

[Laser Lou]

I first learned SQL from an earlier edition of that book (Probably the third.). A coworker borrowed it and kept it when he left for another job.

If you want to learn SQL, buy that book, and don't loan it to others.

Re:mod this up

[Anonymous Coward]

He hasn't read the book and is making stuff up to get a response. The author didn't say anything like that.

Celko for all skill levels

[gripdamage]

Have you read "SQL for Smarties" by Joe Celko?

And for those just getting stated there is:

Instant SQL Programming - Joe Celko

SQL in a Nutshell helpful for variants

[billmil]

As for keeping track of sql variants, I recommend Oreilly's *SQL in a nutshell*. It's helped me quite a bit developing a vendor neutral app that runs on both Oracle and SQL Server. (It covers Postgres and MySQL also).

As the reviewer posted, learning sql and learning the various flavours IMHO is too much for one book. The Nutshell book is a reference for advanced users.

Re:SQL in a Nutshell helpful for variants

[emilami]

I second that... I hadn't done anything with SQL until late February and that book has been a huge help to me. My boss handed me a big scary textbook to look at because he wanted me to learn SQL, and I immediately went out and found an O'Reilly. With that, I caught on quickly. Part of that may be my familiarity with the syntax from using some COBOL in the past, though. I highly recommend the O'Reilly books for everything you can find them for.

Re:mod this up

[Anonymous Coward]

Except that if he wants to comment on sub-optimal SQL standard compliance, he should limit his remarks to MySQL. PostgreSQL has excellent support for SQL92 and much of SQL99 -- in many respects, PostgreSQL's support for standards matches or exceeds that of commercial DB systems.

MySQL, on the other hand, can rightfully be criticized for not supporting much of SQL92. But don't tar all open-source databases with the same brush...

Anybody remember SEQUITUR?

[Bourbonium]

Ahh, takes me back to the old days (1987). The first relational database I ever worked with was SEQUITUR, developed by Lee Felsenstein, the engineer who designed the breakthrough Osborne PC the first portable/lugable computer, (see http://www.obsoletecomputermuseum.org). I didn't know at the time how SEQUITUR performed its magic, but I figured out how to use it, and loved it. Felsenstein never marketed it well enough to compete with dBase or Paradox, but I think it was better than either of them. Many years later, when I took classes in MS-SQL, I began to see how it all fit together and evolved into something even more powerful.

T-SQL != MS SQL Server

[Tadghe]

Actually Transact SQL is not completly tied to M$ SQL Server.

You'll find it used with at least some versions of Sybase.

Remember M$ SQL Server's History (purchased from Sybase, indeed they were Paying Sybase royalties until 7 IIRC).

That being said, while I try and stick as closely to SQL89/92 as possible, I would say that TSQL with it's extentions is not nearly as much of a PITA as PL/SQL.

SQL is not Turing Complete

[Anonymous Coward]

Virtually any interesting programming language is Turing complete. This means that it is complex enough to model a simple state engine. Since this simple state engine is able to model any known computer language, in some sense virtually all computer languages can do the same tasks. (Albeit with different amounts of ease.)

SQL very deliberately cannot do this. It has virtually no procedural capabilities, and never will. Just because you can think of a procedure to accomplish something does not mean that SQL will let you do that.

The reason for the limit is that SQL is supposed to be analyzed and optimized. Turing complete languages cannot fully be analyzed or optimized. (Search for "The Halting Problem".) SQL theoretically can be, and with a good database the way that it finds to actually carry out a query is very likely to be much better than any procedure that you would have thought of.

A classic example of a problem that SQL does not handle well is the old "bill of materials" problem. You want to build a car. You know what parts go into a car. For each part you know what parts it requires. And so on. It is fairly easy to set up your table.

Now how do you, in a single query, find all of the materials that go into building a car? You can't answer that directly. Instead you need to arrange to do a set of queries, or you need to arrange to have some auxilary tables that you carefully synchronize just to answer this question.

The same problem arises whenever you want to model a tree-like hierarchy (eg a threaded view of a discussion) in a relational database.

That answer your question?

Doesn't work all the time

[kpharmer]

I use both join forms with SQL Server, and have finally gotten used to the explicit form. However, the Microsoft implementation is less than perfect:
- you can't use derrived tables
- you can't group one table and then join it to another
- there are also limitations in how you can use outer joins that will occasionally force you to use the old form.

Still, I find that it can improve the maintainability of SQL - especially with careful formatting:

select member.*,
member_customer.*
from member m
left outer join member_customer mc
on mc.id = mc.id
and mc.customer_id = 1
left outer join member_supplier ms
on mc.id = ms.id
left outer join member_preferences mp
on mc.id = mp.id
where m.state = 'IL'