Friday, 6 September 2013

Learning SQL (Master SQL Fundamentals)

Author: Alan Beaulieu
Publisher: O'Reilly
ISBN: 978-0-596-80219-6
Format: E-Book


So, what does the book actually cover? Well, pretty much everything you'd expect a book with the tagline 'Master SQL Fundamentals', truth be told. The problem with tech books is that there's usually no surprise in the title. Oh well, c'est la vie, as problems go it's far from being the worst in the world!

The following chapter list will give you an idea of what topics made it into the book with a very brief breakdown of each chapter to follow below.

  1. A Little Background
    Introduction to Databases
    What Is SQL?
    What Is MySQL?
    What’s in Store
  2. Creating and Populating a Database
    Creating a MySQL Database
    Using the mysql Command-Line Tool
    MySQL Data Types
    Table Creation
    Populating and Modifying Tables
    When Good Statements Go Bad
    The Bank Schema
  3. Query Primer
    Query Mechanics
    Query Clauses
    The select Clause
    The from Clause
    The where Clause
    The group by and having Clauses
    The order by Clause
  4. Filtering
    Condition Evaluation
    Building a Condition
    Condition Types
    Null: That Four-Letter Word
  5. Querying Multiple Tables
    What Is a Join?
    Joining Three or More Tables
    Equi-Joins Versus Non-Equi-Joins
    Join Conditions Versus Filter Conditions
  6. Working with Sets
    Set Theory Primer
    Set Theory in Practice
    Set Operators
    Set Operation Rules
  7. Data Generation, Conversion, and Manipulation
    Working with String Data
    Working with Numeric Data
    Working with Temporal Data
    Conversion Functions
  8. Grouping and Aggregates
    Grouping Concepts
    Aggregate Functions
    Generating Groups
    Group Filter Conditions
  9. Subqueries
    What Is a Subquery?
    Subquery Types
    Noncorrelated Subqueries
    Correlated Subqueries
    When to Use Subqueries
    Subquery Wrap-up
  10. 10 Joins Revisited
    Outer Joins
    Cross Joins
    Natural Joins
  11. Conditional Logic
    What Is Conditional Logic?
    The Case Expression
    Case Expression Examples
  12. Transactions
    Multiuser Databases
    What Is a Transaction?
  13. Indexes and Constraints
    What Are Views?
    Why Use Views
    Updatable Views
  15. Metadata
    Data About Data
    Working with Metadata

Chapter 1 opens you up to what a database is which sets the scene for the rest of the book. Our protagonist....oh wait, wrong book, sorry! The author discusses in brief non-relational database systems followed by the relational database model. What SQL is exactly can be found defined here too along with a very broad overview of SQL statement classes.

Creating and populating a database is the name of the game in the second chapter. The author has kindly added instructions for installing mySQL 6.0 but alas the instructions are out of date now. This is somewhat irrelevant though as pretty much any version will work and there's a ton of how-to documents on the internet that describe how to install the newest versions. Data type, character sets and table design are touched upon with a very basic introduction to normalisation too. This is not a criticism though, this book is about learning SQL - hence the title - and there are volumes of books on the topic of normalisation. Creating and modifying tables and their contents is also discussed and, more interestingly, the author covers some of the pitfalls and common errors that are seen when working with SQL statements.

In the third chapter, you are introduced to a query primer which sets out to inform you how each query is handled, from the server checking your access rights to the various tables and other objects, to the query optimiser which decides on the execution plan to use for your particular query. The topic of hints (for the optimisers) is also touched upon with a brief note on this for each of the main RDBMS systems. Once the theory of how it runs is dispensed with, the meatier topics of clauses, aliases, distinct values, subqueries, views, table links, the where clause, group by and having clauses, order by clause and sorting clauses are discussed.

Filtering in chapter four covers useful things like not deleting every record in your database (or setting every given field to have the same value).The author covers the use of parentheses to make intent clear (both to you and the DBMS), the NOT operator, building conditions, condition types, (equality, inequality, range, etc.) search expressions (wild cards and regex) and the 'joys' of NULL.

As you'll often find yourself needing data from more than one table at a time, the fifth chapter delves into the world of the JOIN keyword and teaches you how to select from multiple tables at a basic level. Inner joins are discussed along with how to tell the optimiser to to use a particular order. Join conditions versus filter conditions are also demonstrated.

Chapter 6 covers working with sets and this is where you FINALLY get to use those Venn diagrams that you learned at school (It's ok, I'll wait while you Google 'Venn diagrams', it's probably been as long for you as it was for me). UNION and UNION ALL are demonstrated, as well as the Intersect and Except operators. Set operator rules are introduced too, such as sorting compound queries and set operations / precedence. 

While not exactly the sexiest of topics, chapter 7 deals with working with character (string generation and manipulation via functions), numeric (arithmetic functions, handling signed numbers and controlling number precision) and temporal data (string representations of temporal data, string-to-date conversions and temporal functions). Conversion using CAST is also covered.

As not all data that you will retrieve from the database will be in its raw form Chapter 8 introduces the concepts of grouping data. GROUP BY and, by extension, HAVING, aggregate functions (MIN, MAX, AVG, SUM, COUNT), implicit versus explicit groups, counting distinct values and using expressions are all discussed here. Once again our friend NULL puts in an appearance, in the form of a cautionary tale of ensuring to cater for it. Finally ROLLUP is discussed with a reminder of the use of the having clause for filtering with groups.
    Subqueries (queries within queries) are a powerful feature of SQL and are covered here in chapter 9. The author discusses different types (e.g. noncorrelated and scalar) and how some can be used on one side of an equality condition. Additional operators that can be used with subqueries are introduced : the IN and NOT IN operators, the ALL operator and the ANY operator. Correlated subqueries are then examined and a previous example is reworked (a common theme throughout the text) to show how these subqueries work.The author also covers the question of when to use a subquery. A lot is covered in this chapter.

    In chapter 10 we rejoin (boom boom!) the author in a discussion of the JOIN. Outer joins (Left versus Right, Three-Way Outer Joins), cross joins and natural joins all get examined, with an increasing complexity to the SQL statements that build upon everything learned so far. 

    The 11th chapter deals with conditional logic and shows you how to have your SQL branch in different directions depending on the column values or expressions. The CASE expression is introduced here in it's various permutations and the concept of selective aggregation is demonstrated, along with the handling of division by 0 (and other errors). It's not just conditional select statements that are covered however as conditional updates are also demonstrated with more handling of the NULL thrown in for good measure. 

    Multi-user databases, locking, locking granularity (table/page/row) are discussed at high level in chapter 12. Transactions are then introduced and the author shows how to start and stop them, the use of savepoints and rollbacks too. 

    While the focus of this book is learning SQL the author shrewdly points out that there are other feathers in the DBMS that can have an effect on the code that you write, among them indexes and constraints. The 13th chapter therefore touches on the theory of indexes, how to create, alter and delete them and considerations for which type of index to use. Just as importantly the downside of indexes are also discussed to make you aware of potential issues in excessive usage. Constraints are also introduced with an overview of how the major DBMS' treat them. 

    The 'how-to' of view creation, modification etc. is covered in the 14th chapter along with the theory of when to use them and their benefits and limitations. 

    Finally, the 15th chapter deals with metadata. A book in its own right, metadata is essentially data about data and this chapter discusses how the DBMS maintains information about every object in it.s data dictionary / system catalogue. The author discusses how to interpret this data, retrieve it programmatically and the practical uses of doing so for schema generation and deployment verification.

    General Impressions

    A well-structured book with dozens of examples and a logical structure, building on each previous exercise to increase your knowledge of how things work in the world of SQL. While the queries are executed in MySQL (as it's free), the author makes note of syntactical differences in statements between the major players of the database world. The aim of this book is to get you to grips with the basics of SQL and does an admirable job of this. Some of the information is a little dated now and a new edition would be quite welcome but it still sets out to do what it states - help you to learn SQL.


    An excellent book for beginners, the author gives clear examples and scripts to ensure that you are quickly up and running. Intermediate or advanced users can probably skip this; it's by no means a reference and does not cover all the things you'd need day-to-day if you're either a developer or a heavy end-user but then it is called Learning SQL and not Advanced SQL. If you're new to the language, and want to learn the basics in a structured and well thought-out way, pick up a copy.  


    A little dated now but still very useful, the price tag of $31.99 may seem a little hefty for the ebook edition, but given all that you get in this book it'd rate it 4/5.

    Thanks for reading!


    Next review will be Designing for Behaviour Change

    No comments:

    Post a Comment