Merge pull request swcarpentry#38 from timtomch/gh-pages

Made all SQL statements UPPER CASE - including figures.
uzinaduzina · Feb 24, 2015 · b248d96 · b248d96
2 parents 784cd40 + 4b8c0fb
commit b248d96
Show file tree

Hide file tree

Showing 22 changed files with 330 additions and 3,361 deletions.
diff --git a/01-select.html b/01-select.html
@@ -308,7 +308,7 @@ <h2>Learning Objectives</h2>
 </table>
 </blockquote>
 <p>Notice that three entries --- one in the <code>Visited</code> table, and two in the <code>Survey</code> table --- don't contain any actual data, but instead have a special <code>-null-</code> entry: we'll return to these missing values <a href="05-null.html">later</a>. For now, let's write an SQL query that displays scientists' names. We do this using the SQL command <code>select</code>, giving it the names of the columns we want and the table we want them from. Our query and its output look like this:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> family, personal <span class="kw">from</span> Person;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> family, personal <span class="kw">FROM</span> Person;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -371,9 +371,9 @@ <h2>Learning Objectives</h2>
 </tr>
 </tbody>
 </table>
-<p>Whatever casing convention you choose, please be consistent: complex queries are hard enough to read without the extra cognitive load of random capitalization.</p>
+<p>Whatever casing convention you choose, please be consistent: complex queries are hard enough to read without the extra cognitive load of random capitalization. One convention is to use UPPER CASE for SQL statements, to distinguish them from tables and column names. This is the convention that we will use for this lesson.</p>
 <p>Going back to our query, it's important to understand that the rows and columns in a database table aren't actually stored in any particular order. They will always be <em>displayed</em> in some order, but we can control that in various ways. For example, we could swap the columns in the output by writing our query as:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> personal, family <span class="kw">from</span> Person;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> personal, family <span class="kw">FROM</span> Person;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -405,7 +405,7 @@ <h2>Learning Objectives</h2>
 </tbody>
 </table>
 <p>or even repeat columns:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> ident, ident, ident <span class="kw">from</span> Person;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> ident, ident, ident <span class="kw">FROM</span> Person;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -443,7 +443,7 @@ <h2>Learning Objectives</h2>
 </tbody>
 </table>
 <p>As a shortcut, we can select all of the columns in a table using <code>*</code>:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> * <span class="kw">from</span> Person;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> * <span class="kw">FROM</span> Person;</code></pre>
 <table>
 <thead>
 <tr class="header">

diff --git a/01-select.md b/01-select.md
@@ -111,7 +111,7 @@ giving it the names of the columns we want and the table we want them from.
 Our query and its output look like this:
 
 ~~~ {.sql}
-select family, personal from Person;
+SELECT family, personal FROM Person;
 ~~~
 
 |family  |personal |
@@ -145,6 +145,8 @@ SeLeCt FaMiLy, PeRsOnAl FrOm PeRsOn;
 Whatever casing convention you choose,
 please be consistent:
 complex queries are hard enough to read without the extra cognitive load of random capitalization.
+One convention is to use UPPER CASE for SQL statements, to distinguish them from tables and column
+names. This is the convention that we will use for this lesson.
 
 Going back to our query,
 it's important to understand that
@@ -155,7 +157,7 @@ For example,
 we could swap the columns in the output by writing our query as:
 
 ~~~ {.sql}
-select personal, family from Person;
+SELECT personal, family FROM Person;
 ~~~
 
 |personal |family  |
@@ -169,7 +171,7 @@ select personal, family from Person;
 or even repeat columns:
 
 ~~~ {.sql}
-select ident, ident, ident from Person;
+SELECT ident, ident, ident FROM Person;
 ~~~
 
 |ident   |ident   |ident   |
@@ -184,7 +186,7 @@ As a shortcut,
 we can select all of the columns in a table using `*`:
 
 ~~~ {.sql}
-select * from Person;
+SELECT * FROM Person;
 ~~~
 
 |ident   |personal |family  |

diff --git a/02-sort-dup.html b/02-sort-dup.html
@@ -37,7 +37,7 @@ <h2>Learning Objectives</h2>
 </ul>
 </div>
 <p>Data is often redundant, so queries often return redundant information. For example, if we select the quantitites that have been measured from the <code>survey</code> table, we get this:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> quant <span class="kw">from</span> Survey;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> quant <span class="kw">FROM</span> Survey;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -110,8 +110,8 @@ <h2>Learning Objectives</h2>
 </tr>
 </tbody>
 </table>
-<p>We can eliminate the redundant output to make the result more readable by adding the <code>distinct</code> keyword to our query:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> <span class="kw">distinct</span> quant <span class="kw">from</span> Survey;</code></pre>
+<p>We can eliminate the redundant output to make the result more readable by adding the <code>DISTINCT</code> keyword to our query:</p>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="kw">DISTINCT</span> quant <span class="kw">FROM</span> Survey;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -131,7 +131,7 @@ <h2>Learning Objectives</h2>
 </tbody>
 </table>
 <p>If we select more than one column --- for example, both the survey site ID and the quantity measured --- then the distinct pairs of values are returned:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> <span class="kw">distinct</span> taken, quant <span class="kw">from</span> Survey;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="kw">DISTINCT</span> taken, quant <span class="kw">FROM</span> Survey;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -219,8 +219,8 @@ <h2>Learning Objectives</h2>
 </tbody>
 </table>
 <p>Notice in both cases that duplicates are removed even if they didn't appear to be adjacent in the database. Again, it's important to remember that rows aren't actually ordered: they're just displayed that way.</p>
-<p>As we mentioned earlier, database records are not stored in any particular order. This means that query results aren't necessarily sorted, and even if they are, we often want to sort them in a different way, e.g., by the name of the project instead of by the name of the scientist. We can do this in SQL by adding an <code>order by</code> clause to our query:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> * <span class="kw">from</span> Person <span class="kw">order</span> <span class="kw">by</span> ident;</code></pre>
+<p>As we mentioned earlier, database records are not stored in any particular order. This means that query results aren't necessarily sorted, and even if they are, we often want to sort them in a different way, e.g., by the name of the project instead of by the name of the scientist. We can do this in SQL by adding an <code>ORDER BY</code> clause to our query:</p>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> * <span class="kw">FROM</span> Person <span class="kw">ORDER</span> <span class="kw">BY</span> ident;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -257,8 +257,8 @@ <h2>Learning Objectives</h2>
 </tr>
 </tbody>
 </table>
-<p>By default, results are sorted in ascending order (i.e., from least to greatest). We can sort in the opposite order using <code>desc</code> (for &quot;descending&quot;):</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> * <span class="kw">from</span> person <span class="kw">order</span> <span class="kw">by</span> ident <span class="kw">desc</span>;</code></pre>
+<p>By default, results are sorted in ascending order (i.e., from least to greatest). We can sort in the opposite order using <code>DESC</code> (for &quot;descending&quot;):</p>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> * <span class="kw">FROM</span> person <span class="kw">ORDER</span> <span class="kw">BY</span> ident <span class="kw">DESC</span>;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -295,9 +295,9 @@ <h2>Learning Objectives</h2>
 </tr>
 </tbody>
 </table>
-<p>(And if we want to make it clear that we're sorting in ascending order, we can use <code>asc</code> instead of <code>desc</code>.)</p>
+<p>(And if we want to make it clear that we're sorting in ascending order, we can use <code>ASC</code> instead of <code>DESC</code>.)</p>
 <p>We can also sort on several fields at once. For example, this query sorts results first in ascending order by <code>taken</code>, and then in descending order by <code>person</code> within each group of equal <code>taken</code> values:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> taken, person <span class="kw">from</span> Survey <span class="kw">order</span> <span class="kw">by</span> taken <span class="kw">asc</span>, person <span class="kw">desc</span>;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> taken, person <span class="kw">FROM</span> Survey <span class="kw">ORDER</span> <span class="kw">BY</span> taken <span class="kw">ASC</span>, person <span class="kw">DESC</span>;</code></pre>
 <table>
 <thead>
 <tr class="header">
@@ -393,7 +393,7 @@ <h2>Learning Objectives</h2>
 </tbody>
 </table>
 <p>This is easier to understand if we also remove duplicates:</p>
-<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">select</span> <span class="kw">distinct</span> taken, person <span class="kw">from</span> Survey <span class="kw">order</span> <span class="kw">by</span> taken <span class="kw">asc</span>, person <span class="kw">desc</span>;</code></pre>
+<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="kw">DISTINCT</span> taken, person <span class="kw">FROM</span> Survey <span class="kw">ORDER</span> <span class="kw">BY</span> taken <span class="kw">ASC</span>, person <span class="kw">DESC</span>;</code></pre>
 <table>
 <thead>
 <tr class="header">

diff --git a/02-sort-dup.md b/02-sort-dup.md
@@ -17,7 +17,7 @@ from the `survey` table,
 we get this:
 
 ~~~ {.sql}
-select quant from Survey;
+SELECT quant FROM Survey;
 ~~~
 
 |quant|
@@ -46,11 +46,11 @@ select quant from Survey;
 
 We can eliminate the redundant output
 to make the result more readable
-by adding the `distinct` keyword
+by adding the `DISTINCT` keyword
 to our query:
 
 ~~~ {.sql}
-select distinct quant from Survey;
+SELECT DISTINCT quant FROM Survey;
 ~~~
 
 |quant|
@@ -64,7 +64,7 @@ both the survey site ID and the quantity measured --- then
 the distinct pairs of values are returned:
 
 ~~~ {.sql}
-select distinct taken, quant from Survey;
+SELECT DISTINCT taken, quant FROM Survey;
 ~~~
 
 |taken|quant|
@@ -101,10 +101,10 @@ This means that query results aren't necessarily sorted,
 and even if they are,
 we often want to sort them in a different way,
 e.g., by the name of the project instead of by the name of the scientist.
-We can do this in SQL by adding an `order by` clause to our query:
+We can do this in SQL by adding an `ORDER BY` clause to our query:
 
 ~~~ {.sql}
-select * from Person order by ident;
+SELECT * FROM Person ORDER BY ident;
 ~~~
 
 |ident  |personal |family  |
@@ -119,10 +119,10 @@ By default,
 results are sorted in ascending order
 (i.e.,
 from least to greatest).
-We can sort in the opposite order using `desc` (for "descending"):
+We can sort in the opposite order using `DESC` (for "descending"):
 
 ~~~ {.sql}
-select * from person order by ident desc;
+SELECT * FROM person ORDER BY ident DESC;
 ~~~
 
 |ident  |personal |family  |
@@ -134,7 +134,7 @@ select * from person order by ident desc;
 |danfort|Frank    |Danforth|
 
 (And if we want to make it clear that we're sorting in ascending order,
-we can use `asc` instead of `desc`.)
+we can use `ASC` instead of `DESC`.)
 
 We can also sort on several fields at once.
 For example,
@@ -143,7 +143,7 @@ and then in descending order by `person`
 within each group of equal `taken` values:
 
 ~~~ {.sql}
-select taken, person from Survey order by taken asc, person desc;
+SELECT taken, person FROM Survey ORDER BY taken ASC, person DESC;
 ~~~
 
 |taken|person|
@@ -173,7 +173,7 @@ select taken, person from Survey order by taken asc, person desc;
 This is easier to understand if we also remove duplicates:
 
 ~~~ {.sql}
-select distinct taken, person from Survey order by taken asc, person desc;
+SELECT DISTINCT taken, person FROM Survey ORDER BY taken ASC, person DESC;
 ~~~
 
 |taken|person|