Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 


Forgot your password?
Close
typodupeerror
×
Programming Books Media Book Reviews IT Technology

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

Posted by chrisd from the sql-doesn't-mean-strong-quizzical-llama dept.
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.
This discussion has been archived. No new comments can be posted.

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

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

Comments Filter:

  • Is the sequel better? (Score:5, Funny)

    by tomknight ( 190939 ) on Friday June 21, 2002 @10:34AM (#3743907) Journal
    I note that this is the 4th edition - is this one of those times that the sequel is better than the original?

    Tom.

    • Not much different. (Score:2, Informative)

      by 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.
    • the sequel is better than the original

      or, maybe:
      the SQL is better than the original

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

      • 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.
        • We're supposed to be smart, not bright. ;) I guess all code and no play makes Slashy a slow boy.
    • No, But the SQL is much better!
    • 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

    • 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.

  • Well-done book (Score:2, Interesting)

    by lseltzer ( 311306 )
    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.
    • 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!
    • 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.
  • 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.

    • 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.
      • 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!
        • I'm curious : where did you find stuff about PostgreSQL replication ? Never found it in the doc. Can you point some URL ?
        • 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 (Score:4, Informative)

            by gazbo ( 517111 ) on Friday June 21, 2002 @11:25AM (#3744215)
            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".

            • 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
    • why did some moron mod this down and give it a troll rating? he makes a perfectly good observation asking a perfectly good question.
  • 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.
    • 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.
    • 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.
  • 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 (Score:5, Insightful)

    by Cinnibar CP ( 551376 ) on Friday June 21, 2002 @10:43AM (#3743970)
    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)

  • Excellent!!! (Score:3, Funny)

    by baldass_newbie ( 136609 ) on Friday June 21, 2002 @10:44AM (#3743981) Homepage Journal
    The CD has a copy of Sybase for the user to work with.

    A free coaster!

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

      Or better yet, if you have a tesla coil you can impress [netcomuk.co.uk] all your friends!

    • No good (Score:2)

      by fm6 ( 162816 )
      Most of us have matched set coasters with an AOL theme.

  • SQL Limitations ? (Score:2, Interesting)

    by smak ( 193931 )

    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 ? (Score:3, Interesting)

      by Entrope ( 68843 )
      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.

      • 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.
        • 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."
          • 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.

          • (* 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 ? (Score:4, Insightful)

        by reemul ( 1554 ) on Friday June 21, 2002 @11:32AM (#3744260)
        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.
        • 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.
        • 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.
      • 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.

      • 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.

    • 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.
      • 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.

  • Glad they emphasis SQL-92 (Score:3, Insightful)

    by pmancini ( 20121 ) <pmancini@[ ]oo.com ['yah' in gap]> on Friday June 21, 2002 @10:54AM (#3744036) Homepage
    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

    • "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.

    • 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.

      • 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.

    • 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!

    • 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.

    • 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.
    • 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


    • 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.
    • 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.
    • 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.

    • 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.

  • SQL Security (Score:2, Insightful)

    by mrkitty ( 584915 )
    www.cgisecurity.com/lib [cgisecurity.com] Has some good papers on sql security.

  • Another good book (Score:3, Insightful)

    by Giant Robot ( 56744 ) on Friday June 21, 2002 @10:59AM (#3744064) Homepage
    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.

  • MySQL again (Score:5, Insightful)

    by fm6 ( 162816 ) on Friday June 21, 2002 @11:11AM (#3744134) Homepage Journal
    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.
  • 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.

  • How does this compare to A Visual Introduction to SQL [amazon.com]? 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

  • 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.

    • Hands on, limits of (Score:3, Insightful)

      by fm6 ( 162816 )
      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.
    • 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.

      .
      • 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...

        • 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.

          .
      • 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.

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

  • Titles? Editions? Sequels? (Score:3, Informative)

    by fm6 ( 162816 ) on Friday June 21, 2002 @11:37AM (#3744304) Homepage Journal
    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 [amazon.com]
  • 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? (Score:4, Insightful)

    by puppetman ( 131489 ) on Friday June 21, 2002 @11:56AM (#3744432) Homepage
    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.

    • 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
      • 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.
    • Why not Postgres 7.2 on Windows? I use it all the time. You don't need to go to Firebird.
  • Thanks for warning mr. lone gunmen.
  • 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.
  • 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.

Slashdot Top Deals

If all else fails, lower your standards.

Close