Skip to content

Commit

Permalink
Regenerate html
Browse files Browse the repository at this point in the history
abbycabs committed Apr 30, 2015
1 parent 767ea71 commit 287a009
Showing 16 changed files with 181 additions and 153 deletions.
35 changes: 18 additions & 17 deletions 00-setup.html
Original file line number Diff line number Diff line change
@@ -23,6 +23,7 @@
<img alt="Software Carpentry banner" src="img/software-carpentry-banner.png" />
</a>
</div>
<article>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<h1 class="title">Databases and SQL</h1>
@@ -41,16 +42,19 @@ <h3 id="testing-the-installation">Testing the installation</h3>
<h4 id="command-line">Command line</h4>
<p>Type</p>
<pre><code>sqlite3 survey.db</code></pre>
<p>Then type the SQLite command <code>.tables</code> to list the tables in the database, and an SQL <code>SELECT</code> command. You should see something like the following.</p>
<p>Then type the SQLite command <code>.tables</code> to list the tables in the database. You should see something like the following.</p>
<pre><code>SQLite version 3.8.8 2015-01-16 12:08:06
Enter &quot;.help&quot; for usage hints.
sqlite&gt; .tables
Person Site Survey Visited
sqlite&gt; select * from Site;
Person Site Survey Visited</code></pre>
<p>Type the following SQL <code>SELECT</code> command. This <code>SELECT</code> statement selects all (*) rows from the Site table.</p>
<p><code>select * from Site;</code></p>
<p>Complete your SQL statement with a semicolon.</p>
<pre><code>sqlite&gt; select * from Site;
DR-1|-49.85|-128.57
DR-3|-47.15|-126.72
MSK-4|-48.87|-123.4</code></pre>
<p>You can change some SQLite settings to make the output easier to read:</p>
<p>You can change some SQLite settings to make the output easier to read. First, set the output mode to display left-aligned columns. Then turn on the display of column headers.</p>
<pre><code>sqlite&gt; .mode column
sqlite&gt; .header on
sqlite&gt; select * from Site;
@@ -59,21 +63,18 @@ <h4 id="command-line">Command line</h4>
DR-1 -49.85 -128.57
DR-3 -47.15 -126.72
MSK-4 -48.87 -123.4</code></pre>
<h4 id="ipython-notebook">IPython notebook</h4>
<p>Create a new IPython notebook and run</p>
<pre><code>import sqlite3</code></pre>
<p>This should complete without an error.</p>
<p>In another cell, run</p>
<pre><code>%install_ext https://raw.githubusercontent.com/benwaugh/sql-novice-survey/gh-pages/code/sqlitemagic.py</code></pre>
<p>This should give the following output:</p>
<pre><code>Installed sqlitemagic.py. To use it, type:
%load_ext sqlitemagic</code></pre>
<p>Run this command as intructed, and then in a new cell run this:</p>
<pre><code>%%sqlite survey.db
select * from Site</code></pre>
<p>You should see the contents of the <code>Site</code> table.</p>
<h4 id="helpful-commands">Helpful Commands</h4>
<ul>
<li>For a list of useful system commands, enter <code>.help</code></li>
<li>To exit sqlite and return to the shell command line, you can use either
<ul>
<li><code>.quit</code> <em>or</em></li>
<li><code>.exit</code></li>
</ul></li>
</ul>
</div>
</div>
</article>
<div class="footer">
<a class="label swc-blue-bg" href="http://software-carpentry.org">Software Carpentry</a>
<a class="label swc-blue-bg" href="https://github.com/swcarpentry/sql-novice-survey">Source</a>
16 changes: 9 additions & 7 deletions 01-select.html
Original file line number Diff line number Diff line change
@@ -23,6 +23,7 @@
<img alt="Software Carpentry banner" src="img/software-carpentry-banner.png" />
</a>
</div>
<article>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<h1 class="title">Databases and SQL</h1>
@@ -46,10 +47,10 @@ <h2><span class="glyphicon glyphicon-certificate"></span>Learning Objectives</h2
<h2><span class="glyphicon glyphicon-pushpin"></span>Changing database managers</h2>
</div>
<div class="panel-body">
<p>Every database manager --- Oracle, IBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite --- stores data in a different way, so a database created with one cannot be used directly by another. However, every database manager can import and export data in a variety of formats, so it <em>is</em> possible to move information from one to another.</p>
<p>Every database manager Oracle, IBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite stores data in a different way, so a database created with one cannot be used directly by another. However, every database manager can import and export data in a variety of formats, so it <em>is</em> possible to move information from one to another.</p>
</div>
</div>
<p>Queries are written in a language called <a href="reference.html#sql">SQL</a>, which stands for &quot;Structured Query Language&quot;. SQL provides hundreds of different ways to analyze and recombine data; we will only look at a handful, but that handful accounts for most of what scientists do.</p>
<p>Queries are written in a language called <a href="reference.html#sql">SQL</a>, which stands for Structured Query Language. SQL provides hundreds of different ways to analyze and recombine data; we will only look at a handful, but that handful accounts for most of what scientists do.</p>
<p>The tables below show the database we will use in our examples:</p>
<blockquote>
<p><strong>Person</strong>: people who took readings.</p>
@@ -143,7 +144,7 @@ <h2><span class="glyphicon glyphicon-pushpin"></span>Changing database managers<
<tr class="odd">
<td align="left">734</td>
<td align="left">DR-3</td>
<td align="left">1939-01-07</td>
<td align="left">1930-01-07</td>
</tr>
<tr class="even">
<td align="left">735</td>
@@ -314,7 +315,7 @@ <h2><span class="glyphicon glyphicon-pushpin"></span>Changing database managers<
</tbody>
</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>
<p>Notice that three entries one in the <code>Visited</code> table, and two in the <code>Survey</code> table dont contain any actual data, but instead have a special <code>-null-</code> entry: well return to these missing values <a href="05-null.html">later</a>. For now, lets 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>
<table>
<thead>
@@ -346,7 +347,7 @@ <h2><span class="glyphicon glyphicon-pushpin"></span>Changing database managers<
</tr>
</tbody>
</table>
<p>The semi-colon at the end of the query tells the database manager that the query is complete and ready to run. We have written our commands and column names in lower case, and the table name in Title Case, but we don't have to: as the example below shows, SQL is <a href="reference.html#case-insensitive">case insensitive</a>.</p>
<p>The semicolon at the end of the query tells the database manager that the query is complete and ready to run. We have written our commands and column names in lower case, and the table name in Title Case, but we dont have to: as the example below shows, SQL is <a href="reference.html#case-insensitive">case insensitive</a>.</p>
<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>
@@ -378,8 +379,8 @@ <h2><span class="glyphicon glyphicon-pushpin"></span>Changing database managers<
</tr>
</tbody>
</table>
<p>You can use SQL's case insensitivity to your advantage. For instance, some people choose to write SQL keywords (such as <code>SELECT</code> and <code>FROM</code>) in capital letters and <strong>field</strong> and <strong>table</strong> names in lower case. This can make it easier to locate parts of an SQL statement. For instance, you can scan the statement, quickly locate the prominent <code>FROM</code> keyword and know the table name follows. 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>
<p>You can use SQLs case insensitivity to your advantage. For instance, some people choose to write SQL keywords (such as <code>SELECT</code> and <code>FROM</code>) in capital letters and <strong>field</strong> and <strong>table</strong> names in lower case. This can make it easier to locate parts of an SQL statement. For instance, you can scan the statement, quickly locate the prominent <code>FROM</code> keyword and know the table name follows. 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, its important to understand that the rows and columns in a database table arent 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>
<table>
<thead>
@@ -509,6 +510,7 @@ <h2><span class="glyphicon glyphicon-pencil"></span>Query Style</h2>
</div>
</div>
</div>
</article>
<div class="footer">
<a class="label swc-blue-bg" href="http://software-carpentry.org">Software Carpentry</a>
<a class="label swc-blue-bg" href="https://github.com/swcarpentry/sql-novice-survey">Source</a>
12 changes: 7 additions & 5 deletions 02-sort-dup.html
Original file line number Diff line number Diff line change
@@ -23,6 +23,7 @@
<img alt="Software Carpentry banner" src="img/software-carpentry-banner.png" />
</a>
</div>
<article>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<h1 class="title">Databases and SQL</h1>
@@ -132,7 +133,7 @@ <h2><span class="glyphicon glyphicon-certificate"></span>Learning Objectives</h2
</tr>
</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>
<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>
<table>
<thead>
@@ -220,8 +221,8 @@ <h2><span class="glyphicon glyphicon-certificate"></span>Learning Objectives</h2
</tr>
</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>
<p>Notice in both cases that duplicates are removed even if they didnt appear to be adjacent in the database. Again, its important to remember that rows arent actually ordered: theyre just displayed that way.</p>
<p>As we mentioned earlier, database records are not stored in any particular order. This means that query results arent 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>
@@ -259,7 +260,7 @@ <h2><span class="glyphicon glyphicon-certificate"></span>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>
<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 descending):</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>
@@ -297,7 +298,7 @@ <h2><span class="glyphicon glyphicon-certificate"></span>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 were 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>
<table>
@@ -476,6 +477,7 @@ <h2><span class="glyphicon glyphicon-pencil"></span>Displaying Full Names</h2>
</div>
</div>
</div>
</article>
<div class="footer">
<a class="label swc-blue-bg" href="http://software-carpentry.org">Software Carpentry</a>
<a class="label swc-blue-bg" href="https://github.com/swcarpentry/sql-novice-survey">Source</a>
Loading
Oops, something went wrong.

0 comments on commit 287a009

Please sign in to comment.