Data Management

The SELECT statement explained in detail

Whether you are a SQL guru or newbie, SQL Queries for Mere Mortals is a book for you. Besides a detailed introduction to syntax, you can pick up some tips on database design.


In my never-ending search for a great reference book on SQL, I stumbled upon a book entitled SQL Queries for Mere Mortals, by Michael J. Hernandez and John L. Viescas (published by Addison Wesley). This well-written book introduces the reader to database fundamentals and transitions into some fairly advanced SQL queries at a reasonable pace. The book’s primary focus is retrieving information (SELECT statements.) The authors feel that many people are trying to get data out of databases and that there are plenty of books about database programming but not enough data extraction.
 
 

SQL Queries for Mere Mortals

By Michael J. Hernandez and John L. Viescas
Addison-Wesley

ISBN: 0-201-43336-2
Price: $39.95


 
 

I must admit that the title was the initial attraction (that’s right, I buy stuff on the end of aisles too), but I followed up with searches on some of my favorite book sites to read the reviews. I got a warm feeling from the reviews on the book jacket and Amazon, plus the book comes from one of my favorite publishers, Addison-Wesley; so, I decided it was worth trying.

The book consists of 14 chapters grouped into 4 parts:
  • ·        Relational databases
  • ·        SQL basics
  • ·        Working with multiple tables
  • ·        Summarizing and grouping data

The accompanying CD contains sample Access and SQL Server databases that are used throughout the book. Not to worry, all you non-Microsoft users—you can use the SQL scripts to build the databases in whatever DB you prefer. The database comes with a trial version of Microsoft SQL Server 7 that can be installed on Windows 95, 98, 2000 or NT. The authors also include SQL command files that will create the tables and load the data. (The authors state that they made every effort to use the most common syntax for these commands.)

An extremely useful feature used throughout this book, when discussing the SQL syntax, is a syntax diagram. The diagrams explain the syntax of the SQL statement you are currently exploring and do a great job of bringing together the information.

Relational databases and SQL
If you have any SQL experience under your belt, you can safely skip these three chapters of the book. This section of the book is reserved for individuals who need a better understanding of some of the terms frequently tossed around in the database world, such as tables, fields, records, keys, one-to-one, views, and so on. Although the terms are lightly covered, they will leave the reader with a functional handling of the basic database terminology.

In Chapter 2, the authors briefly touch on some aspects of database layout and design. I found this chapter particularly useful because if you don’t have a well-designed database, then you will have a tough time retrieving data. Usually, whole books are devoted to this topic, but the writers offer a “CliffsNotes” version of the subject that provides a solid introduction.

On the flipside, Chapter 3, which is devoted to the history of SQL, is of little practical use.

SQL basics
The chapters in this section dissect the SELECT statement and all of its components, including DISTINCT, ORDER BY, literal values, expressions, and WHERE. After reading these chapters, you will be able to create simple SELECT statements to retrieve data. This section introduces translating the user requests into a SQL query. The authors call this the “Request/Translation/Clean Up/SQL” technique. All the examples in the book take the reader through these steps, similarly to the example shown in Listing A.

Although I found this exercise to be a great way to introduce the novice user to translating requests into SQL, I grew tired of seeing these steps in the later chapters.

A topic that gets some good coverage is how NULL values are treated in the SELECT statement. The authors point out that NULLs can “adversely” affect the outcome of a query, when not specifically dealt with. Listing B highlights an example from the book that points out some of the problems you can run into.

The trouble occurs when the TotalValue column is calculated with a NULL value. As long as Price and QuantityOnHand are non-NULL values, a valid number is calculated. If Price or QuantityOnHand is NULL, the value for TotalValue in that row will be NULL as well. This result is easily corrected by changing the table design to prevent NULL values from being inserted into those columns. If you are dealing with a legacy table, then you can modify the query in Listing B to exclude those columns that are NULL, as shown in Listing C.

Another great point made in this section is how to properly construct your query when filtering with the WHERE clause. Always remember that it is best to place the more limiting restrictions of a query first; that way, you can better manage database resources applied to the query.

Working with multiple tables
Those of you who have some SQL miles on your tires can turn right to this section and get some traction. Set theory is introduced in the first chapter to give the reader some background on the concept of using JOIN with tables. I found this to be extremely helpful when digging into the later chapters. You can then work your way through chapters that cover inner joins, outer joins, and unions, ending with subqueries. Each chapter provides examples using the “Request/Translation/Clean Up/SQL” technique mentioned earlier. You can polish off each chapter with exercises to try what you learned.

Joins are thoroughly covered. I found myself with a new understanding of how and when to use them. The authors also show what data you should and should not join tables on and continued to drive home the importance of database design. Sound database design is crucial to understanding table relationships and craft better queries.

Summarizing and grouping data
The final section in this book centers on the discussion of aggregate functions within SQL and how they are constructed. The authors start the section by detailing aggregate functions that summarize data in SQL, such as COUNT, AVG, MAX, MIN, and SUM. DISTINCT is also discussed, because it pertains to some of the aggregate functions. (DISTINCT eliminates duplicate rows based on the column name that follows it. For instance, if you wanted a count of all unique cities in a table, you would use the DISTINCT keyword to retrieve unique ones and then count them.) The next chapter provides a complete discussion of GROUP BY and how to use it with the aggregate functions. The book wraps up describing HAVING clauses, which is the filter applied after using a GROUP BY clause.

Check it out
For those of you who would like to try before you buy, check out the book’s table of contents online at Addison Wesley’s Web site. Whether you are a SQL newbie or want to brush up on advanced SELECT statements, look no further than this great guide.

Got a favorite book?
Have a favorite book on SQL that you would like to share? Post it in our discussion center below.

 

Editor's Picks