Catch up on stories from the past week (and beyond) at the Slashdot story archive

 


Forgot your password?
Close
typodupeerror
×

SQL Cookbook 172

Posted by samzenpus from the database-dinners dept.
Simon P. Chappell writes "One of the staples of corporate I.S. development is processing data, and increasingly these days that data lives in a relational database. The lingua franca of relational database programming is the Structured Query Language (SQL), often pronounced "sequel". Many programmers find that the basics of SQL are easy to learn, but after that, it tends to get complicated. Enter the SQL Cookbook from O'Reilly." Read the rest of Simon's review.
SQL Cookbook
author Anthony Molinaro
pages 595 (9 page index)
publisher O'Reilly
rating 8/10
reviewer Simon P. Chappell
ISBN 0596009763
summary If you need help working with a database, this is the book for you.


The book is not for beginners and makes no efforts to teach any SQL. It concentrates purely on building on the base level of SQL knowledge that most programmers have. If you know the basics: Create, Read, Update and Delete (an unfortunate, yet memorable acronym) but rarely go beyond that, this book is for you. I know that I fall into this target market.

The point of the cookbook is that you need to get something done and you need to get it done sooner rather than later. Now, most of us can figure out most things given enough time, but in the real world, we rarely have enough time. The cookbooks objective is to save you time by giving you a successful approach that you could have figured out eventually anyway.

If you've previously read any other technology cookbook from O'Reilly, then you already know the structure of the recipes. For those new to the O'Reilly cookbook format, it's actually fairly straightforward. Each recipe starts out with a problem statement. Recipe one in chapter one, titled "Retrieving all Rows and Columns from a Table" has the problem statement "You have a table and want to see all of the data in it." Nice and clear. Then comes the solution. Naturally, for this problem statement we end up with a SELECT that looks like this:


select * from emp


Then the recipe has a discussion section where the solution is explained in more detail and the reasoning behind it is provided. For recipe one, the discussion explores the trade-offs between using the "*" to signify all columns versus naming each column explicitly.

The chapter structure through the book is very much one of building on the previous material. The first chapter starts with the fundamentals, the retrieving of records and then chapter two takes over with sorting the results of your query, while chapter three looks at using multiple tables.

Chapter four covers inserting, updating and deleting records. After that it's back into the world of queries, with chapter five exploring metadata queries for those times when you need to know just a little more about what's going on under the covers. Chapter six looks at working with Strings, a much harder topic than it would seem at first thought. Chapter seven addresses working with numbers and chapter eight does the same for date arithmetic with chapter nine bringing more understanding to general date manipulation. Chapter ten looks at working with ranges.

Chapter eleven dives into advanced searching in preparation for chapter twelve's information on reporting and (data) warehousing. Hierarchical queries are always challenging, so they're reserved for chapter thirteen. Finally, chapter fourteen is titled "Odds 'n' Ends" and is a general catch-all for some pretty advanced, but very infrequent problems.

I guess you either like the recipe approach or not. I love it, so I'm listing it as something to like about this book. The recipes are very well explained and while each one presents only one approach, where there are obviously multiple options, the discussion takes care to explain the reasoning behind the selection.

The writing is clear and the explanations are well laid out. Both the SQL code and the query results are presented well and are easy to read.

A very important part of the book is that it covers SQL variations for Oracle, IBM's DB2, Microsoft SQL Server and the open-source databases PostgresSQL and MySQL. Each of the recipes includes solutions for each of the five databases. While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard; hence the book has to present up to five solutions for each problem.

Many of the recipes are advanced. While the front of the book has the entry level material, it ramps up in complexity pretty quickly. For some of us with very straightforward SQL knowledge, some of the recipes are going to take a while to fully understand and be able to use.

Many of the recipes are obviously oriented towards corporate reporting. If this is a big need for you, then this book goes along way to meeting your needs. If you have no need for corporate reporting, it's wasted paper.

This is an excellent book; it does exactly what it sets out to do and fully equips you to handle the most sophisticated database transactions.

You can purchase SQL Cookbook from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
This discussion has been archived. No new comments can be posted.

SQL Cookbook More

SQL Cookbook

Comments Filter:

  • Recommendation (Score:4, Informative)

    by PFI_Optix ( 936301 ) on Wednesday May 10, 2006 @03:09PM (#15303151) Journal
    As most people I know learn SQL and PHP together, if you're to the point that this book would be of benefit to you, I'd suggest also getting the PHP Cookbook. That book has done wonders on improving the quality of my code.

    • Re:Recommendation (Score:4, Insightful)

      by SatanicPuppy ( 611928 ) <Satanicpuppy.gmail@com> on Wednesday May 10, 2006 @03:15PM (#15303208) Journal
      If they write SQL like most people write PHP...yeesh.

      In my experience, the people who generally most need SQL books like this are the report guys...You know, not really IT, just kinda work on the fringes of things with Access and Crystal Reports...Don't know much about code, but are good with the data. I can see one of these being really useful. I have the Java Cookbook (and the Php cookbook, as it happens. =P), and they're pretty handy, though they fill a niche that I usually fill with google.

      • Re:Recommendation (Score:5, Informative)

        by PFI_Optix ( 936301 ) on Wednesday May 10, 2006 @03:28PM (#15303321) Journal
        Google has failed me a few times with PHP issues, the cookbook has helped fill in the gaps there.

        Problems with finding answers on the web:

        1) The code for the answers to problem A and problem B don't necessarily work together

        2) The code is of questionable quality. I'm six months in to teaching myself PHP (and CSS, SQL, and a few web server applications) and I can tell you that the code I wrote just two months ago was utter crap, largely because it was based on web tutorials that completely neglected good practices.

        3) I have no prior experience or training with programming, which makes it hard to even know where to start looking for a solution. Books like this have helped me learn how to know what I need to search for.

        • Re:Recommendation (Score:5, Informative)

          by jbarket ( 530468 ) on Wednesday May 10, 2006 @03:56PM (#15303564)
          The PHP manual is the absolute must use resource.

          Not only is it obviously a complete reference guide to the language, but the user comments on each function often cover a wide variety of uses that can help solve your particular problem. More importantly, if poor quality code is posted, someone will often post a cleaned up version.

          Not to knock books at all. Just saying, there's a great free resource out there that is vastly superior to the hundreds of ad ridden script sites.
          • Agreed. The php online manual is one of the best features of the language. I also like how it's presented; simple and esaily searchable.

            I went and picked up O'Reilly's Safari Service [oreilly.com], and while it is perfectly sufficient for slogging through an entire book, it's pretty inadequate the day-to-day function reference stuff, mainly due to its clunky interface. Fine, you need to split the pages up, but if I have to load a new page more than once a chapter, I'm going to be pissed. I'm not pleasure reading here, I

      • Now, I would like to have this book to support my own ends, but as a report guy, I don't particularly need it. Why not? Because Crystal does it for me. Anything so complicated that Crystal will screw it up, I wouldn't be doing in Crystal anyway. That's when I would need more SQL for actual work, but the fact is that I don't need it anyway, because I don't have to do any reports too complex for crystal.

        Meanwhile, no one with sense is going to write their own SQL (except for occasional SQL statements to r

        • Re:Recommendation (Score:3, Interesting)

          by rainman_bc ( 735332 )
          Now, I would like to have this book to support my own ends, but as a report guy, I don't particularly need it. Why not? Because Crystal does it for me. Anything so complicated that Crystal will screw it up

          I'm a report guy, and let me tell you that you cannot count on Crystal to do it all for you. Sometimes the request comes in is too complex to solve without using correlated subqueries, derived tables, and the like which Crystal Won't properly use for you.

          Trust me, I work for an online gaming company, and
          • Sure, but once it's that complicated, I'm more likely to abandon crystal entirely, and just go generate HTML with perl, unless I explicitly need to generate a PDF or XLS from the results, because Crystal is Crap.
            • unless I explicitly need to generate a PDF or XLS from the results, because Crystal is Crap.

              Then you don't know how to use Crystal well enough. Compared to Xreporter or Jasper or R&R Report writer, it's pretty damned good, don't kid yourself.
              • The least stinky turd in a row of turds is still stinky. I'm tired of crystal crashing all the time. Sad thing is, sometimes it does it when I'm only getting back a small record set.

      • Re:Recommendation (Score:4, Interesting)

        by WindBourne ( 631190 ) on Wednesday May 10, 2006 @03:44PM (#15303463) Journal
        Normally, I do staight coding on servers, sometimes the client. Most of my work does not involve huge amounts of DB work. So I get by on the DB, but I do not consider it a strength.

        Now, I am in a job where currently, I am testing data that is being modified through a series of transforms. We test by loading the end work and taking it back to the initial work (i.e. we reverse the process). We find it easy to load the data into a Postgres DB, create views with conversion functions to take end->initial and then run an assortment of queries over the data to make certain that it is correct. I have found that I do go back to several book to make sure of my sql as well as to improve the speeds (a subquery took 20 min to run while using a except gaves a similar answer in under several seconds. BIG difference considering that we are looking at many tables with a number of tests each).

        The books help.
        • i have found that the more books you can read on data Structures the better - they don't even have to be based on things you use it jsut gives you a good refrence..

          It is amazing how much you can optimize things if you know what your doing and can visualize things well..

          only issue i have is that i tend to over design/build things.. which makes them take longer.. but i have never had to restart from scratch to implement something new so it is worth while
      • Just to point out I'm a report guy. I know Java, Ruby, PHP, and some Perl. I know VB.Net and C#. I know Coldfusion and ASP. I've worked with SQL Server, Sybase ASE, and DB2. I've got skills. Most girls like guys with skills :) I'm not a dumbass, and I get paid well because I'm not one.

        The problem is many report people and PHP guys can't use more advanced sql techniques like:
        • Derived Tables
        • Correlated Subqueries
        • Proper Utilization of Indexes
        • Query optimization

        The other problem is we're hired to do work n

    • Re:Recommendation (Score:5, Insightful)

      by fh8510 ( 967845 ) on Wednesday May 10, 2006 @03:37PM (#15303400)
      Most people I know who use SQL work in Java, Ruby, Python, C++, JDBC, ODBC, ASP.NET, Hibernate, etc. Please, there are so many SQL related technologies. PHP is not the be-all, end-all one. The beauty of SQL is that it's a language, platform independent way to define and manipulate relational data. It's a data-centric way to deal with data. Adding one of the related language to the mix just muddles things.
    • Select *
      From US
      Where grits=hot AND natalieportman=naked

    • I know most of you have read this one, but as a service to our new readers:

      The Art of Unix Programming [catb.org]. I read that one as a budding PHP coder; while very Unix/C centric (well, duh) the basic philosophies will benefit any coder. My code's not that good, really, but without the principles outlined in the book it would be absolutely horrid.

      If the PHP kiddies would read it we would at least have properly indented code.

      I forget; what's the official Slashdot position on ESR these days?

  • sequel? (Score:5, Funny)

    by TechnoLust ( 528463 ) * <kai...technolust@@@gmail...com> on Wednesday May 10, 2006 @03:15PM (#15303200) Homepage Journal
    Shit, I've been calling it squirrel all this time!

  • Microsofties say "sequel" (Score:1, Informative)

    by Anonymous Coward
    Unix MEN say S.Q.L.
    • Indeed. I was always under the impression that "sequel" came from an old precursor to SQL called Structured English Query Language (Thus SEQL). Oh well, what do I know. I still call it S.Q.L. myself.

      • Re:Microsofties say "sequel" (Score:5, Informative)

        by filesiteguy ( 695431 ) <perfectreign@gmail.com> on Wednesday May 10, 2006 @03:31PM (#15303353)
        I believe you're correct. http://en.wikipedia.org/wiki/SQL#History [wikipedia.org]
        During the 1970s, a group at IBM's San Jose research center developed a database system "System R" based upon, but not strictly faithful to, Codd's model. Structured English Query Language ("SEQUEL") was designed to manipulate and retrieve data stored in System R. The acronym SEQUEL was later condensed to SQL because the word 'SEQUEL' was held as a trademark by the Hawker-Siddeley aircraft company of the UK. Although SQL was influenced by Codd's work, Donald D. Chamberlin and Raymond F. Boyce at IBM were the authors of the SEQUEL language design.[1]. Their concepts were published to increase interest in SQL.

        Oh, and as to the OP stating that SQL is difficult, try IMS.

        I'll take any SQL system over an IMS or other heirarchical anyday. :P I had the unfortunate need to learn IMS after years of various SQL databases. Sure it is faster, but what a learning curve!

        • I tell the business types whose reports I do that it was named for the little squirrels inside the machine that horde and organize your data like acorns. Except they call the acorns tuples, and they get stored in relations.

          To be in 1NF, your data must first be "acornic" in that it cannot be broken down further. There must be no repeating groups of acorns; and each row of acorns must depend entirely on the massive acorn (aka key acorn) that the squirrels use to organize the other acorns.

          This also explains wh
        • Structured Query Language is a misnomer, as it is not a complete language, just a small syntax, it is not limited to queries, and anybody who has used it will agree it's not structured.

          For example, if you want to change data you use a different command to changing database structure.
          • For example, if you want to change data you use a different command to changing database structure.

            Nitpick: If an RDBMS has properly implemented updatable views for the catalogue, you should be able to run insert/update/delete statements against the catalogue tables and have DDL happen automatically. SQL itself doesn't prohibit this that I know of, though probably very few RDBMS's actually allow updatable system tables. In practice, you can sometimes get away with this for simple datatype changes (that don'
      • I also call it S.Q.L. But there's a guy at work that pronounces it "squeal" (like a pig). This makes design meetings painful, since I spend a lot of time trying to delete this mental image of Ned Beatty doing DB administration. "Yew got a purty table, boy."
    • Ess-Cue-Ell. Yes indeedy. What about an Access Control List? Ayy-Cee-Ell or Ack-uhl?

    • Bullshit (Score:2, Informative)

      by Reality Master 201 ( 578873 )
      First, SQL comes from SEQUEL ( Structured English Query Language), created by IBM in the 70's for getting data from System R.

      Second, I know plenty of mainframe and UNIX guys from way back who say "sequel" for SQL, some former IBMers, others not. The only people I ever hear call it "ess-que-ell" are management types and some FOSS people who have only ever heard about databases from reading stuff on the web and have never had a real job working with real databases.

    • And the Microsofties are correct, if referring to the server platform. 'MS-SQL Server' is 'Microsoft Sequel Server.' (It's MS's product; they can call it whatever they wish.)

      The structured query language is abbreviated 'ess cue ell.'

      So it's perfectly correct to say, "let's see how that ess cue ell code performs on the sequel server."
      • "So it's perfectly correct to say, "let's see how that ess cue ell code performs on the sequel server."

        yet, highly confusing.
        Other database companies call the database a sequel server.

        More accuratly:
        "So it's perfectly correct to say, "let's see how that ess cue ell code performs on the MS sequel server."

        This all goes to Microft naming things in an unclear manner to make people think everything in an area of computing is theirs.

        • Yet I find it works against them in this day of search engines. If I search for a concept or problem relating to one of their products by the product's name, say, "word", "publisher", "sql server", "windows", etc. Google is in no way required to assume I'm referring to MS products; it also should not have to guess that the one random forum post I'm looking for was intended to refer to a Microsoft product, even though the author forgot to put 'Microsoft' in front of each reference to the product. I hate cute

  • BN vs. Amazon (Score:5, Informative)

    by CheeseTroll ( 696413 ) on Wednesday May 10, 2006 @03:17PM (#15303223)
    You could pay $39 from BN, or $26.37 at Amazon.

    This looks like a very handy reference for those of us who can read SQL statements alright, but have grown lazy with all the GUI SQL-statement builders that exist now.

    • Re:BN vs. Amazon (Score:3, Informative)

      by nuzak ( 959558 )
      Or you could install BookBurro and compare prices yourself.

      Just google for it. It's an extension these days, not a greasemonkey script, which has its good and bad points (hard to add another bookstore yourself).

      Too bad SKU's aren't common in the public-facing interface of online merchants ... you could expand bookburro to nearly any product at all in that case.

    • GUI SQL statement builders? They're actually useful? Maybe I haven't seen the right tools, but the ones I have were dreadful. Only useful for writing the simplest select queries with perhaps simple joins. Chances are I can type faster than I can use the GUI. At the end, I have a readable SQL code that I can easily debug. Some queries just don't make sense doing in one go (performace sucks - e.g. doing a cross-tab report on big tables) and I find it's much easier to do a multi-step query by hand than b
      • They are pretty dreadful if you're creating a mildly complex query. But something like Access, or OpenOffice Base, or even Crystal, is very useful for browsing through the tables and fields when you can't remember how to spell every one of the 150+ tables, not to mention all the fields, in your company's system. The vast majority of queries are relatively simple select statements, anyway.
    • Here's the link [amazon.com] to Amazon.

      Yes, it's an affiliate link, flame away, what do I care.
      • I used to do affilliate links every time I talked about a book on Slashdot. Once I even snuck one into the main story. Not worth the trouble — I've earned maybe $15 doing that. Where's the Slashdot Effect when you really need it?
    • You could pay $39 from BN, or $26.37 at Amazon.

      Or get it in PDF format from your favorite torrent site for $0.
    • Yes, but Slashdot's kickback deal is with BN, not Amazon!

    • I will, actually! (Score:3, Insightful)

      by pestie ( 141370 )
      You could pay $39 from BN, or $26.37 at Amazon.

      Yup, and I will, because Amazon sucks balls, what with their 1-click patents and "oh, by the way, all that stuff we promised before? Not so much!" privacy policies. Unlike some Slashdotters, I actually put my money where my mouth (or keyboard, if you prefer) is.

  • The practical SQL handbook (Score:5, Informative)

    by WindBourne ( 631190 ) on Wednesday May 10, 2006 @03:19PM (#15303238) Journal
    For those of you just starting out, try the the practical sql handbook. [amazon.com] I recently glanced through edition 3. I started at edition one years ago. It still remains an awesome book.
    • I have also found this a very useful book. Cheaper than other SQL books such as those from Oracle.
    • There is a fourth edition, with a different subtitle, 'Using SQL variants', it was published in 2001 (as opposed to the 3rd edition in 96). Also, the same authors have a second book, called Practical SQL: The Sequel. Haven't looked at it, but it might be worth looking at. Often with technology books, I find there are a decent number of introductory books, but a dearth of intermediate books. Economically it makes sense, but still frustrates me.

  • Joe Celko (Score:4, Informative)

    by ytsejam-ppc ( 134620 ) on Wednesday May 10, 2006 @03:23PM (#15303285)
    There are a few books by Joe Celko like SQL For Smarties that are in the position of honor on my bookshelf. If you're beyond "select * from emp" and want to really learn how to efficiently get things done in a relational database, Celko is your guy.

  • More Cooking with SQL (Score:5, Funny)

    by digitaldc ( 879047 ) * on Wednesday May 10, 2006 @03:31PM (#15303348)
    SELECT eggs (*) AS ingredients
    FROM Refrigerator
    GROUP BY NO.dozen
    INSERT INTO my_fryingpan (eggs1, butter1)
    UPDATE my_table SET toast = 'lightly browned'
    INSERT hole INTO toast
    MERGE eggs INTO toast
    USING (
    SELECT Eggs, Toast)
    CREATE Breakfast eggy_toast
    DELETE FROM my_table WHERE plate = 'N'

    • Re:More Cooking with SQL (Score:5, Funny)

      by UnknowingFool ( 672806 ) on Wednesday May 10, 2006 @03:51PM (#15303514)
      SELECT eggs (*) AS ingredients
      FROM Refrigerator
      GROUP BY NO.dozen
      INSERT INTO my_fryingpan (eggs1, butter1)
      UPDATE my_table SET toast = 'lightly browned'
      INSERT hole INTO toast
      MERGE eggs INTO toast
      USING (
      SELECT Eggs, Toast)
      CREATE Breakfast eggy_toast DELETE FROM my_table WHERE plate = 'N'

      You fool! You didn't specify nolock in that first select statement
      . . .
      . . .
      and I don't know if it's sadder that you wrote that SQL statement or I critiqued it

      • nolock? Is that like a MS SQL Server thing? I've never seen/used a nolock in Oracle or MySQL.

      • NoLock is a SQL Server and Sybase thing (and maybe InGres?). They force select queries to wait until any updates that would affect their results are done and committed, unless you tell it to read the uncertain data with a NoLock. What Oracle does is to give you the data as it was before any uncommitted transactions began.

    • INSERT INTO TRASH (
      SELECT eggs (*) AS ingredients
      FROM Refrigerator
      GROUP BY NO.dozen
      INSERT INTO my_fryingpan (eggs1, butter1)
      UPDATE my_table SET toast = 'lightly browned'
      INSERT hole INTO toast
      MERGE eggs INTO toast
      USING (
      SELECT Eggs, Toast)
      CREATE Breakfast eggy_toast
      DELETE FROM my_table WHERE plate = 'N'
      )

      SELECT beverage
      FROM Refrigerator
      WHERE Type = 'Beer'
      AND ROWNUM < 12;

    • Arrggh... why do you have to write this in the morning? You're making me hungry. I need to start having a real breakfast.

      CREATE TRIGGER AFTER UPDATE TO Mental_Picture IF NEW.image = 'Eggs & Bacon' AND NEW.sound = 'Sizzle' EXECUTE PROCEDURE Salivate(NOW());
  • SQL, you say? Never heard of it. Must be something new.

  • Speak more slowly, please... (Score:4, Funny)

    by TopShelf ( 92521 ) on Wednesday May 10, 2006 @03:38PM (#15303407) Homepage Journal
    One of the staples of corporate I.S. development is processing data, and increasingly these days that data lives in a relational database.

    Thank you, Mr. Obvious!
  • As with the author of this review, I really like the cookbook format. I've made heavy use of the MySQL cookbook, also from O'Reilly, by Paul DuBois. It is excelent, and just about everything that Simon P. Chappell says about the SQL Cookbook applies, except of course the focus on just MySQL. I'm glad to hear of the SQL cookbook because I'll be developing for postgresql as well as MySQL in the near future. Thanks.
    • The nice thing about the MySQL cookbook, is they demostrate database recipes from the major web programming CGI languages (not exhaustive, please don't flame) such as Python, Java, PHP and PERL (and shell scripts for that matter) which is a handy quick reference, when I forget how to do basic connectivity in the language I haven't used in a while... I don't need this for day-to-day programming in MySQL, but when the recipe fits, it is generally a huge time saver (like database validating of email or URL, or

  • sql (Score:3, Funny)

    by flogic42 ( 948616 ) on Wednesday May 10, 2006 @03:41PM (#15303434)
    Select * From SovietRussia Where Article AND ReadsYOU
  • What I have always found funny about SQL is that, while it's very 'old' (in software terms), and mature, and widely used, there is in fact no real standard. There never was. From the article:

    SQL variations ... While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard...

    Or, as Jim Starkey said: 'SQL isn't a standard but a theme'. For a book, it means list 5 different dialects. For regular developers (not database specialists) it means knowing only one dia

    • Just because nobody adheres to the standard strictly doesn't mean it isn't a standard. The standard itself is fully documented. Blame the developers, not ANSI.

      http://en.wikipedia.org/wiki/SQL [wikipedia.org]
      • What does a standard help if nobody follows it?

        > The standard itself is fully documented.

        On how many pages? Maybe the size of the standard is the problem.

        > Blame the developers, not ANSI.

        I'm not blaming the developers, as it's quite impossible to implement this huge standard. I don't blame the commitee. They did what such a group usually does: they held meetings. Lots of them.

        Still, there is no standard. Otherwise, how do you explain having to list 5 dialects?
      • Well, remember how a standard is created. It's put together by a committee of people all of whom have a vested interest in using the standards process to give them some advantage.

        The Microsoft rep says "hey, we have to support language extensions for row locking this way, because that's how our engine works." Oracle says "Well, let's just agree to call row locking an 'extension' but not define it because we do it differently." The DB2 guy says "Everybody needs to be able to DROP TABLE, so that should b

    • Re:SQL is a standard. Is it? (Score:5, Informative)

      by orthogonal ( 588627 ) on Wednesday May 10, 2006 @05:10PM (#15304145) Journal
      there is in fact no real standard

      Yes, there's a great deal of nonconformance and extensions.

      But, there is a standard, in fact, five: SQL-86, SQL-89, SQL-92, SQL:1999 and SQL:2003 (yeah, dashes replaced by colons, go figure). SQL:2003 can be purchased from ANSI or ISO, just like the C or C++ standards.

      Various sites [www.tar.hu] list product conformance to the standards.

      When I write SQL, I pretty rigorously stick to SQL-99, as that's likely to be supported by most vendors. If I need to deviate from that, I make sure I know how to replicate the vendor-specific code in SQL-99 (e.g, postgresql's inherits keyword -- it's useful for sub-typing, but it can be effected by using joins and views).

      If the non-standard code is DML (not DDL), I'll do my best to encapsulate it in a stored procedure or a view, and let the rest of my code call the encapsulated abstraction. This is just the same layering you'd do in any programming language to wall off platform-specific code. And just as you'd typedef in C or create abstract types in C++, you create UDTs in SQL too.

      Here's an example, using a useful testing date "function" that is cross-dialect, doesn't rely on any user-supplied function support, and can be incorporated into live code.

      -- postgresql
      CREATE DOMAIN datetime_type timestamp null;
      create view system_datetime as select 1 as id, now() as date_now ;

      -- MS SQL server
      create type datetime_type from datetime null;
      create view system_datetime as select 1 as id, getdate() as date_now ;


      -- code below is SQL dialect independent
      -- works on postgresql or MS SQL server

      -- code to use actual current date or fake date for testing purposes
      -- all DML that wants the current system timestamp should instead join on (or subselect) the view date_now
      -- this view always returns a single row

      create table test_date ( id int, test_date datetime_type ) ;
      insert test_date values ( 1, null ) ;

      create view date_now as
      select
      coalesce(
      ( select test_date from test_date where id = 1) ,
      ( select date_now from system_datetime where id = 1)
      ) as date_now ;

      -- note: for Sybase, replace coalesce with isnull

      -- see what invoices were due as of January 1:
      update test_date set test_date = '1/1/2006' ;

      select * from invoices a, date_now b
      where a.issue_date <= b.date_now
      and ( a.paid_date > b.date_now or a.paid_date is null )

      -- look at real invoices due as of now
      -- Query is the same, we just null out the fake date:
      update test_date set test_date = null ;

      select * from invoices a, date_now b
      where a.issue_date <= b.date_now
      and ( a.paid_date > b.date_now or a.paid_date is null )


      (And yes, you can hire me.)
    • There is a series of official SQL Standards, but the trouble is nobody any longer measures the extent to which database vendors conform to the official standards. Once upon a time NIST used to do this, but that part of their work was cancelled after some lobbying by the big database vendors: naturally they much prefer it if you use their dialect then you get locked in to their product. Who allowed this to happen? I think it was Bill Clinton.

  • Sample chapter (Score:4, Informative)

    by sootman ( 158191 ) on Wednesday May 10, 2006 @03:51PM (#15303513) Homepage Journal
    Chapter 11: Searching [oreilly.com]
    40 pages, 500k PDF
  • Great - yet another book that teaches me the "ins-and-outs" of the "Northwind" database. Seriously, when are publishers and authors going to come out with more original content? For instance - content for those of us that AREN'T designing HR or order entry apps?

    Ah well, it could be worse - it could be teaching me how to build another class registration system....
    • Most software(and all databases) are written to track something, whether it's people, inventory, money, weather etc...
    • Read the book. It has NOTHING to do with the Northwind database. It is not even specifically for Microsoft SQL; it further covers DB2, Oracle, MySQL and PostgreSQL. Every example is provided with the respective variations for each system, which as a way to familiarize with other RDBMS is a terrific learning oportunity. Seriously, the book is extraordinary. I thought I knew SQL (and I teach that stuff!) but there are always some more tricks to learn. I would give it a 9/10.
  • is a book full of sample queries.

    Half the time I am using PHP for something SQL could do in a couple simple commands.
    • I actually see this all the time. I do a lot of refactoring of code which I didn't write. I can't tell you how many times I have seen people "select * from table" and then use application logic to: determine number of records determine maximum from a field search for existence What a waste. If people would just learn a little more SQL than "select * from blah", the world would be a better place.

  • I am not sure how anyone can resolve the following found in the review - emphasis mine.

    The book is not for beginners and makes no efforts to teach any SQL. It concentrates purely on building on the base level of SQL knowledge that most programmers have. If you know the basics: Create, Read, Update and Delete (an unfortunate, yet memorable acronym) but rarely go beyond that, this book is for you. I know that I fall into this target market.

    Recipe one in chapter one, titled "Retrieving all Rows and Co

  • SQL is growing long in the tooth. It is time to replace it with something better that learns from lessons of the past. One proposal is Tutorial D, by Chris Date. I have also proposed "TQL" (or SMEQL due to a name comflict with another product) as a draft SQL replacement. Unlike Tutorial D, SMEQL uses a prefix notation for the main relational operaters. This makes it easier to extend by DBA's. If you don't like the out-of-the-box set of relational operators, you can define your own (using the existing ones).

  • SQL for Smarties (Score:3, Informative)

    by nrrd ( 4521 ) on Thursday May 11, 2006 @12:35PM (#15309465)
    SQL for smarties is the best advanced SQL book I've seen. Highly recommended for developers, and should be on the bookshelf of any DBA.

Slashdot Top Deals

BLISS is ignorance.

Close