Slashdot Mirror
SQL Cookbook
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:
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.
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.
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.
120 characters for a sig? That's bloody useless.
Shit, I've been calling it squirrel all this time!
"Da ist ein Technölüst in mein Unterpanten!"
Unix MEN say S.Q.L.
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.
A post a day keeps productivity at bay.
For those of you just starting out, try the the practical sql handbook. I recently glanced through edition 3. I started at edition one years ago. It still remains an awesome book.
I prefer the "u" in honour as it seems to be missing these days.
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.
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'
He who knows best knows how little he knows. - Thomas Jefferson
Don't forget a pinch of marquee tags.
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.
SQL, you say? Never heard of it. Must be something new.
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!
Stop by my site where I write about ERP systems & more
SQL started out being called SEQUEL (and pronounced as such) until it was changed to SQL due to trademark issues. IMHO, the historical pronunciation is a valid precedent. See the History section of the SQL Wikipedia entry.
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.
-- Mein Systemadminstrator hat einen großen schwarzen Moustache.
Select * From SovietRussia Where Article AND ReadsYOU
Check out my women's designer clothing store.
Wow am I glad this book is finally out.
Now if I happen to forget proper SELECT or INSERT syntax I can just fall back to idiotic acronyms to help me remember.
If SQL is pronounced 'ess cue ell', is MySQL pronounced 'emm why ess cue ell'?
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 dialect really well. For an application it means, running only with one database (mostly). It would be really cool the industry could get together and define a 'real' standard. Could be a subset of SQL (http://ldbc.sf.net/) or a new language (http://newsql.sf.net/). Things would get simpler then.
(Side note: LDBC and NewSQL are both projects I started, but interest was quite low; currently I'm working on a new database engine http://www.h2database.com/ where I try to be compatible as much as possible with existing databases)
Or is there some other solution? I don't think that that O/R mapping tools will solve the problem completely, as there is always the need interactive database queries. Maybe the Microsoft extension to C# (forgot the name) could be a solution? Other ideas?
by Joe Celko?
Chapter 11: Searching
40 pages, 500k PDF
Dear Slashdot: next time you want to mess with the site, add a rich-text editor for comments.
Chris Date has stated in many of his books on RDMS theory and practice, several of which were published before many Slashdotters were born, that SQL is pronounced 'sequel'. Good enough for me.
Actually that is pronounced T3h-5ux-0r5.
Smaller and more efficient, but doesn't quite the performance of its derivatives. If you've got a high volume/availibility site then you have to use h1database as it won't get "stuck"
When I have a kid, I want to put him in one of those strollers for twins and then run around the mall looking frantic.
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....
Because all of the flavors have a sample "Northwind" db.
It's not about showing you how to create a HR or order entry app, its about showing you how to go about basic tasks that are easily explainable problems under those types of schemas.
When I have a kid, I want to put him in one of those strollers for twins and then run around the mall looking frantic.
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 can think of a far more unfortunate acronym...
SCRUM. I think it has something to do with smegma but I'm not sure.
...is WRONG and is like shouting to everyone that your IQ is low and you're lazy and illiterate. It is properly pronounced like "Ess Queue Ell", because SEQUEL is an entirely different ancient IBM product altogether.
You know how stupid it sounds when someone pronounces Linux as "Line-uhx"? Well that's exactly how smart you sound like when you pronounce SQL as "sequel".
RDMS is pronounced, "riddums", right?
If you disagree with me on social issues, then it's pretty clear that you are a narrow-minded bigot.
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 why some of the acorns disappear during special seasons: the squirrels eat them.
as in "Squeal like a little piggy!"
Though outside of our office we do use the better known "sequel"
You can track the lowest price here for "SQL Cookbook" here:
r l=http%3A%2F%2Fwww.amazon.com%2Fgp%2Fproduct%2F059 6009763%2Fsr%3D1-1%2Fqid%3D1147293625%2Fref%3Dsr_1 _1%2F002-4415993-5683262%3F%255Fencoding%3DUTF8
http://www.alienegg.com/lowest-price-finder.php?u
It's a crappy MSSQL thing.
The Kruger Dunning explains most post on
I had a look at the sample chapter, which looks mildly interesting, but the examples are a litte contrived in my opinion.
d ex.html
I only know SQLServer in depth. Most of the techniques he shows rely on features introduced in SQLServer 2005.
http://www.oreilly.com/catalog/sqlckbk/chapter/in
I can onomatopoeically refer to PHP as fffffffffffffuupp.
(Average of 1 Customer Reviews)
Save yourself $13.58 by buying the book here: SQL Cookbook. And if you use the "secret" A9.com discount, you can save an extra 1.57%!
I am not sure how anyone can resolve the following found in the review - emphasis mine.
How can a book not be for beginners when the first recipe teaches the most basic SQL command possible? I have this book and it is a very good book, but like most technical books it kills a great deal of trees needlessly to pad its count. You can probably rip more than 100 pages out of the book that is mindless beginner crap and self-flogging and actually have a very good technical reference for mid-level SQL people.
Programming: Its not just a job - its an indenture.
Uh, Captain Obvious. Have some respect for the accomplishment of others!
Except that "Linus" is pronounce Lee-nuss.
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.
I spend a lot of work hours reading/debugging/re-writing other peoples MSSQL stored procs. While it seems the people before me didn't understand the idea of a set-based language at all (CURSORS! CURSORS! CURSORS!) I understand the concept but have little experience implementing it. Can anyone suggest a SQL reference that puts emphasis on teaching how to implement set-based logic in stored procs, rather than just procedural batches?
I've experiments to run, there is research to be done on the people who are still alive.
[banging head against desk]WHO CARES! WHO CARES! WHO CARES![/banging head against desk]
Jebus God and his black bastard brother Harry, can you people spend your time arguing about something more important!!!!
Dates books are awful, unreadable trash. Take a look at how he explains set operators in "Introduction to Database Systems" - its like he is actively trying to obfuscate them. Who cares how he pronounces it.
Much easier than "S-Q-L" or "sequel". And it gets an occasional laugh.
Celko, according to some real DB experts, knows squat about RDB. I agree. Follow Celko's lead at your peril.
My Ass K3wl!!!!1
Escher was the first MC and Giger invented the HR department.
And the person that modded you insightful is an even bigger dipshit for not seeing what an already astoundingly gigantic dipshit you are.
And for those of us not in an area where technical English texts are not available in a timely manner, there is the Safari Books Online from O'reilly. I don't know if they still have the referal program, but if they do, please use me.
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). I have even created a wannabe book cover for SMEQL.
Table-ized A.I.
SQL is an acronym. It's pronounced, "structured query language".
But when I'm wearing my blue wizard hat, I refer to it by the convenient name, "sequel" which is actually a different product, but everyone knows what I'm talking about.
"Ess cue ell" is what management types call it, because they love spelling out acronyms to make themselves sound important.
Are there any good e-books one could refer to for SQL? Is there a freely available standards document which describe how `base` SQL "should" be, like say the ANSI standards for C?
From the pdf of _Chapter 11_
select a.ename,
(select count(*)
from emp b
where b.ename <= a.ename) as rn
from emp a
I would layout that like
SELECT a.ename,
(SELECT COUNT(*)
FROM emp AS b
WHERE b.ename <= a.ename) AS rn
FROM emp AS a
or like
select a.ename,
(select count(*)
from emp as b
where b.ename <= a.ename) as rn
from emp as a
MySQL is named after one of the original coders daughter My (relatively common swedish girls name).
It just happens to mean "My SQL" in english.
And, no, I didn't just make that up.
A MySQL employee told me this when I asked why he didn't* pronounciate the products name as either "My sequel" or "My S.Q.L".
*) This was in Sweden. Don't know how they say it other countries... Seems stupid from a sales standpoint to stick to a non-intuitive (for a native english speaker) pronounciation outside of MySQLs home country...
"First lesson," Jon said. "Stick them with the pointy end."
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.
"Eye halve a spelling chequer, It came with my pea sea, It plainly marques four my revue, Miss steaks eye kin knot sea"
No, MYSQL would be pronounced as you describe. But the lower case 'y' clearly denotes that that is not part of the acronym
Unfortunately, your "real experts", although brilliant, are just a couple of complete asshole, ivory tower theorists who refuse to accept the fact that the real world can get by quite well without their 1337 über-knowledge.
I'd buy this book in an instant if it could give me a good (efficient) solution for this query: return the set of most recent sales grouped by customer. [Stands back and smirks while flocks of geeks bang their heads against relational algebra]
Key phrase: "comes from" - SQL has come a long way since SEQUEL was created. Most specifically, after it was adopted ANSI in 1986, it essentially lost its meaning as an acronym for "Structured Query Language" and became a hapless three-letter name. Check the ANSI Standard - not only do they insist its pronunciation is letter-by-letter, but its official name is 'SQL' - short for...SQL.
It's pronounced "SQueaL". As in "squeal like a pig".
The problem with the GUI SQL Builders is that you can end up with some pretty slow performing SQL statement. However there are tools available that can Tune/Optimize the SQL that these generate.