When I first started looking at SQL and Relational Theory: How to Write Accurate SQL Code by C.J. Date, I thought I knew a bit about SQL – how to use it, where to use it, and so on. After reading the book however, I’m now know how much I don’t know about the topics of SQL and SQL theory. Reading along, I often felt like I fell down the rabbit hole in Wonderland.
Date is well known for writing An Introduction to Database Systems (Addison Wesley), which has been one of the best-selling textbooks for learning the theories behind modern database systems. And in this book for O’Reilly, he does a great job of explaining, generally with many well thought out discussions and examples, the various applications of many of the database constructs we take for granted. For example, Date takes the simple notion of scalar and non-scalar data types and expounds on them exponentially.
A scalar type is simply a type with no extra attributes, such as an Integer. So a variable of a scalar type has no visible components, just the value. But a non-scalar type is basically a relational object with a set of attributes that define it – basically any other kind of relational model object you can think of that doesn’t boil down to a simple value.
Date then walks through what the basic SQL scalar types are (BOOLEAN, CHARACTER, NUMERIC, and so on) and explains how type checking and coercion works as well as collations, row and table types, and more. He makes sure to provide not only an explanation of how these various properties of SQL work, but also how to avoid some regular gotchas that may crop up. Many developers such as myself trip up on type checking in complex queries, and his suggestion to ensure that “columns with the same name are always of the same type” should help me avoid some issues by not relying on weak type conversions (i.e. an INTEGER isn’t necessarily a FLOAT, even though they can be compared as numeric values).
The book is full of useful examples and explanations that went way over my head in some cases, but I still think I came away with some great information. His notes on operators, constraints, views, logic, and so on provided a ton of food for thought. And as you work through the book, Date does a great job of coming up with example problems to work through and even provides a list of answers at the back of the book in clear language. This allows the reader to take a much more interactive approach and attempt to apply the principles and discussions at a more practical level.
For those of us with only surface-level knowledge of SQL and its many uses, I might stray towards other O’Reilly books like Learning SQL, which provide more practical day to day information than theory. If you’re a DBA or software developer focusing daily on the intricacies of SQL queries and database design, I would strongly suggest you take a look at the book.
Be sure to check out SQL and Relational Theory: How to Write Accurate SQL Code.