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

 


Forgot your password?
Close
typodupeerror
×

The Art of SQL 225

Posted by samzenpus from the learn-all-about-it dept.
Graeme Williams writes "One difference between SQL and a conventional procedural programming language is that for SQL there's a bigger gap between what the code says and what the code does. The Art of SQL is the opposite of a cookbook – or rather it's about cooking rather than recipes. It's not a reference manual, although there's plenty to refer back to. It's an intermediate level book which assumes you know how to read and write SQL, and analyzes what SQL does and how it does it." Read on for Graeme's review.
The Art of SQL
author Stéphane Faroult with Peter Robson
pages xvi + 349
publisher O'Reilly Media
rating 9
reviewer Graeme Williams
ISBN 0-596-00894-5
summary An excellent way to improve your approach to SQL


I guess it's normal for an intermediate text to present a number of serious examples, the idea being that the code from an example can be applied to roughly similar problems with roughly similar solutions. I think Faroult's goal is both more abstract and more ambitious. He wants to expand your ability to navigate among and analyze alternative SQL statements with more confidence and over a larger range. This isn't so much a book about SQL as it is about thinking about SQL.

There's almost no chance that the SQL examples in the book will be directly applied to a real problem. The examples are relevant at one remove: What does thinking about this example tell me about thinking about my current problem? So the book doesn't come with downloadable samples. There's no point.

The first few chapters of the book lay a foundation for the rest. As each brick in this foundation is placed, it sometimes feels as though it's placed firmly on your head. Think about indexes ... whack! Think about join conditions ... whack! These chapters have very few examples – the goal is to force you to think through queries from first principles. It's more effective (and less painful) than it sounds.

These introductory chapters cover how a query is constructed and executed, including how a query optimizer uses the information which is available to it. Faroult discusses the costs and benefits of indexes, and the interaction of physical layout with indexes, grouping, row ordering and partitioning. He also explains the difference between a purely relational query and one with non-relational parts, and how such a query can be analyzed in layers. Chapter 4 is available on the book's web page. It will give you a good idea of the style of the book, but not of the level of SQL discussed – the longest example in the chapter is just 15 lines.

Chapter 6 presents and analyzes nine SQL patterns, from small result sets taken from a few tables, to large result sets taken from many tables. The chapter falls roughly in the middle of the book, and feels like its heart. Prior chapters have built up to this one, and subsequent chapters are elaborations on particular topics. The theme of the book, to the extent that it has one, is that details matter. Different SQL statements can be used to produce the same result, but their performance will be different depending on details of the data and database. A change to the database structure, such as adding an index, might improve performance in one set of circumstances, but make it worse in another. The case analysis in this chapter will make you more sensitive to details in query design and execution.

The authors almost never mention particular database products. Their justification is that any absolute statement would be invalidated by the next release, or even a different hardware configuration, and anyway, that's not the business they're in. But sometimes this can go too far. The phrase "A clever optimizer ... will be able to" is too hypothetical by half. Is this an existing hypothetical query optimizer, or a vision of a future optimizer? Or the optimizer of one hypothetical database product and not of another? I suspect that Faroult knows and is simply being coy. It's just unhelpful not to tell us what existing databases will do, even if depends on the release or the hardware.

Faroult does this because he's not much interested in telling you what actually happens when a particular SQL statement is executed by a particular database. If the authors wanted a cute title for the book, I'm surprised they passed over The Zen of SQL Maintenance. When you look at an SQL statement, Faroult wants you to see what other SQL statements would do under other circumstances. He literally wants you to see the possibilities.

The second half of the book continues the analysis of chapter 6 into special cases, such as OLAP and large volumes of data, monitoring and resolving performance issues, and debugging problematic SQL.

Chapter 7 discusses tree-structured data, like an employee table with a column for the employee's manager. Faroult likes his own solution best, but presents an alternative approach by Joe Celko clearly enough for you to explore that as well.

Chapter 8 includes a series of examples of SQL and PHP. For anyone like me who spends more time in various programming languages than in SQL, this chapter is a small gem. It nicely illuminates the care needed in deciding what happens in code and what happens in SQL.

Chapter 9 addresses locking and concurrency, as it applies to both physical and logical parallelism. Transactions are included, but the discussion is just one part of a 20-page chapter and seems thin.

The Art of SQL is very clearly written. Whether it is "easy" will depend on how comfortable you are with SQL. This book is targeted at (page xi) "developers with significant (one year or, preferably, more) experience of development with an SQL database", their managers and software architects. I have months of experience spread over a decade or more, so I'm nominally outside the target audience. I found the SQL examples and discussion clear once I had a chance to let them sink in. If you're working with SQL regularly, they'll be perfectly clear.

The graphs let down the otherwise high quality of the book. For example, Figure 5-3 shows a rate (higher is better) but the legend says "Relative cost" (higher is worse). Figures 9-1 through 9-3 on facing pages 228 and 229 show response time histograms for three different query rates but don't show what the rates are. The x-axis of Figure 10-1 seems to be calendar time, but it's decorated with a stop watch icon. And as a representative of rapidly aging boomers with rapidly deteriorating eyesight, could I beg book designers not to put figure legends in a smaller font than the text of the book? Diagrams should be simple and clear, not something to puzzle over.

This is a book to conjure with, but it's not a book for everyone. Some people may find it too abstract, with too much discussion of too few examples. If you're completely new to SQL, the book will be hard going. If you have very many years of experience with SQL, it's just possible that you won't find anything new in the book, although I expect you'll find a lot to think about. For anyone in between, The Art of SQL is a excellent way to improve the way you attack problems in database and query design.

You can purchase The Art of SQL 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.

The Art of SQL More

The Art of SQL

Comments Filter:

  • BN vs Amazon (Score:3, Informative)

    by beavis88 ( 25983 ) on Wednesday June 07, 2006 @03:32PM (#15489546)
    I know Amazon has software patents and all, but this (and just about every other book I see reviewed here) is ~20% cheaper at Amazon than it is at BN...

    • Re: (Score:2, Informative)

      by account_deleted ( 4530225 )
      Comment removed based on user account deletion
      • Well, I did a search at Amazon for this book by ISBN and got this [amazon.com].

        I did the same search at B&N.com and got the page for this book. Can't buy what I can't find.

    • Re:BN vs Amazon (Score:3, Insightful)

      by fm6 ( 162816 )
      This isn't about software patents, or about prices. This is about who gives Slashdot the best deal for steering traffic to their site.

    • bookpool (Score:5, Informative)

      by stoolpigeon ( 454276 ) * <bittercode@gmail> on Wednesday June 07, 2006 @03:59PM (#15489725) Homepage Journal
      bookpool has it cheaper [bookpool.com] than amazon.

    • Cheaper isn't everything (Score:5, Insightful)

      by PCM2 ( 4486 ) on Wednesday June 07, 2006 @04:18PM (#15489885) Homepage
      In fact, if you have access to a local, independently-owned bookseller in your area, you should be buying your books there instead of online.

      Stacey's Books [staceys.com] in San Francisco doesn't give me Amazon's 34 percent discount -- in fact, it gives me 10 percent -- but it is a wonderful resource and not one I'd like to see disappear.

      That's not hyperbole either. This year we've seen two classic, quality Bay Area bookstores close their doors: Cody's on Telegraph Avenue in Berkeley and A Clean, Well-Lighted Place for Books on Van Ness in San Francisco. These were not holes in the wall; they were spacious, carried a lot of stock and had served their communities well for years. (And believe me, the Bay Area in general buys a lot of books.)

      The reality is that the book market is changing. Superstores like Borders and Barnes and Noble have a lot to do with it, and so does Amazon. Another factor is the overall decline in book sales to the American public. People walk into Borders to buy DVDs of Friends and they pick up a paperback of Harry Potter at the same time. That's not the model I want my booksellers to be based around; I want to support local businesses that understand their communities and are dedicated to selling books.

      This is not to knock Amazon, or Borders or B&N for that matter; in communities where those are the only option, it's better to have someplace to buy books than no place at all. I still buy plenty of stuff at Amazon. But for books, I vote with my wallet.

      • In fact, if you have access to a local, independently-owned bookseller in your area, you should be buying your books there instead of online.

        Only if you also stop by and mention to the manager that you're only buying your books there so that they don't go out of business, and that most people who buy books won't do the same, and that you might not in the future.

        Businesses that require this sort of support in order to stay in business have a big problem: they're not viable businesses. What needs to ha

  • art (Score:4, Insightful)

    by Lord Ender ( 156273 ) on Wednesday June 07, 2006 @03:35PM (#15489570) Homepage
    If you think SQL is an "art," you are a hack. Designing proper databases and the SQL to use them optimally falls under the domain of science/engineering. 95% of developers see relational databases simply as a means for a persistent data store, but that's not what it was designed to do. If you don't know engineering (what you do when designing functional systems*) from art (painting pictures, etc) you should have gone to a better college.

    See this page [wikipedia.org] for a start on the science of databases.

    *Yes, I know creativity is usually involved when designing things. That doesn't make it art.

    • Re:art (Score:3, Funny)

      by Mindwarp ( 15738 )
      A slashdotter who did not build his own computer is like a jedi who did not build his own lightsaber.

      Imaginary?

    • Re:art (Score:2, Insightful)

      by gowen ( 141411 )
      There's a difference between something being an art and something being art.

      Science, yes even computer science, and mathematics are particularly susceptible to elegance, and science (and engineering) built on elegant designs, based on concrete principles, have fewer points of failure, and can often be conceptually simpler, and algorithmically faster. Art is simply the application of insight, and this book (seems) to show us how insight into the problem space and the mathematics can achieve elegant (and eff

    • Art is about creativity, not rote coding (Score:5, Insightful)

      by Graboid ( 975267 ) on Wednesday June 07, 2006 @03:55PM (#15489704)
      Ahhh - but the best scientists are artists as well. (In fact, scientists and mathematicians often have more in common with artists than engineers).

      Sure, the mechanics of programming is rather dull and boring, but large scale system design often requires considerable creativity that is much better done by people not constrained by artificially perceived IT limitations.

      Coding J2EE isn't an art, but designing/building a massive neural net or complex, distributed game/simulation is. MySpace, Google, eBay, etc weren't concieved by 'classic' engineers, but, rather, by creative people who understood how technology can enable new paradigms.

    • Designing proper databases and the SQL to use them optimally falls under the domain of science/engineering.
      The process of doing science isn't as clear cut as you're trying to make it out to be. You're thinking of science as the nice, clean end product that's been checked and written in textbooks. Getting those results can most certainly be an "art" (that is something learned by experience, and not easily definable). Working with something as complex as a database is similar in that the performance you ge

    • Re:art (Score:2)

      by Ruie ( 30480 )
      *Yes, I know creativity is usually involved when designing things. That doesn't make it art.

      But adding cavities to design of nitrogen chilled supercomputer so you can cool things there does.

    • I think it could be an art-- but today it is mostly automatic optimizers that tune databases.
      Some of the tunings are counter-intuitive.

      Clearly any complex setup with multiple tradeoffs and ill-defined specs requires a bit of an artist to set up. Your wiki link was nice, but many businesses don't budget enough money to do it that rigorously.

  • Great Statement (Score:3, Funny)

    by lbmouse ( 473316 ) on Wednesday June 07, 2006 @03:39PM (#15489587) Homepage
    "for SQL there's a bigger gap between what the code says and what the code does"

    I couldn't agree more. Sometimes while working in SQL I really wish I had a time machine [wikipedia.org] and a rubber hose.

  • Bummer, trees (Score:4, Insightful)

    by plopez ( 54068 ) on Wednesday June 07, 2006 @03:39PM (#15489595) Journal
    Chapter 7 discusses tree-structured data

    Looks like no discussion of many-to-many relationships. This would make any book on databases and sql queries of limited value, not much more than a beginner book.

    Trees are of limited value, they only exist in special circumstances. If you stick to tree structured data relations then you will almost always have to do wierd hacks that may threaten data integrity.

    While many-to-many *seems* harder, as a data model M:M is often a much better practicle solution. As well as modeling the reality of the situation in a much more accurrate manner.

    My $.02

    • Trees are of limited value, they only exist in special circumstances.

      Yes, in fact, I'm struggling to remember the last time I saw a tree structure, as I post one of many replies to your comment, which in turn is one reply of many to the article.

      Trees crop up in all kinds of different places, and the approaches most newbies tend to take are awful. That warrants their inclusion in a book like this. I agree that many-to-many relationships are important, but that's no reason to neglect tree structure

      • But are they a natural artifact of a conversation or because people just can't wrap there heads around a different structure becase that is all they were ever taught.

        I for one am very frustrated with constant drilling down through file structures, conversation threads, posting comments which associate across different parent posts conceptualy but I have to post twice if the point is relevent to 2 source posts.

        A cloud view based on meta data and linking would be nice. Almost like, date I say it, relationship

        • But are they a natural artifact of a conversation or because people just can't wrap there heads around a different structure becase that is all they were ever taught.

          I believe that they aren't a natural artifact of a conversation, but nobody has come up with a decent interface for multi-parent comments. There have been attempts, but they all end up being too confusing for very little gain. Comment trees are the optimum balance between complexity and usability.

      • Bills of Materials lend themselves perfectly well to tree structures.

    • Re:Bummer, trees (Score:3, Informative)

      by eh2o ( 471262 )
      FYI this book does mention many-to-many relations a few times, including its implications for multiple indexing and in the chapter on trees (since graphs are a generalization of trees where a node may have multiple parents). In the book there are 4 different data structures proposed for trees.

    • Re:Bummer, trees (Score:3, Informative)

      by sqlgeek ( 168433 )
      Didn't look at the sample chapter, did you? It deals with a many-many relation several times in varying contexts.
    • Storing things as adjacency lists (which, obviously, is an M2M table where the node properties live in their own normalized table) tends to be faster in the long run for all but the largest and most active trees.

      Nested sets are cool, and I've implemented them (in MySQL 4.1 no less), but at the end of the day, traversing a graph happens far more often and more usefully.

      This seems to be where the CS majors separate from the rest of the crowd. Point out that they ought to know how to do this unless they faile

    • Re:Bummer, trees (Score:3, Insightful)

      by dbdweeb ( 598548 )

      You ARE just being facetious right?

      Tree structures are everywhere in computing... Like file systems... Like the DOM for every web page you have ever looked at is represented by a tree structure.

      As regards the coverage of M:M... Another post pointed out that it IS covered in the book.

      As regards the usage of M:M... That's just for high level conceptual modeling right? Surely you are not actually going to implement that way but will instead insert an intersect object AKA associative table, right? Data

    • Re:Bummer, trees (Score:3, Insightful)

      by abirdman ( 557790 )

      I basically agree with your post, but you're overlooking a significant part of the audience for this book -- people who didn't design the data structures they're querying. You're absolutely correct that tree structured data is a terrible hack in most databases, but if you're writing reports, or writing code to update legacy data, or the data was converted from another system altogether (perhaps written in Cobol or Quick Basic or some horrible old language where hierarchical data was a way to wring out some

  • One year of SQL is significant experience? (Score:3, Insightful)

    by Osty ( 16825 ) on Wednesday June 07, 2006 @03:44PM (#15489635)

    Perhaps that's what's wrong with database development these days (just check out The Daily WTF [thedailywtf.com], as it seems they have a SQL example every other day). When a single year of experience is considered "significant" and "experienced", it's no wonder there are so many crap DBAs out there. We look for people with 5+ years of C# experience (ha! Good luck finding someone with more than 5 years experience ...) for intermediate-level developer positions. There's no way someone with only a year of SQL experience would qualify for an intermediate-level DBA position.

    Just as background, I've been doing development on SQL Server for 6 years now (from SQL 7 to SQL 2005). I'm still learning, still finding ways to improve my code's cleanliness and performance, still finding new things I can do in SQL. For example, SQL 2005 finally has CTEs, making it only the second database to implement that ANSI SQL99 standard. CTEs make it very easy to do things that were painfully hard before, like walking a tree or implementing a recursive algorithm over sets of data.

    After my fourth year of working with SQL, I'd have been willing to say I had "significant" experience with SQL. Four years is arbitrary -- it really depends on how much you work with it day to day. Someone may have "significant" experience after only two years, while someone else may not be significantly experienced until he's worked with SQL for eight years. If you had to put a number of years on what would constitute significant experience, I'd err on the safe side and go with three or four years. Certainly not just one year.

    • In most areas, 3-5 years is the minimum.

      This goes for trades as well as engineering. The EIT paradigm used in engineering requires logging hours under a licensed engineer for what amounts to a 3 r more year apprenticeship.
      Most trades programs do not allow you to be called 'Master' until after apprenticeship and being a journeyman for a few years.

      The upshot being that most plumbers are probably better trained than IT people.

      And the posts on this board prove it. Also, as bad as the review was, it gave me enou
    • FWIW, I have 10 years of experience with Oracle and MS SQL and I still don't consider myself an expert. I wrote reporting queries against one of the largest Oracle databases in the US and our truely expert DBAs were always teaching me something new.

      Some people talk to me about databases like I'm Yoda. But I label myself as "very experienced." When performing the right kind of work I'd say it takes at least 4 or 5 years to gain significant experience.
    • Are we talking parent-child hierarchy tables? If so, Oracle's had statements to take care of that for a long time, since 1998 or so. Perhaps not ANSI standard, but they get the job done.

      • Re:CTEs (Score:2, Informative)

        by Osty ( 16825 )

        Are we talking parent-child hierarchy tables? If so, Oracle's had statements to take care of that for a long time, since 1998 or so. Perhaps not ANSI standard, but they get the job done.

        No, I'm talking about Common [sqlservercentral.com] Table [ibm.com] Expressions [ianywhere.com] (okay, so I was slightly wrong about implementation of CTEs -- apparently other products have implemented the standard, but DB2 and SQL Server 2005 are the only "Big Boy" engines with them). CTEs aren't so much about implementing a hierarchy as they are about doing recursiv

        • Re:CTEs (Score:3, Informative)

          by BitterAndDrunk ( 799378 )
          TY for the info.
          FWIW, the parent-child solve in Oracle is set based. CONNECT BY I think is the syntax, but it's been almost 7 years since I was working BOM hierarchies that utilized it heavily.
    • so you only look for people who ahve been coding in C# since 2001? I guess you only look a resume of MS emploee's who where on the project thatc reated it...

      I assume you mean MSSQL7-MSSQL2005. Which is a horrible database to learn on. I have used almost every version, and Oracle is a lot better. Of course Informix could out perform both of them, but I digress.

  • Developers and SQL (Score:5, Insightful)

    by DebianDog ( 472284 ) <dan.danslagle@com> on Wednesday June 07, 2006 @03:45PM (#15489641) Homepage
    As a DBA, if developers would read... oh.... I dunno... just Chapter 1... the basics of SQL... of this book... any SQL book really AND understand "the basics"... My job would be 100 times easier!

    I spend much of my time explaining why a 5 page SQL statement "that takes a long time" is NOT A DATABASE PROBLEM!
    /rant

    • Why is it not a database problem? Wasn't the whole point of SQL that you could state the result you wanted in a declarative manner and let the database figure out how to get you your result fastest? If it can't, why the hell do we need to write SQL, as I do now how to traverse an indexed btree fast using a simple loop or recursive algorithm? What's that layer doing there apart from being a pain in the neck?

      • Why is it not a database problem?

        Because it's a 5 page SQL query.

        ...how to get you your result fastest?

        Sometimes, fastest still takes a long time.

      • Of course, your B-tree traversal code is going to understand issues of concurrency, replication, locking, optimisation, scalability, failure, error recovery, robustness in the face of change of the database layout, and be maintainable by everyone else who comes after you.

        Or maybe you could leave that to the DBMS which already understands these things.
    • and as a developer, I will counter, that, in my experience, the reason there was a 5-page sql query in the first place was because the DBA had very restrictive rules about normalization (not realizing that denormalization is a common, industry-standard way to improve database performance). Why did my query (this was at my last job, which had the dba's from hell) have 16 joins in it? Because I had to split stuff up across 6 tables that I knew I would always be accessing at the same time anyway...
      • For future reference, ask for a view with the 6 tables joined.

        Then you can have build the simple query you want and any issues with performance/maintenance will be in in the DBA hands.

        Unless you are playing with data-warehouse size data or are using a toy *cough*MySQL*cough* a view like this shouldn't be a problem.
        • fine, but unless you are using a materlialized view (which is, in most cases, only for read), that doesn't address the fundamental problem of joining and reading/updating/deleting rows from six tables is slower (in most, but not all cases) than putting it in one.
      • Normalisation is a tool to protect the integrity of the database. It is concerned with the logical model of the data. It should have nothing to do with the underlying physical representation of the data (ask a competent DBA). Denormalisation is a dangerous hack.

  • A useful review - Thanks! (Score:2, Informative)

    by Anonymous Coward
    Many so-called book reviews on Slashdot fail to review the book. Instead, they simply state what each chapter covers. This review is actually useful. It describes the book's target audience, gives a sense of what the book does and doesn't contain, and helps me understand whether the book would be useful to me. Thanks!

  • SQL says what to do (Score:5, Insightful)

    by booch ( 4157 ) <slashdot2010@cra ... m ['k.c' in gap]> on Wednesday June 07, 2006 @03:54PM (#15489695) Homepage
    there's a bigger gap between what the code says and what the code does

    That's stated incorrectly. With SQL, the code says what to do, but it does not say how to do it. That's the difference between "normal" procedural code and languages like SQL.
    • And like with any declarative language, saying what to do is followed by wiggling chicken bones over the database cache and indexing at random places to actually get it do to what you said it should do within your lifetime. Or you can buy this book and figure out the subtleties of finding out how to say what you want with sufficient hints to how you want to get it done for the dumb machine not to choke on it. What was again the advantage of a declarative language?

  • SQL Books (Score:3, Insightful)

    by Municipa ( 99320 ) on Wednesday June 07, 2006 @03:56PM (#15489707)
    Pretty much every book on SQL I've seen only gives you obvious examples and covers the most simple uses. Every project I've worked on (for about 10 years) where there is pre-existing SQL written, almost all of it is written inefficiently. I'm not sure this book explains this kind of thing. But I've found 99%+ of the time you don't need to use a cursor, and it's almost always slower.

    SQL can do a lot more than most programmers ever try to do with it. There are a lot of clever tricks you can use exploiting its set based nature. The only place I've seen clever solutions beyond simple insert/delete/update statements is some of the trade magazines; the one for MS SQL Server sometimes has some very neat examples. These trade magazines have examples and ideas presented using the SQL language of a particular database, but it's almost always portable wihtout much work. I consider myself pretty good at SQL and even I find it's hard to learn more to get to the point where I can design clever SQL more frequently. Anyone else find that too?

    Another thing I've noticed is on some open source projects (and perhaps some closed source ones), particularly web based ones, there is displayed at the bottom the number of database queries used to generate the page. They are often 10 or more, which almost always seems ridiculous. I think there just aren't all that many people out there who understands what SQL can do, how it's different than procedural languages and how to use it beyond a simplistic straight forward approach. Hopefully this book helps explain that - I'll probably browse a bit the next time I'm in a book store.
  • One difference between SQL and a conventional procedural programming language is that for SQL there's a bigger gap between what the code says and what the code does.

    What? My SQL code tends to do exactly what the code says it will, are you trying to say that it's a high level language or am I missing something here?
  • ... trying to read the sample chapter screws up the tab holding that page in firefox (1.5.0.4) - scrolling no longer works for that tab until you either close it or go to another url, etc. It's too bad, it sounds like a good read.

  • This is an excellent book (Score:3, Informative)

    by tcopeland ( 32225 ) * <tom AT thomasleecopeland DOT com> on Wednesday June 07, 2006 @03:59PM (#15489720) Homepage
    I'm slowly working my way through it; it's a great book on a number of levels. The writing itself is very nice, with a real personality showing through and not just the usual dry technical flavor. The illustrations are done in a nifty "drawing" style that looks good and portrays the data well. The technical insights are very helpful; after reading what I've moved through so far I've rewritten some of my Rails code [blogs.com] to be more efficient.

    I highly recommend this book; the $40 you'll spend on it will be repaid the first time you delete a swath of Java looping code and replace it with an additional subquery. If I can do half as well on my next book [generating...javacc.com] I'll consider it a job well done.

  • sql vs. procedural (Score:2, Interesting)

    by JCOTTON ( 775912 )
    Now that the subject has been raised, my 2 cents.
    I have found (and who can disagree (just trolling)) that at least half of the production databases that I have come across hare not normalized. Go figgure.

    Anyway, this being the case, I have found that SQL is poor in handling a non-normalized table/database. (cant really call a non-normalized table as a database can we? (nuther troll))

    For example. We keep a complete record for each person for each pay period. Even inactives.

    I am asked to give a li



    • Actually, they don't, in the sense that there is no one single built-in command to handle this case. Just like in SQL, you (or someone else) has to write the function that performs a 'select distinct' equivalent.

      Unless you're using a targeted-product (one built specifically for your data needs), nothing you do will be handled natively in any language. You can build this functionality by using correct SQL or writing the appropriate functions in a procedural language.

      And why don't you consider 'select distinc

    • Re:sql vs. procedural (Score:4, Interesting)

      by cruachan ( 113813 ) on Wednesday June 07, 2006 @05:22PM (#15490338)
      Firstly most production databases contain some denormalization. Indeed the art of designing a real database is knowing where and when to denormalize data. How much denormalization is required is dependent upon the database, access paths and application usage and is rarely more than a few fields or a table or two. Nevertheless real production databases that have been correctly denormalized often run orders of magnitude faster than those that rigidly stick to 3rd normalization throughout.

      Secondly what you are asking for is generally straightforward in any real dialect of SQL. Select distinct works fine, as do various scenarios with subselects and group by / having clauses (having is the most overlooked of the standard SQL clauses and it's use generally signifies you are using code written by someone who knows what they are doing).

      However if you have a good dbms to hand that implements user defined functions then usually the best way if to create a function that returns the uid of the record from the multiple recordset you require (i.e. last payroll record for employee x) and use that in the where clause.

      OTOH if you are stuck with MySQL then the first step you have to take is upgrade to Postgres :-)

  • procedural programming (Score:4, Insightful)

    by jbgreer ( 4245 ) on Wednesday June 07, 2006 @04:23PM (#15489925) Journal
    "One difference between SQL and a conventional procedural programming language is that for SQL there's a bigger gap between what the code says and what the code does."

    Well, certainly one difference between SQL and a conventional procedural programming language is that SQL isn't procedural, it's declarative. One describes the data a query such produce, rather than state a set of steps necessary to achieve a desired result.

    jbgreer
  • The reviewer managed to miss something that's pretty important: the authors are totally infatuated with Sun Tzu's The Art of War, even to the point of copying that book's chapter titles [wikipedia.org]. Which is evidence either that they're educated people building on age-old wisdom — or they're half-educated dweebs copying a book that's faddish right now.
    • It's important that in a book entitled "The Art of SQL" they followed the organizational structure of "The Art of War".

      Well, it really isn't at all important should it be a surprise.

      And The Art of War faddish? The book is over 2500 years old, influenced Emperor Napoleon, General Patton, BH Liddell Hart (who in turn influenced the creation of the WWII German military strategies), General McArthur, etc, and has sold well to non-military types for at least 20 years. I think the world could use a few more boo
  • One difference between SQL and a conventional procedural programming language is that for SQL there's a bigger gap between what the code says and what the code does

    Or more importantly SQL is not a procedural programming language at all. Please don't try to compare the two together at all, it just leads to misconceptions about what SQL is and how it works.

  • the sample chapter is promising (Score:3, Informative)

    by sqlgeek ( 168433 ) on Wednesday June 07, 2006 @04:31PM (#15489979)
    I like the look of this book quite a bit, judging soley from the sample chapter. It talks in a straight-forward manner about the factors that determine how a database goes about it's job and how you can make that job easier or harder. If the rest of the book plays out similarly then thorough understanding of this book as well as Tom Kyte's would make for a programmer I'd love to hire.
  • If you are going to go with the cooking/food reference then I do not think SQL is like cooking. SQL is like ordering at a restuarant, where the restuarant is your DBMS. It's like programming in prolog. You don't tell prolog what to do; you tell it what you want. Just a thought.

  • There's a bigger CRAP (Score:2, Insightful)

    by dbdweeb ( 598548 )

    Regarding > "there's a bigger gap between what the code says and what the code does." I think that's a typo. It should read...

    there's bigger CRAP between what the code says and what the code does.

    There's a lot of code in the RDBMS and normally you shouldn't have to delve into the RDBMS' source... But you should know what it does and how to use it.

    I was once on a project where a DUHveloper needed to perform an unnatural sort on a key column. He needed to display the query results where certain rows

Slashdot Top Deals

He has not acquired a fortune; the fortune has acquired him. -- Bion

Close