forked from swcarpentry/sql-novice-survey
-
Notifications
You must be signed in to change notification settings - Fork 0
/
04-calc.html
251 lines (251 loc) · 8.88 KB
/
04-calc.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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
<!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>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<h1 class="title">Databases and SQL</h1>
<h2 class="subtitle">Calculating New Values</h2>
<div id="learning-objectives" class="objectives panel panel-warning">
<div class="panel-heading">
<h2><span class="glyphicon glyphicon-certificate"></span>Learning Objectives</h2>
</div>
<div class="panel-body">
<ul>
<li>Write queries that calculate new values for each selected record.</li>
</ul>
</div>
</div>
<p>After carefully re-reading the expedition logs, we realize that the radiation measurements they report may need to be corrected upward by 5%. Rather than modifying the stored data, we can do this calculation on the fly as part of our query:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fl">1.05</span> * reading <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="st">'rad'</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">1.05 * reading</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">10.311</td>
</tr>
<tr class="even">
<td align="left">8.19</td>
</tr>
<tr class="odd">
<td align="left">8.8305</td>
</tr>
<tr class="even">
<td align="left">7.581</td>
</tr>
<tr class="odd">
<td align="left">4.5675</td>
</tr>
<tr class="even">
<td align="left">2.2995</td>
</tr>
<tr class="odd">
<td align="left">1.533</td>
</tr>
<tr class="even">
<td align="left">11.8125</td>
</tr>
</tbody>
</table>
<p>When we run the query, the expression <code>1.05 * reading</code> is evaluated for each row. Expressions can use any of the fields, all of usual arithmetic operators, and a variety of common functions. (Exactly which ones depends on which database manager is being used.) For example, we can convert temperature readings from Fahrenheit to Celsius and round to two decimal places:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> taken, <span class="fu">round</span>(<span class="dv">5</span>*(reading<span class="dv">-32</span>)/<span class="dv">9</span>, <span class="dv">2</span>) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="st">'temp'</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">taken</th>
<th align="left">round(5*(reading-32)/9, 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">734</td>
<td align="left">-29.72</td>
</tr>
<tr class="even">
<td align="left">735</td>
<td align="left">-32.22</td>
</tr>
<tr class="odd">
<td align="left">751</td>
<td align="left">-28.06</td>
</tr>
<tr class="even">
<td align="left">752</td>
<td align="left">-26.67</td>
</tr>
</tbody>
</table>
<p>We can also combine values from different fields, for example by using the string concatenation operator <code>||</code>:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> personal || <span class="st">' '</span> || family <span class="kw">FROM</span> Person;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">personal</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">William Dyer</td>
</tr>
<tr class="even">
<td align="left">Frank Pabodie</td>
</tr>
<tr class="odd">
<td align="left">Anderson Lake</td>
</tr>
<tr class="even">
<td align="left">Valentina Roerich</td>
</tr>
<tr class="odd">
<td align="left">Frank Danforth</td>
</tr>
</tbody>
</table>
<div id="fixing-salinity-readings" class="challenge panel panel-success">
<div class="panel-heading">
<h2><span class="glyphicon glyphicon-pencil"></span>Fixing Salinity Readings</h2>
</div>
<div class="panel-body">
<p>After further reading, we realize that Valentina Roerich was reporting salinity as percentages. Write a query that returns all of her salinity measurements from the <code>Survey</code> table with the values divided by 100.</p>
</div>
</div>
<div id="unions" class="challenge panel panel-success">
<div class="panel-heading">
<h2><span class="glyphicon glyphicon-pencil"></span>Unions</h2>
</div>
<div class="panel-body">
<p>The <code>UNION</code> operator combines the results of two queries:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> * <span class="kw">FROM</span> Person <span class="kw">WHERE</span> ident=<span class="st">'dyer'</span> <span class="kw">UNION</span> <span class="kw">SELECT</span> * <span class="kw">FROM</span> Person <span class="kw">WHERE</span> ident=<span class="st">'roe'</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">ident</th>
<th align="left">personal</th>
<th align="left">family</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">dyer</td>
<td align="left">William</td>
<td align="left">Dyer</td>
</tr>
<tr class="even">
<td align="left">roe</td>
<td align="left">Valentina</td>
<td align="left">Roerich</td>
</tr>
</tbody>
</table>
<p>Use <code>UNION</code> to create a consolidated list of salinity measurements in which Roerich's, and only Roerich's, have been corrected as described in the previous challenge. The output should be something like:</p>
<table>
<thead>
<tr class="header">
<th align="left">taken</th>
<th align="left">reading</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">619</td>
<td align="left">0.13</td>
</tr>
<tr class="even">
<td align="left">622</td>
<td align="left">0.09</td>
</tr>
<tr class="odd">
<td align="left">734</td>
<td align="left">0.05</td>
</tr>
<tr class="even">
<td align="left">751</td>
<td align="left">0.1</td>
</tr>
<tr class="odd">
<td align="left">752</td>
<td align="left">0.09</td>
</tr>
<tr class="even">
<td align="left">752</td>
<td align="left">0.416</td>
</tr>
<tr class="odd">
<td align="left">837</td>
<td align="left">0.21</td>
</tr>
<tr class="even">
<td align="left">837</td>
<td align="left">0.225</td>
</tr>
</tbody>
</table>
</div>
</div>
<div id="selecting-major-site-identifiers" class="challenge panel panel-success">
<div class="panel-heading">
<h2><span class="glyphicon glyphicon-pencil"></span>Selecting Major Site Identifiers</h2>
</div>
<div class="panel-body">
<p>The site identifiers in the <code>Visited</code> table have two parts separated by a '-':</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="kw">DISTINCT</span> site <span class="kw">FROM</span> Visited;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">site</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DR-1</td>
</tr>
<tr class="even">
<td align="left">DR-3</td>
</tr>
<tr class="odd">
<td align="left">MSK-4</td>
</tr>
</tbody>
</table>
<p>Some major site identifiers are two letters long and some are three. The "in string" function <code>instr(X, Y)</code> returns the 1-based index of the first occurrence of string Y in string X, or 0 if Y does not exist in X. The substring function <code>substr(X, I)</code> returns the substring of X starting at index I. Use these two functions to produce a list of unique major site identifiers. (For this data, the list should contain only "DR" and "MSK").</p>
</div>
</div>
</div>
</div>
<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>