Authors: John Viescas and Michael J. Hernandez
Publisher: Addison Wesley, 2014
Aimed at: Anyone wanting to learn or improve SQL
Reviewed by: Kay Ewbank
While it may sound as though it's aimed at people who don't know SQL, in reality there are topics in this book that would be useful no matter how good you are at writing SQL code. If, however, you don’t know much, if any, SQL this is an excellent book.
The authors have done everything they can to make the material understandable, with good descriptions, lots and lots of examples, and a real in-depth coverage of SQL. They have also done their best to make sure the SQL they cover works, no matter what database you’re working with. They use ISO/ANSI SQL, and when they reach a part of SQL that’s not supported as per the standard by a major product, they warn you in the text and show the alternative (working) version of the SQL. This 3rd edition has been updated to use the 2011 SQL Standard, though the authors do stick to those parts that have been implemented. The examples have also been updated to take account of changes to SQL Server, Access, DB2, Oracle and MySQL.
The book starts with some scene setting. There’s a chapter laying out what a relational database is in terms of tables, fields, relationships etc, followed by an interesting chapter on ensuring your database structure is sound. The authors point out that if your database structure is poor, there’s not much point learning how to write really good SQL because it will be at best difficult to implement and at worst, relatively useless. The chapter is split into sections on fine-tuning fields and tables, and on establishing solid relationships. Much of the material in the chapter is classic relational theory and third normal forms, but written in a way that makes practical sense without the preaching overtones often associated with this section of database books. Next is a short history of SQL discussing its origins, the standard, and the future of SQL.
Having got the background out of the way, the authors move on to the basics of SQL, starting with a look at how to create a simple query using the Select statement. Even in this initial introduction, the authors do more than just discuss the statement, with a nice aside on data versus information, suggesting that you store data in a database but retrieve information from it. Next comes a chapter on how to get more than simple columns returned by your queries. This is essentially a look at expressions showing what they are and how to use them in a Select clause. 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. As with all the other chapters in the book, the authors use plenty of examples, and finish with a set of problems for you to solve using the techniques you’ve used within the chapter.
The next part of the book moves on to 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. Most people are OK on an Inner Join, but get onto Left and Right Outer Joins and a look of panic comes into the eyes. The examples used by the authors for illustrating joins make the subject nice and clear. The authors have a nice technique for showing how to create a query where they take a request (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 authors move 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 is new in this edition, and is perhaps the most useful as it looks at solving tough problems. Even if you already know SQL, this section makes really interesting reading. 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, and the final chapter looks at unlinked data and the use of 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.
I think this is an excellent book. The examples are really clear and show both traps you can fall into and how to avoid them. The authors avoid relational jargon while staying within the rules, and help you think more clearly about how to write good SQL.