Skip to content

Commit

Permalink
Use single quotes for string literals
Browse files Browse the repository at this point in the history 
Replace double quotes with single quotes.  MySQL supports both forms,
but has a stricter ANSI_QUOTES option that restores the standard SQL
interpretation of double quotes as identifier delimiters [1,2].
SQLite silently accepts double-quoted string literals, but its docs
say [3]:

  A string constant is formed by enclosing the string in single quotes
  (').

[1]: https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
[2]: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes
[3]: https://www.sqlite.org/lang_expr.html
  • Loading branch information
@wking
wking committed Mar 26, 2015
1 parent dbd1d59 commit 3cc3a7d
Show file tree
Hide file tree
Showing 5 changed files with 29 additions and 29 deletions.
18 changes: 9 additions & 9 deletions 03-filter.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -19,7 +19,7 @@ We can select these records from the `Visited` table
by using a `WHERE` clause in our query:

~~~ {.sql}
SELECT * FROM Visited WHERE site="DR-1";
SELECT * FROM Visited WHERE site='DR-1';
~~~

|ident|site|dated |
Expand All @@ -40,7 +40,7 @@ we can filter records using `WHERE`
based on values in columns that aren't then displayed:

~~~ {.sql}
SELECT ident FROM Visited WHERE site="DR-1";
SELECT ident FROM Visited WHERE site='DR-1';
~~~

|ident|
Expand All @@ -56,7 +56,7 @@ For example,
we can ask for all information from the DR-1 site collected since 1930:

~~~ {.sql}
SELECT * FROM Visited WHERE (site="DR-1") AND (dated<="1930-00-00");
SELECT * FROM Visited WHERE (site='DR-1') AND (dated<='1930-00-00');
~~~

|ident|site|dated |
Expand Down Expand Up @@ -90,7 +90,7 @@ If we want to find out what measurements were taken by either Lake or Roerich,
we can combine the tests on their names using `OR`:

~~~ {.sql}
SELECT * FROM Survey WHERE person="lake" OR person="roe";
SELECT * FROM Survey WHERE person='lake' OR person='roe';
~~~

|taken|person|quant|reading|
Expand All @@ -110,7 +110,7 @@ Alternatively,
we can use `IN` to see if a value is in a specific set:

~~~ {.sql}
SELECT * FROM Survey WHERE person IN ("lake", "roe");
SELECT * FROM Survey WHERE person IN ('lake', 'roe');
~~~

|taken|person|quant|reading|
Expand All @@ -132,7 +132,7 @@ If we *don't* use parentheses,
we get this:

~~~ {.sql}
SELECT * FROM Survey WHERE quant="sal" AND person="lake" OR person="roe";
SELECT * FROM Survey WHERE quant='sal' AND person='lake' OR person='roe';
~~~

|taken|person|quant|reading|
Expand All @@ -150,7 +150,7 @@ and *any* measurement by Roerich.
We probably want this instead:

~~~ {.sql}
SELECT * FROM Survey WHERE quant="sal" AND (person="lake" OR person="roe");
SELECT * FROM Survey WHERE quant='sal' AND (person='lake' OR person='roe');
~~~

|taken|person|quant|reading|
Expand All @@ -170,7 +170,7 @@ matching any characters in that place.
It can be used at the beginning, middle, or end of the string:

~~~ {.sql}
SELECT * FROM Visited WHERE site LIKE "DR%";
SELECT * FROM Visited WHERE site LIKE 'DR%';
~~~

|ident|site |dated |
Expand All @@ -191,7 +191,7 @@ we can use `DISTINCT` with `WHERE`
to give a second level of filtering:

~~~ {.sql}
SELECT DISTINCT person, quant FROM Survey WHERE person="lake" OR person="roe";
SELECT DISTINCT person, quant FROM Survey WHERE person='lake' OR person='roe';
~~~

|person|quant|
Expand Down
10 changes: 5 additions & 5 deletions 04-calc.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -16,7 +16,7 @@ we can do this calculation on the fly
as part of our query:

~~~ {.sql}
SELECT 1.05 * reading FROM Survey WHERE quant="rad";
SELECT 1.05 * reading FROM Survey WHERE quant='rad';
~~~

|1.05 * reading|
Expand All @@ -41,7 +41,7 @@ we can convert temperature readings from Fahrenheit to Celsius
and round to two decimal places:

~~~ {.sql}
SELECT taken, round(5*(reading-32)/9, 2) FROM Survey WHERE quant="temp";
SELECT taken, round(5*(reading-32)/9, 2) FROM Survey WHERE quant='temp';
~~~

|taken|round(5\*(reading-32)/9, 2)|
Expand All @@ -55,10 +55,10 @@ We can also combine values from different fields,
for example by using the string concatenation operator `||`:

~~~ {.sql}
SELECT personal || " " || family FROM Person;
SELECT personal || ' ' || family FROM Person;
~~~

|personal || " " || family|
|personal || ' ' || family|
|-------------------------|
|William Dyer |
|Frank Pabodie |
Expand All @@ -80,7 +80,7 @@ SELECT personal || " " || family FROM Person;
> The `UNION` operator combines the results of two queries:
>
> ~~~ {.sql}
> SELECT * FROM Person WHERE ident="dyer" UNION SELECT * FROM Person WHERE ident="roe";
> SELECT * FROM Person WHERE ident='dyer' UNION SELECT * FROM Person WHERE ident='roe';
> ~~~
>
> |ident|personal |family |
Expand Down
8 changes: 4 additions & 4 deletions 05-null.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -42,7 +42,7 @@ Null doesn't behave like other values.
If we select the records that come before 1930:

~~~ {.sql}
SELECT * FROM Visited WHERE dated<"1930-00-00";
SELECT * FROM Visited WHERE dated<'1930-00-00';
~~~

|ident|site|dated |
Expand All @@ -54,7 +54,7 @@ we get two results,
and if we select the ones that come during or after 1930:

~~~ {.sql}
SELECT * FROM Visited WHERE dated>="1930-00-00";
SELECT * FROM Visited WHERE dated>='1930-00-00';
~~~

|ident|site|dated |
Expand Down Expand Up @@ -134,7 +134,7 @@ that weren't taken by Lake.
It's natural to write the query like this:

~~~ {.sql}
SELECT * FROM Survey WHERE quant="sal" AND person!="lake";
SELECT * FROM Survey WHERE quant='sal' AND person!='lake';
~~~

|taken|person|quant|reading|
Expand All @@ -154,7 +154,7 @@ If we want to keep these records
we need to add an explicit check:

~~~ {.sql}
SELECT * FROM Survey WHERE quant="sal" AND (person!="lake" OR person IS NULL);
SELECT * FROM Survey WHERE quant='sal' AND (person!='lake' OR person IS NULL);
~~~

|taken|person|quant|reading|
Expand Down
20 changes: 10 additions & 10 deletions 06-agg.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -60,23 +60,23 @@ Three others are `avg`,
and `sum`:

~~~ {.sql}
SELECT avg(reading) FROM Survey WHERE quant="sal";
SELECT avg(reading) FROM Survey WHERE quant='sal';
~~~

|avg(reading) |
|----------------|
|7.20333333333333|

~~~ {.sql}
SELECT count(reading) FROM Survey WHERE quant="sal";
SELECT count(reading) FROM Survey WHERE quant='sal';
~~~

|count(reading)|
|--------------|
|9 |

~~~ {.sql}
SELECT sum(reading) FROM Survey WHERE quant="sal";
SELECT sum(reading) FROM Survey WHERE quant='sal';
~~~

|sum(reading)|
Expand All @@ -96,7 +96,7 @@ for example,
find the range of sensible salinity measurements:

~~~ {.sql}
SELECT min(reading), max(reading) FROM Survey WHERE quant="sal" AND reading<=1.0;
SELECT min(reading), max(reading) FROM Survey WHERE quant='sal' AND reading<=1.0;
~~~

|min(reading)|max(reading)|
Expand All @@ -107,7 +107,7 @@ We can also combine aggregated results with raw results,
although the output might surprise you:

~~~ {.sql}
SELECT person, count(*) FROM Survey WHERE quant="sal" AND reading<=1.0;
SELECT person, count(*) FROM Survey WHERE quant='sal' AND reading<=1.0;
~~~

|person|count(\*)|
Expand All @@ -127,7 +127,7 @@ aggregation's result is "don't know"
rather than zero or some other arbitrary value:

~~~ {.sql}
SELECT person, max(reading), sum(reading) FROM Survey WHERE quant="missing";
SELECT person, max(reading), sum(reading) FROM Survey WHERE quant='missing';
~~~

|person|max(reading)|sum(reading)|
Expand Down Expand Up @@ -176,7 +176,7 @@ We know that this doesn't work:
~~~ {.sql}
SELECT person, count(reading), round(avg(reading), 2)
FROM Survey
WHERE quant="rad";
WHERE quant='rad';
~~~

|person|count(reading)|round(avg(reading), 2)|
Expand All @@ -191,8 +191,8 @@ she could write five queries of the form:
~~~ {.sql}
SELECT person, count(reading), round(avg(reading), 2)
FROM Survey
WHERE quant="rad"
AND person="dyer";
WHERE quant='rad'
AND person='dyer';
~~~

person|count(reading)|round(avg(reading), 2)|
Expand All @@ -210,7 +210,7 @@ using a `GROUP BY` clause:
~~~ {.sql}
SELECT person, count(reading), round(avg(reading), 2)
FROM Survey
WHERE quant="rad"
WHERE quant='rad'
GROUP BY person;
~~~

Expand Down
2 changes: 1 addition & 1 deletion 09-create.md
View file
Original file line number Diff line number Diff line change
Expand Up @@ -126,7 +126,7 @@ once we realize that Frank Danforth didn't take any measurements,
we can remove him from the `Person` table like this:

~~~ {.sql}
DELETE FROM Person WHERE ident = "danforth";
DELETE FROM Person WHERE ident = 'danforth';
~~~

But what if we removed Anderson Lake instead?
Expand Down

0 comments on commit 3cc3a7d

Please sign in to comment.