forked from swcarpentry/sql-novice-survey
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdiscussion.html
85 lines (85 loc) · 4.71 KB
/
discussion.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="generator" content="pandoc">
<title>Software Carpentry: Databases and SQL</title>
<link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="css/bootstrap/bootstrap.css" />
<link rel="stylesheet" type="text/css" href="css/bootstrap/bootstrap-theme.css" />
<link rel="stylesheet" type="text/css" href="css/swc.css" />
<link rel="alternate" type="application/rss+xml" title="Software Carpentry Blog" href="http://software-carpentry.org/feed.xml"/>
<meta charset="UTF-8" />
<!-- HTML5 shim, for IE6-8 support of HTML5 elements -->
<!--[if lt IE 9]>
<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
</head>
<body class="lesson">
<div class="container card">
<div class="banner">
<a href="http://software-carpentry.org" title="Software Carpentry">
<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">
<a href="index.html"><h1 class="title">Databases and SQL</h1></a>
<h2 class="subtitle">Discussion</h2>
<h2 id="setup">Setup</h2>
<p>For the SQL lessons, we will use the <a href="https://www.sqlite.org/">SQLite</a> relational database management system, which comes pre-installed on most operating systems. While these instructions are specific to SQLite, most other database management systems (e.g. MySQL, Oracle, PostGreSQL) have similar functions for loading data and performing basic operations.</p>
<p>First, download and install SQLite if it is not already installed on your operating system:</p>
<ul>
<li>Windows: Download the <a href="http://www.sqlite.org/download.html">SQLite program</a>.</li>
<li>Mac OS X: <code>sqlite3</code> comes pre-installed on Mac OS X.</li>
<li>Linux: <code>sqlite3</code> comes pre-installed on Linux.</li>
</ul>
<p>Create a directory where you will carry out the exercises for this lesson, and change to it using the <code>cd</code> command. Download the file <a href="http://files.software-carpentry.org/survey.db">survey.db</a> into this directory.</p>
<pre><code>$ mkdir swc_sql
$ cd swc_sql
$ wget http://files.software-carpentry.org/survey.db</code></pre>
<p>First, load the example database into SQLite. On the shell command line, type</p>
<pre><code>sqlite3 survey.db</code></pre>
<p>This command instructs SQLite to load the database in the <code>survey.db</code> file.</p>
<p>You should see something like the following.</p>
<pre><code>SQLite version 3.8.8 2015-01-16 12:08:06
Enter ".help" for usage hints.
sqlite></code></pre>
<p>For a list of useful system commands, enter <code>.help</code>.</p>
<p>All SQLite-specific commands are prefixed with a . to distinguish them from SQL commands. Type <code>.tables</code> to list the tables in the database.</p>
<pre><code>sqlite> .tables
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> 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. First, set the output mode to display left-aligned columns. Then turn on the display of column headers.</p>
<pre><code>sqlite> .mode column
sqlite> .header on
sqlite> select * from Site;
name lat long
---------- ---------- ----------
DR-1 -49.85 -128.57
DR-3 -47.15 -126.72
MSK-4 -48.87 -123.4</code></pre>
<p>To exit SQLite and return to the shell command line, you can use either <code>.quit</code> or <code>.exit</code>.</p>
</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>
<a class="label swc-blue-bg" href="mailto:admin@software-carpentry.org">Contact</a>
<a class="label swc-blue-bg" href="LICENSE.html">License</a>
</div>
</div>
<!-- Javascript placed at the end of the document so the pages load faster -->
<script src="http://software-carpentry.org/v5/js/jquery-1.9.1.min.js"></script>
<script src="css/bootstrap/bootstrap-js/bootstrap.js"></script>
</body>
</html>