Simply SQL - The Web Site

The companion web site for the SitePoint book Simply SQL by Rudy Limeback

Welcome!

My name is Rudy Limeback and I'm the author of Simply SQL (ISBN-13: 978-0-9804552-5-0), published in December 2008 by SitePoint Pty, Ltd.

The purpose of this site is to provide:

Feedback

This site is just getting off the ground, so there's not a lot here yet. I've documented a small number of errata, below. Many explanations and clarifications have yet to be written up, so this is a good time for you to submit questions or beefs; maybe there's something that is poorly explained in the book, or perhaps you've found some errors that I haven't (yet). The contact form for this site isn't ready, so please submit all comments and questions via my r937.com contact page.

Style this site!

June 10, 2009

My apologies for taking so long (two months!) to make a decision on the lovely CSS designs that were submitted for this site. The one I liked the best was by Arlen Walker of Paladin Web Services. You're soaking in it. (That was a cultural reference, a famous line from a television commercial.)

In my copious spare time, I plan next to implement a stylesheet switcher. I received many great submissions and you should get a chance to see them all. (I'll stop short of implementing a "hot or not" voting feature, though.)

Thanks to everyone who submitted a stylesheet.

Purchase

Please make sure your browser's cookies are turned on and then use any of the following links:

Errata

Updated March 18, 2009

In sequence by page number
Page xx, Clarification: avoid HAVING without GROUP BY

Under Chapter 6 The HAVING Clause, change:

Chapter 6 will show you how to write HAVING clause conditions, and how to use the HAVING clause without a GROUP BY clause.

to:

Chapter 6 will show you how to write HAVING clause conditions, and how to avoid using the HAVING clause without a GROUP BY clause.

Chapter 6 has two examples of HAVING without GROUP BY—threshold alerts, and column aliases (in a Warning)—but they are anti-examples; alternate strategies are provided.

Page 9, Typo: sample DDL should use leading commas

The sample DDL statement should be using leading commas. The correct example is:

CREATE TABLE teams
(
  id           INTEGER      NOT NULL  PRIMARY KEY
, name         VARCHAR(37)  NOT NULL
, conference   VARCHAR(2)   NULL
);

It's correct in the code library (see module Teams_01_CREATE_ALTER_DROP.sql).

This would seem to be a picky minor point, but my coding style for SQL is actually somewhat sophisticated. It's quite important to me, and involves much more than leading commas.

Unfortunately for me, leading commas turned out to be the only part of my SQL coding style that survived the technical editing process. Sitepoint already had pre-existing conventions for code (not just SQL but other types of code as well). Allowing leading commas in the SQL for this book was a concession to me on their part, and for this I remain grateful. (Actually, I think they agreed with my reasons why leading commas are better than trailing commas—see the Tip Leading Commas on page 51.) Still, the automatic conversion of my code before publication had a few whoopsies, and this is one of them.

Page 9, Clarification: SQL keywords are case-insensitive

The Tip Upper Case or Lower Case reads as follows:

Although it's of no consequence to SQL whether a font appears in caps or lower case, identifiers may indeed be case-sensitive. However, I'd strongly advise you to create your database objects with lower case letters to avoid syntax problems with unknown names.

The first part is needlessly ambiguous. Fonts are visual stylings of display or printed characters, like Helvetica or Courier New. What this should have said is that it's of no consequence whether the keywords appear in caps or lower case, because SQL itself is case-insensitive.

My coding style is to write SQL keywords always in upper case. This makes them stand out from identifiers, which should always be in lower case. I had debated whether to discuss the merits of CamelCase, but decided against it, because there are so few, and because we're still only on page 9.

Page 9, Typo: "mispelled" is misspelled

The first sentence after the CREATE TABLE sample says:

Neatness helps us to spot parts of the statement we have omitted or mispelled...

Yes, "mispelled" is misspelled. I take full responsibility for this, but can only guess how it got past the editors.

Page 45, Typo: rep should say represent

In addition to fixing the typo, the first paragraph could also be reworded for clarity. Change:

Cross joins can be very useful but are exceedingly rare. Their purpose is to produce a tabular structure containing rows which rep all possible combinations of two sets of values (in our example, columns from two tables) as shown in Figure 3.6; this can be useful in generating test data or looking for missing values.

to:

Cross joins can be very useful but are exceedingly rare. Their purpose is to produce a tabular structure containing rows which represent all possible combinations of two sets of values—in our example, column values from tables A and B, as shown in Figure 3.6. Cross joins can be useful in generating test data or looking for missing values.

Page 71, Typo: the Chapter 4

In the last paragraph, change:

In the Chapter 4, ...

to:

In Chapter 4, ...

Page 81, Clarification: date comparison operators

The first sentence after the Collations Note reads:

Besides comparing numbers and strings, we can also compare dates using the equals and not equals operators (= and <>).

Change this to:

Besides comparing numbers and strings, we can also compare dates. The same comparison operators may be used with dates as with numbers and strings:

  • = (equal)
  • <> (not equal)
  • < (less than)
  • <= (less than or equal)
  • > (greater than)
  • >= (greater than or equal)
Page 86, Error: dateposted should be created

In the second of the three WHERE clause examples, change the column name from dateposted to created, so that the example reads:

WHERE
  '2009-02-01' <= created AND created < '2009-03-01'
Page 86, Clarification: compound condition coding style

Both the second and third WHERE clause examples show a compound condition written on a single line, using the AND operator in the middle of that line. I never do that. My style always places ANDs and ORs on separate lines. Furthermore, indentation and spacing allow parts of the conditions to "line up" visually.

The second of the three WHERE clause examples (using the correct column name; see previous typo) was originally styled like this:

WHERE '2009-02-01' <= created 
  AND                 created < '2009-03-01'

The purpose of this indentation was to emphasize the "between-ness" of the column value between the endpoints of the date range.

The third of the three WHERE clause examples was originally written like this:

WHERE created >= '2009-02-01' 
  AND created  < '2009-03-01'

Thus the comparison operators (greater-than-or-equal, and less-than) are visually isolated, while the date values are also easy to compare. This style, in my opinion, is much easier to grok, especially if you're scanning for errors or reading a query that you're not familiar with.

Page 91, Clarification: expressions

In the paragraph following the second example, I used the term "expression" without much explanation:

The IN condition syntax consists of an expression, followed by the keyword IN, followed by a list of values in parentheses.

In the example, the expression is simply the qualified column customers.name. Column names are the most common expressions, but the syntax actually allows complexity. Expressions can involve functions and constants, combined in numerous ways. As long as the expression resolves to a single value, you can use it with an IN condition. In fact, you can use an expression in other conditions, too, as well as in the SELECT clause.

Page 131, Error: columnC = 5 should be columnC = 0

The sample query on this page has:

WHERE
  calc > 9 
      OR ( columnC = 5 AND calc > 37 )

This should say columnC = 0 to match the condition on the previous page.

Thanks to SimonJM for spotting this one.

Page 179, Typo: missing parenthesis

In the second of the two SELECTs in the UNION, change:

, CAST(COUNT(items.name AS CHAR) AS item

to:

, CAST(COUNT(items.name) AS CHAR) AS item

It's correct in the code library (see module Cart_16_Details_and_Totals.sql).

Page 180, Typo: SELECT *

The second sentence of the first paragraph begins:

SELECT * star has been used ...

Remove the word star.

Page 196, Typo: have not tested

The last sentence of the third paragraph ends:

... and I've yet to tested the differences.

This should say I have not tested the differences. The word "yet" suggests I'm still planning to test it, but to be honest, I'm not going to.

Page 227, Typo: relationship between entries and content

In the second sentence of the first paragraph, change:

In the CMS sample application, there's a one-to-one relationship between entities and content.

to:

In the CMS sample application, there's a one-to-one relationship between entries and content.

Thanks to rctneil for spotting this one.