November 17, 2006
SQL Hacks: Write queries that won't turn to digital dust
Sebastopol, CA--Andrew Cumming is a big fan of SQL; he says it's elegant,
well designed, and phenomenally useful...but it's not always easy to use:
"You can do incredibly powerful queries in just a few lines of code, but
then other times you have to tie yourself in knots just to get something
trivial done." With coauthor Gordon Russell, Cumming has written SQL Hacks
(O'Reilly, US $29.99), a collection of 100 tips and tools to help
users get more from SQL's power and flexibility, and spend less time tying
themselves in knots. "There are a handful of SQL 'cliches' or 'phrases'
that I use all the time, but not everyone knows about them," says Cumming.
"They save a lot of time and once they get under your skin, you can use
them without thinking about them."
For example, "Just as you might use for (int i=0;i<10;i++) without
thinking about how it works, I use SELECT 100*foo/(SELECT SUM(foo) FROM
bar) FROM bar or a more efficient variation."
SQL Hacks is primarily aimed at the working programmer, but Cumming
assures non-programmers that they'll find the book useful as well. "If
you're confident using a drag-and-drop query builder to get the data you
need on an ad-hoc basis, then maybe you should think about taking your
skills to the next level. You can use a query builder for the simple
stuff--it's ideal--but if you're asking complex questions of your database
then sooner or later you need to get your hands dirty and write some
The book is also for those programmers who hate SQL, the kind who reach
for a loop or hash table to do anything but the simplest filtering
commands. "If they took just a little time to learn a handful of tricks,
they could improve their code and their productivity immensely," says
Equally applicable for users running Access, MySQL, SQL Server, Oracle, or
PostgreSQL, SQL Hacks shows how to:
- Wrangle data in the most efficient way possible
- Aggregate and organize data for meaningful and accurate reporting
- Make the most of subqueries, joins, and unions
- Stay on top of the performance of your queries and the server that runs
- Avoid common SQL security pitfalls, including the dreaded SQL injection
"Just a tiny bit more SQL can make a huge difference in terms of
efficiency and maintainability," Cumming reminds his readers. "If you have
a choice between implementing a complicated calculation in the program or
in the database, many programmers will do it in the program because that's
the language they're more comfortable with. But if you do it in the
database, the chances are it will run faster. Not only that, it will live
longer, too. When the next version of your application comes out, the
application code may well be digital dust--but the data, the database, and
your queries and views are likely to survive--to be reused over and
Andrew Cumming is the Zoo Keeper at sqlzoo.net. He keeps a collection of tame SQL engines and presents these to the public. He cares
for these beasts, protecting them from the abuse inflicted by SQL
learners, and protecting SQL learners from the injuries that the wild
engines can inflict. But mostly he shovels manure out of the cages and
hoses them down every now and then. Andrew also teaches at Napier
University in Edinburgh, Scotland.
Dr. Gordon Russell is a Computing lecturer in Napier University, Scotland.
He teaches databases, Linux, and networking. He also builds educational
websites, concentrating on technology-driven online learning environments.
These include db.grussell.org, whose claim-to-fame is its automatic
checking of SQL assessments, and linuxzoo.net, which offers online
users root free access to Linux servers. But without database technology
to support these sites, even he would be lost.
Background and Market Information:
Andrew Cumming and Gordon Russell
ISBN: 0-596-52799-3, 410 pages, $29.99 US, $38.99 CA
O'Reilly Media spreads the knowledge of innovators through its books, online services, magazines, and conferences. Since 1978, O'Reilly Media has been a chronicler and catalyst of cutting-edge development, homing in on the technology trends that really matter and spurring their adoption by amplifying "faint signals" from the alpha geeks who are creating the future. An active participant in the technology community, the company has a long history of advocacy, meme-making, and evangelism.
Return to: O'Reilly Press Room
Recent Press Releases
Press Release Archive »
Media Relations - North America & Conferences
Media Relations - Japan
Media Relations - United Kingdom