Slashdot Mirror
SQL: Visual QuickStart Guide
What this book does right: The myth that it's more important for a programming book to be technically accurate than well written endures even though the opposite situation is true: A lucid explanation of a difficult concept or clever algorithm is more valuable than a bug-free implementation of same.
Consider Ken Henderson's The Guru's Guide to Transact-SQL , a book full of useful examples but so marred by the author's bloated style and disrespect for the language that I cringe every time I'm forced to read the text rather than simply lift a code snippet. Henderson even goes so far as to include an introductory section, titled "On Formality," about how he is going to split infinitives (even though their syntax is a burden for the brain to parse) and how he is going to use "data" in the singular sense (even though doing so can cause confusion) and how he considers "record," "row", and "tuple" to be interchangeable terms (even though they're not) and on and on. Readers would be aghast to find such self-exculpatory nonsense in the pages of Donald Knuth or Patrick Henry Winston. As for SQL: Visual QuickStart Guide, the author, a statistical programmer, presents each topic with a mathematician's sense of restraint and order. I've found few typos, no technical errors, and consistent use of technical terms.
Almost every aspect of SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP is covered. What distinguishes this book is that every ANSI SQL statement -- and there are hundreds of examples -- was tested on six separate DBMSes: Microsoft Access 2002, Microsoft SQL Server 2000, MySQL 4.0, PostgreSQL 7.1, Oracle 8i, and Oracle 9i (8i and 9i differ considerably in SQL-92 compliance). The examples in each section increase in depth and complexity, so you can stop reading once you've learned what you need to know. When an ANSI SQL statement doesn't work as-is on a particular DBMS, the author shows you how to fix it or offers workarounds (which is particularly useful for MySQL, whose adherence to the SQL standard is poor). These DBMS-specific fixes are given as separate "DBMS Tips" apart from the main body of text, so they don't interfere with the conceptual flow. This organization is especially useful for consultants who have difficulty keeping track of how each implementation deviates from the ANSI standard, and is superior to the alphabetical, segregated approach of O'Reilly's SQL in a Nutshell.
This book was shoehorned into the publisher's Visual QuickStart format, which, as I implied earlier, doesn't work well for procedural languages, but does work for a declarative language like SQL. A two-column layout separates examples from explanatory text. Red type highlights the relevant portions of code and results. The book is extensively cross-referenced and has an 18-page index. This layout also makes the book a good quick reference for experienced programmers. Almost all the examples use a single, sample database (so there's no need to memorize multiple schemas). The code listings and sample database are available for download.
The derivative nature of programming books makes it difficult to determine whether the author truly has mastered the material. Writing a book is a difficult task (perhaps even harder than programming) but, at the risk of exaggerating my point, I suspect that any determined, organized, and competent programmer could write any O'Reilly Nutshell book by paraphrasing existing materials. But if an author establishes his credentials early, the reader gains a sense of trust that remains throughout the entire book. In the introduction to this book, the author avoids an error that almost every other SQL-book author commits: that SQL stands for structured query language. According to ANSI (the only legitimate arbiter here), it stands for S-Q-L and nothing more. Fehily even offers an amusing explanation of why structured query language is the worst possible description of SQL. Throughout the book, the author also scatters bits of practical advice (job candidates are wise to say my-es-kyu-el, not my-sequel), beginner-friendly insights ("Although SELECT is powerful, it's not dangerous: You can't use it add, change, or delete data or database objects."), and advanced topics (optimization, concurrency control, logical data independence). It is these asides and respect for basic research, rather than swaths of expository text, that lend authority.
This book describes the effects of nulls in almost every aspect of SQL, including the interpretation of null-contaminated query results. You can no more be a competent SQL programmer without understanding nulls than you can be a competent LISP programmer without understanding recursion. Particularly useful are the discussion of three-value logic (true/false/unknown) and an algebraic derivation of how a null can cause a subquery to return an empty result unexpectedly (which has bitten me more than once).
As a wizened developer weary of hand-holding users and junior programmers through routine queries, I've found it mollifying to give away copies of this book (it's cheap) to reduce my interrupt stack.
What's Missing: Some missing items that I would have found useful:
- A glossary
- A quick syntax reference
- A chapter about statistics
- A chapter about advanced SQL "tricks"
- DB2 coverage
- Coverage of security commands (GRANT/REVOKE)
- An expanded query-optimization discussion
- Improved normalization examples
- A little more mathematical rigor in the set-theory discussion
"The myth that it's more important for a programming book to be technically accurate than well written endures even though the opposite situation is true"
So, you're saying... who cares if the code is wrong as long as you think you understand it? Personally, I'd rather have the right code.
Is the only SQL book worth reading. Period.
book that I won't be purchasing; I already have 5 or so $50 books that are currently serving as expensive doorstops. Anything you can get out of these books can easily be grokked or googled for through the web. -
This comment was randomly generated by a school of piranhas chewing on the PCB of a Microsoft Natural Keyboard.
Bookpool has it for $14.50
I like to keep beginner books around for reference. I think I'll keep my C J Date book on SQL. It has yet to let me down.
...make sure you pick up a cross-platform tool for working with SQL. SQL Server is particularily hard to use in a cross-platform way, but thanks to the jTDS and FreeTDS projects, we now have drivers.
Linux and Mac OS X users, unite! Or untie. Or something...
Javascript + Nintendo DSi = DSiCade
I liked their javascript and XML quickstart guides; they're basically ways of getting up to speed on something really quickly, and for relatively simple subjects (like Javascript, html, and xml) they might be all you need outside of web searches. The xml book had some really nice info about stylesheets and XSLT, for instance, and I use the stylesheet reference even when I'm just doing some HTML.
The books are cheap (twenty or thirty bucks) and small (easy to carry in a backpack). I think they're a really good value. Although, I wouldn't buy one for a more in-depth programming language, for the simpler stuff (html, xml) they're great.
Farewell! It's been a fine buncha years!
If an SQL book isn't easy to search I'm not buying it.
Omnis amans amens
... doesn't it end up being easier reading the documentation for the SQL products themselves? SQL is one of the few languages I would argue actually does *not* need a book. Reading a PHP/Perl book about any RDBMS using SQL would enlighten one as to the basic functionality; all further research could be done on the site of the RDBMS of choice. Yes, this would imply that they end up learning implementation specific SQL, but as long as one sticks to the "basic guidelines" found in the aforementioned books, one should be able to write fairly portable SQL, and fairly quickly.
Kind regards, Devon H. O'Dell
This is how I first cut my teeth in SQL, but I only developed 'real' skills when I started writing SAS code on a huge solaris system when I worked for a bank.
Amazon has it for $6.60 cheaper (30%) than bn!!!!
What about "SELECT MYSEQUENCE.NEXTVAL FROM DUAL" ?
" book that I won't be purchasing; I already have 5 or so $50 books that are currently serving as expensive doorstops. Anything you can get out of these books can easily be grokked or googled for through the web. -"
Scan them all, and do some OCR. Put them in this format on a DVD, and they can be carried were ever they're needed.
It's mostly complient to 92, and working towards 99. Check here for it's current "Bugs" status.
The only noted "bug" in it's SQL-92 complience is that whitespace gets stripped out at the end of VARCHAR types. Though a bug, there are other databases that do this as well. MySQL is working on becoming SQL-99 complient and have already started to look at (hypothetically) the changes that are to be made for SQL-2003 compliance.
"Time is long and life is short, so begin to live while you still can." -EV
I still can't believe we need a chapter on all the implementation differences. SQL-XX compliance seems to be as worthless a label as it was over ten years ago.
Barnes and Noble has it for $21.99 while Amazon has it for $15.39 (unless they have some kind of dinamic pricing and not everybody sees the same price).
Plus, there is also MySQL: Visual QuickStart Guide.
This paid my last vacation, it mi
It is difficult to determine, from the sample pages at Amazon, how advanced this book gets. I'm looking for a book that covers SQL 92 joins. Particularly the nuances of outer joins, exists statements and the like. We need a good SQL reference in the office that will show newbies the SQL92 way.
Anyone know of such a text?
One thing I was especially hoping to see in this book is the topic of Advanced Transactional Support in MySQL.
Want to improve your Karma? Instead of "Post Anonymously", try the "Post Humously" option.
I have the XML and the Web-Based UI ones. They're both very handy to have around, and easier to carry than toting O'Reilly books everywhere. They also cover more ground than the O'Reilly "Pocket Reference" series tend to.
-B
Ash and Hickory, straight-grained and true, make excellent bludgeons, dandy for the cudgeling of vegetarians.
If you read Oracle's docs, they'll tell you to use NVL(). If you read MSFT's docs, they'll tell you to use ISNULL(). Both databases support the standard SQL function COALESCE() that does the same thing and is portable. There are other similar examples. Only use the docs that came with your database if your database is PostgreSQL. Except for backslashes within string literals, they pretty much respect the standard.
MySQL's frequently bashed for being bad and/or slow at SQL standards support (subqueries, a redefined concatenation operator, handling floating point math incorreclty, etc)
How'd that post get "informative"?!?
You're better off buying a book targeted toward the platform you will be using. TransactSQL and PL/SQL have very different implementations of the same "standard". If you're using MySql or postgres just get some of their excellent manuals.
:( I guess that's why I get paid, doing what the bosses want.
I prefer PL/SQL but my job uses T-SQL
Here's a table that summarizes the standards compliance for a few database systems.
I'm not happy that this is required either but there you are.
The Internet is full. Go Away!!!
After you have learnt the logical basics, like joins, predicates, etc and the physical basics like partitions, etc etc. You are preety much set for basic SQL stuff.
At this point even if you read about advanced sorting, joining, complex inner queries , outer joins etc, you tend to forget them if they are not a solution to your immediate problems.
Once to get donw to the real Coding, and face challanging tasks which require complex queires, only then can you learn and remember the complex SQL stuff.
for the last time people, I am "frodo from middle eaRTH", not "middle eaST".
Many relational gurus suggest that nulls can and should be tossed from relational languages. "3-value logic" is far messier than Boolean logic (2-value). I personally think nulls are okay for numbers, but NOT for Booleans and strings. In strings, nulls are like a "poison pill" in which one null value can ruin the entire result.
I also think SQL should be replaced by some other relational language, but it is too entrenched right now. Relational languages could be a lot simpler IMO without losing power. SQL was sort of modeled on COBOL, which tries to be "English-like", complicating the language and the structure.
Table-ized A.I.
For beginners, this sounds like a good book to learn the basics of querying and allow them to begin honing their skills.
For professional (and soon-to-be professional) developers, learning STRONG SQL is a good idea. Many developers (esp. casual web devs) never take the time to get a good grasp on the language and write queries with a balance of effectiveness and efficiency.
Another good book to keep in mind (if you're serious about development) is "Database Design for Mere Mortals" by Hernandez and Getz. I'm sure it's been mentioned a few times here already.
If SQL doesn't stand for structured query language, what does it stand for? Or is this guy just full of sh*t? This also is mentioned in "The Free On-line Dictionary of Computing (09 FEB 02)" and is attributed to Allen G. Taylor. Can anyone clear this up?
O'reilly's SQL In A Nutshell is pretty good. It covers standard SQL and the differences between each database(Oracle, MySQL, Postgre SQL, MS SQL).
I find Fehily's approach to teaching different languages very unaffective and unorganized. I own his Python book, and have sampled many of his other works.
Hate to reply to my own comment, but I said that wrong. A binary search is O(lgn) not O(nlgn).
...interesting if true.
HERD, not heard
TransactSQL is the Sybase/MS implementation of SQL
;)
PL/SQL is the Oracle implementation of SQL
Stored procedures are independent of the language implentation, they just execute the SQL.
I like PL/SQL the best because I worked with Oracle extensively as my first Enterprise level database. Going from their implementation to the MS implementation was frustrating (for about a week, then it all sunk in, hell I'd probably be just as irritated if I had to go back to PL/SQL
Is doing queries like:
SELECT -1 as Sort_key, count( ID ), ''
FROM tbl_some_table
UNION ALL
SELECT 0, ID, Name
FROM tbl_some_table
Your guaranteed to get something, if only a zero in the first row.
You make one trip to the database, a cheap, yet noticable performance boost.
You know exactly how many rows are in the result.
You aren't too platform-specific.
You can chain a truckload of these (keeping a weather eye on the Sort_key values).
Are there any arguments against this strategy, beyond the usual "that's too advanced for the FNG coders we prefer to hire to maintain"?
Get thee glass eyes, and, like a scurvy politician, seem to see things thou dost not.--King Lear
Well, they do find "Kids In The Hall" funny. Bryant Gumbel must be hilarious to them.
Uh.... where did you get your DBAs? Do they understand that stored procedures offer performance benefits by reusing a stored execution plan? That stored procedures encourage code reuse just like function libraries in the procedural language of your choice? That by isolating a block of code away from being embedded in code it's much easier to put in the finnicky lock control statements that you generally don't want mucked about with by junior programmers? That by moving back to embedded SQL you are probably giving up control of locking mechanisms whatsoever? That you are quite likely moving sorting/selecting logic out to your embedding code, and away from the database engine that is much more optimized for these operations, offering advanced tools like cursors and temp tables?
If you're a programmer, you need to know how to specify what you want to feed into a stored procedure, and what you want to get out of it. If you can't write the proc yourself, you need to get someone to write it for you. Embedded SQL is a bad idea. You tie yourself to one table structure because you can't possibly change all the embedded code snippets. You miss out on debugging opportunities because you won't use PRINT statements in your sql. Etcetera, etcetera, etcetera. I don't want to do maintenance on my code either, but it IS my job after all.
So YOU! don't find this sort of book useful and YOU! won't need to buy it. That's great. Thanks for sharing that with us. Really.
How is the result of thisr y.ProductCategory, tblStage.Stage, tblStatus.Status"
"SELECT DISTINCT tblCalender.CalenderID AS ID, tblActivity.Activity AS
ActivityType, tblCalender.ActivityDesc, tblProductFamily.ProductFamily,
tblProductCatego
possibly going to be 3? 7 fields and 7 values unless i'm crazy.
Because it's clear what isnull does just from looking at it. Coalesce isn't. I've never ported systems from one database engine to another, only to other versions (not counting upsizing access). So I don't see how portable could actually offers a benefit for databases. People are too damned scared to move them even when they should.
Select certainly can insert data
I've had the (mis)fortune of having to teach courses in Unix using the Visual Quickstart Guides. (I've also checked out several of the other books in the series.) The problem with these books is that they teach by example with very little theoretical background. There's nothing wrong with teaching by example, but if it isn't set in a strong foundation, you end up with students that might have breadth of understanding, but no depth. The books themselves become useless at this point, as the examples are often so narrowly contrived that it's difficult to extrapolate them into more generic scenarios.
There's nothing worse than to see students pay good money for books that will be obsolete or of little use as soon as they finish a class. God knows I bought enough of them when I was in school. For this reason, I avoid the Visual Quickstart Guides and opt for textbooks with more depth of coverage.
The Ingres project used a database query language called QUEL, short for QUEry Language.
IBM developed a query language inspired by QUEL, which they jokingly named "sequel", rationalized as "Structure English (version of) QUEL".
That name was then shortened to SQL, still pronounced "sequel" by people who know what they're talking about, "ess queue ell" by people who don't.
So the name "SQL" was not really derived from "Structured Query Language"; rather, SQL was derived from SEQUEL, which was derived from QUEL. The S, Q and L did originally stand for "Structured", "Query" and "Language", but at different times during the evolution of the final terminology...
GCHQ Quantum Insert installed. If only our tongues were made of glass, how much more careful we would be when we speak
And they fold case on identifiers to lower instead of upper. Makes it more readable, but some tools don't handle having identifiers fold to lower case well.
A: Because you're an overly-literal anal retentive nerd!
I already have 5 or so $50 books that are currently serving as expensive doorstops.
Do what I'm doing and unload the books that you can spare on Amazon or Ebay. Ten bucks in my pocket beats the ten useless kilos on my shelf.
To suppliment the book, I have these two pages bookmarked: http://www.perldoc.com/perl5.6.1/lib/DBI.html and http://www.mysql.com/documentation/mysql/bychapter /
I still want to read Programming the Perl DBI as well, which looks like a useful book for what I want to do.
CfkRAp1041vYQVbFY1aIwA== RV/hBCLKKcSTP5UFK3kqsg==
six separate DBMSes: Microsoft Access 2002,
Somebody who starts a list of databases with Microsoft Access doesn't understand what a database is. Hint: Access is not a database, it's a program for maintaining and manipulating tables and generating reports from the data in them.
SQL:1999 has been defined for years and some of the DBMSs have actually implemented some of its features. Yes, SQL:1999 is big and perhaps bloated in comparison with SQL-92, but it's not that bad if you concentrate on the core parts. Some of the news in SQL:1999 are actually clarifications on stuff in SQL-92.
SQL:2003 will probably be agreed on this year.
- So I find it strange to use the more than a decade old SQL-92 as the platform for a book published in the year 2002.
Another thing: I'd say it's "ISO SQL" nowadays (or ISO/IEC SQL), not "ANSI SQL".
Apart from that, I've put the book on my Safari bookshelf and look forward to reading (at least parts of) it. Unfortunately, it seems that Safari's index of the book is currently lost.
I like the approach where you start by thinking about the standard way to do it, and then try to squeeze your design into a real-World product. It's too bad that the official standard isn't online; fortunately, it's possible to get something close to being official. When trying to set up an initial, standards compliant schema, it's nice to have an evaluation-installation of Mimer SQL to play with. I wish it were open source or at least had an official price tag.
I expect that the book could be relevant in connection with a page about SQL differences that I've started writing, after having had to port some SQL from PostgreSQL to MSSQL.
The other nice thing about coalesce() is that it takes an arbitrary number of arguments, and returns the first non-null, so you can do coalesce(a, b, c, d) rather than isnull(a, isnull(b, isnull(c, d))).
Vaguely related to the product, is what I meant to say up there.
Non gratis rodentus anus
I used a couple of the Visual Quick Start books as references several years ago. If they have kept up the quality, then they should be considered, by anyone after tech references.
Still how does anyone find enough info to fill a book on SQL?.
My Paintball Pics
If you're teaching Unix to a group of computer users, you might want to check out Think Unix by Jon Lasser.
I bought it shortly after I first installed Linux.
The book starts out on how to use man pages (and other forms of sparse documentation) to learn new commands. From there it teaches how to make aliases, use pipes, and redirect output. It eventually tackles vi, shell programming, changing your environment, and the X-Windows system.
It's a pretty good book which treats you like you know your way around a comuputer, but are new to *nices.
My father is a blogger.
Where I come from, 0 is also a number that is used, for instance, when you count your apples and you have no apples. Wait, you say, then use -1, well, that is what we use when we owe someone an apple. Whatever number you come up with, that is conceivably how many apples someone has or owes to someone else.
NULL is used to denote that the value is undefined. It is consistent for all data types.
In reply to the parent post (by MattRog) of your post, how on earth can a fully normalized database save you from not having to use NULLs? I'm sorry, I just can't follow your point.
I'd like to suggest that you follow the advice of at_kernel_99. If you don't want a value to be NULL, declare the column as not null. Problem solved.
Finally, to the parent post of at_kernel_99, if a NULL value can ruin a string, and you can't solve it by using at_kernel_99's excellent advice of declaring your string columns not null, why don't you use the SQL standard function called coalesce.
Instead of writing str1 + str2 and watch it be destroyed because one of them is null, write this
coalesce(str1, '') + coalesce(str2, '') --'' <-- this is actully two single quotes
Coalesce replaces the first argument with the second argument if it is null. In fact, you can give it any number of arguments and it will walk forward in the argument list until it finds a non null value.
coalesce(mayBeNull1, mayBeNull2, mayBeNull3, ' IGiveUp')
I apologize in advance for stepping on your toes. I had a bad day at work (where I program in SQL, among other things, for a living).
The Internet is full. Go Away!!!
And firmly believe the data layer needs to be at least two separate layers itself.
I know web apps are the name of the game. At the e-commerce dot com I used to work at we used all embedded SQL and it was a huge mess as processes locked with each other when the engine had too many simultaneous requests and none of the direct sql that was used bothered to specify WITH (NOLOCK) on the 98% of the code that was just doing reads. Maintenance went way down when we moved to stored procedures that had locking specified by the DBAs. There is often some overhead with connecting to the database too - if not for an actual connection then in instantiating variables to hold the results. If you need to query three separate times to get what you're lookign for, most web developers write three separate queries - its very rare to see embedded SQL that makes use of database variables as opposed to programming variables. So that helps too.
Stored procedures should be written to be atomic in nature, skipping the CRUD procs is what results in your 'you have to rewrite all the procs'. You need a group of low level procs that do insert, delete, etc that should be called by higher level scripting like procs. Then you just modify the lower level procs and the higher level ones are fine.
by using the system stored proc sp_executesql
Each layer can be subdivided. I divide the database layer into 3 layers itself: tables, CRUD, complicated procs. It's all about code re-use and maintenance. You write the dumb CRUD procs to handle your basic inserts, updates, etc. You call them from the more complicated procs. The role of the complicated procs is to accept a big chunk of data and format it for the tables. Lets say your middle tier has a nice xml document representing the contents of a shopping cart. Fine, the business logic is done. It passes that to a stored proc. The proc takes care of putting the pieces where they belong. This way the middle tier com object doesn't need to be recompiled (and the web server stopped for re-registration) when a data field changes length. Now that stopping/stopping the site thing doesn't apply to .Net, but we're not on .Net.
I divide the front end code into three layers too. The topmost layer being DHTML and client side script (what do you see in your screen), next HTML (what is sent to the browser), next ASP or your other server-side scripting language (what generates the other two and interacts with the middle tier).
MS doesn't go far enough because they're lazy. The front end shouldn't know anything about the database, definitely. The middle tier shouldn't know anything about the database except for calling stored procs. That lets you completely divorce the best way to handle the data from the best way to handle middle tier logic - your data structure should be driven by normalization and performance of the data, not by modeling what the middle tier does since that leads to poor database structure. Also by allowing 1 call to the database from the middle tier to generate an arbitrary amount of SQL processing, you cut down on unneccesary traffic. Now, you CAN write out the text of a stored proc as one big string and execute it from the middle tier - but it won't stay like that because programmers will tend to move things to programming language processing and away from database variables and cursors just as a rule of thumb.