Author: John L. Viescas
Publisher: Addison Wesley
Pages: 960
ISBN: 978-0134858333
Print: 0134858336
Kindle: B079G27STM
Audience: Developers working with data
Rating: 5
Reviewer:Kay Ewbank

This is an updated version of a classic book teaching SQL. It's an excellent choice if you don't know SQL and need to get to grips with it.

The ideal reader would be someone who's beginning to use SQL or only knows the basics, but the author does go on to advanced topics, and there are new chapters in this updated version.

Author John Viescas has worked hard to make SQL understandable by using lots of examples. The SQL in the book is suitable for (and backed up by sample databases for) Access, Microsoft SQL Server, MySQL and PostgreSQL. The descriptions given are understandable, and where there are differences in the dialect of a particular SQL that is explained with a warning in the text and an explanation of the the alternative version of the SQL to work in whichever database goes away form the standard.

The first part of the book is all about what makes up a SQL database, starting with a chapter explaining tables, fields, and relationships. Viescas then goes on to explain how to ensure your database structure is sound, with explanations about how to fine-tune fields and tables, and how to establish solid relationships. Underpinning the practical advice is classic relational theory, but as with the rest of the book Viescas concentrates on making the material accessible. This opening section of the book ends with a short history of SQL discussing its origins, the standard, and the future of SQL.

SQL basics are the next topic, taking us into the simplest of SQL with the Select statement. The way this topic is handled highlights the strength of the book, with a digression to explain data versus information, on the basis you store data in a database but retrieve information from it. Next, Viescas looks at how to use expressions to return more than simple columns. The final chapter in this part of the book looks at filtering your data using Where, search conditions, and using multiple conditions with And and Or.

Part Three of the book is on working with multiple tables, starting with an introduction to sets and set operations – intersections, differences, and unions. Having covered the theory, the SQL Set operations are then introduced. The next three chapters cover the parts of SQL that can cause most confusion – Inner and Outer Joins and Unions. It's hard for beginners to work out what data will be returned by a particular join type, and even harder to work out which join they need.  The examples in the book are good, and Viescas has a nice technique for showing how to create a query. In this case, the questions are about recipes in a cookery book, so for example, list the recipe types in my cookery database that do not yet have any recipes. The request is then translated into pseudo-SQL, cleaned up to remove non-SQL words such as ‘the’ and converted so empty is changed to Null. At this stage the extra words are shown crossed out. Finally, you’re shown the SQL. This technique is used all the way through the book and it does give a useful guide into the steps of building a query. Sometimes, the query being constructed isn’t a valid one. The authors say at the start Caution, there’s a trap here, then lead you through the creation of the query before explaining why it wouldn’t actually work and what the results would really be.

There’s a nice clear chapter on subqueries, then the author moves on to a section on summarizing and grouping data. This has chapters on working out simple totals using aggregate functions such as Sum, Count, Avg, Max and Min; grouping data using the Group By clause; and filtering using Having. The latter chapter is good at explaining the problems you can get into over where to put your filter – in the Where or Having clause, what the Having Count problems are, and so on.

Three chapters on the modifying operators – Update, Insert and Delete – take you from the basics through to more advanced problems such as dealing with transactions, generating primary keys while inserting, and how to avoid key violations when deleting.

The last part of the book looks at solving tough problems. It was introduced in the previous edition and has been extended in this version, and makes interesting reading no matter how good your SQL knowledge. There’s a chapter on Not and And problems that works through some complex queries using clauses such as Not In and Not Exists. By this point in the book there’s a lot of code and the explanations are short by comparison. The chapter on Condition Testing is mainly an explanation of Case statements and how to use them correctly. A chapter on unlinked data shows how to use the Cross Join, as well as using ‘driver’ tables where you create a table consisting of just the list of values you want to Cross Join with other tables to find specific information.

The final two chapters in the book are new. There's a chapter on performing complex calculations on groups that shows how to use the more advanced concepts of Group By such as Rollups, Cubes and Grouping Sets. The final chapter looks at partitioning data into windows as introduced in the SQL 2003 Standard. Window functions are applied to a set of rows described by a window descriptor, and return a single value for each row. The author describes what you can do using windows, ranking data, splitting data into quintiles, and using aggregate functions.

I think this is an excellent book. You could argue that a reader who needs to begin at the start will fall by the wayside long before data quintiles, but given enough time and practice they'd get there. The examples are really clear and show both traps you can fall into and how to avoid them. The author avoids relational jargon while staying within the rules, and help you think more clearly about how to write good SQL.

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.