-
Notifications
You must be signed in to change notification settings - Fork 0
/
reference.html
180 lines (180 loc) · 10.3 KB
/
reference.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
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="generator" content="pandoc">
<title>Software Carpentry: Introduction to 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">
<h1 class="title">Introduction to Databases and SQL</h1>
<h2 class="subtitle">Reference</h2>
<h2 id="selecting-data"><a href="01-select.html">Selecting Data</a></h2>
<ul>
<li>A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records.</li>
<li>A database manager is a program that manipulates information stored in a database.</li>
<li>We write queries in a specialized language called SQL to extract information from databases.</li>
<li>SQL is case-insensitive.</li>
</ul>
<h2 id="sorting-and-removing-duplicates"><a href="02-sort-dup.html">Sorting and Removing Duplicates</a></h2>
<ul>
<li>The records in a database table are not intrinsically ordered: if we want to display them in some order, we must specify that explicitly.</li>
<li>The values in a database are not guaranteed to be unique: if we want to eliminate duplicates, we must specify that explicitly as well.</li>
</ul>
<h2 id="filtering"><a href="03-filter.html">Filtering</a></h2>
<ul>
<li>Use <code>where</code> to filter records according to Boolean conditions.</li>
<li>Filtering is done on whole records, so conditions can use fields that are not actually displayed.</li>
</ul>
<h2 id="calculating-new-values"><a href="04-calc.html">Calculating New Values</a></h2>
<ul>
<li>SQL can perform calculations using the values in a record as part of a query.</li>
</ul>
<h2 id="missing-data"><a href="05-null.html">Missing Data</a></h2>
<ul>
<li>Databases use <code>null</code> to represent missing information.</li>
<li>Any arithmetic or Boolean operation involving <code>null</code> produces <code>null</code> as a result.</li>
<li>The only operators that can safely be used with <code>null</code> are <code>is null</code> and <code>is not null</code>.</li>
</ul>
<h2 id="aggregation"><a href="06-agg.html">Aggregation</a></h2>
<ul>
<li>An aggregation function combines many values to produce a single new value.</li>
<li>Aggregation functions ignore <code>null</code> values.</li>
<li>Aggregation happens after filtering.</li>
</ul>
<h2 id="combining-data"><a href="07-join.html">Combining Data</a></h2>
<ul>
<li>Every fact should be represented in a database exactly once.</li>
<li>A join produces all combinations of records from one table with records from another.</li>
<li>A primary key is a field (or set of fields) whose values uniquely identify the records in a table.</li>
<li>A foreign key is a field (or set of fields) in one table whose values are a primary key in another table.</li>
<li>We can eliminate meaningless combinations of records by matching primary keys and foreign keys between tables.</li>
</ul>
<h2 id="data-hygiene"><a href="08-hygiene.html">Data Hygiene</a></h2>
<ul>
<li>Every value in a database should be atomic.</li>
<li>Every record in a database should have a primary key.</li>
<li>Database entries should not contain redundant information.</li>
</ul>
<h2 id="creating-and-modifying-data"><a href="09-create.html">Creating and Modifying Data</a></h2>
<ul>
<li>Database tables are created using queries that specify their names and the names and properties of their fields.</li>
<li>Records can be inserted, updated, or deleted using queries.</li>
<li>It is simpler and safer to modify data when every record has a unique primary key.</li>
</ul>
<h2 id="programming-with-databases"><a href="10-prog.html">Programming with Databases</a></h2>
<ul>
<li>We usually write database applications in a general-purpose language, and embed SQL queries in it.</li>
<li>To connect to a database, a program must use a library specific to that database manager.</li>
<li>A program may open one or more connections to a single database, and have one or more cursors active in each.</li>
<li>Programs can read query results in batches or all at once.</li>
</ul>
<h2 id="glossary">Glossary</h2>
<dl>
<dt><span id="aggregation-function">aggregation function</span></dt>
<dd>A function that combines multiple values to produce a single new value (e.g. sum, mean, median).
</dd>
<dt><span id="atomic">atomic</span></dt>
<dd>Describes a value <em>not</em> divisible into parts that one might want to work with separately. For example, if one wanted to work with first and last names separately, the values “Ada” and “Lovelace” would be atomic, but the value “Ada Lovelace” would not.
</dd>
<dt><span id="cascading-delete">cascading delete</span></dt>
<dd>An <a href="#sql">SQL</a> constraint requiring that if a given <a href="#record">record</a> is deleted, all records referencing it (via <a href="#foreign-key">foreign key</a>) in other <a href="#table">tables</a> must also be deleted.
</dd>
<dt><span id="case-insensitive">case insensitive</span></dt>
<dd>Treating text as if upper and lower case characters were the same. See also: <a href="#case-sensitive">case sensitive</a>.
</dd>
<dt><span id="case-sensitive">case sensitive</span></dt>
<dd>Treating upper and lower case characters as different. See also: <a href="#case-insensitive">case insensitive</a>.
</dd>
<dt><span id="cross-product">cross product</span></dt>
<dd>A pairing of all elements of one set with all elements of another.
</dd>
<dt><span id="comma-separated-values-(csv)">comma-separated values (CSV)</span></dt>
<dd>A common textual representation for tables in which the values in each row are separated by commas.
</dd>
<dt><span id="cross-product">cross product</span></dt>
<dd>A pairing of all elements of one set with all elements of another.
</dd>
<dt><span id="cursor">cursor</span></dt>
<dd>A pointer into a database that keeps track of outstanding operations.
</dd>
<dt><span id="database-manager">database manager</span></dt>
<dd>A program that manages a database, such as SQLite.
</dd>
<dt><span id="fields">fields</span></dt>
<dd>A set of data values of a particular type, one for each <a href="#record">record</a> in a <a href="#table">table</a>.
</dd>
<dt><span id="filter">filter</span></dt>
<dd>To select only the records that meet certain conditions.
</dd>
<dt><span id="foreign-key">foreign key</span></dt>
<dd>One or more values in a <a href="#table-database">database table</a> that identify a <a href="#record-database">records</a> in another table.
</dd>
<dt><span id="prepared-statement">prepared statement</span></dt>
<dd>A template for an <a href="#sql">SQL</a> query in which some values can be filled in.
</dd>
<dt><span id="primary-key">primary key</span></dt>
<dd>One or more <a href="#field">fields</a> in a <a href="#table">database table</a> whose values are guaranteed to be unique for each <a href="#record">record</a>, i.e., whose values uniquely identify the entry.
</dd>
<dt><span id="query">query</span></dt>
<dd>A database operation that reads values but does not modify anything. Queries are expressed in a special-purpose language called <a href="#sql">SQL</a>.
</dd>
<dt><span id="record">record</span></dt>
<dd>A set of related values making up a single entry in a <a href="#table">database table</a>, typically shown as a row. See also: <a href="#field">field</a>.
</dd>
<dt><span id="referential-integrity">referential integrity</span></dt>
<dd>The internal consistency of values in a database. If an entry in one table contains a <a href="#foreign-key">foreign key</a>, but the corresponding <a href="#record">records</a> don’t exist, referential integrity has been violated.
</dd>
<dt><span id="relational-database">relational database</span></dt>
<dd>A collection of data organized into <a href="#table">tables</a>.
</dd>
<dt><span id="sentinel-value">sentinel value</span></dt>
<dd>A value in a collection that has a special meaning, such as 999 to mean “age unknown”.
</dd>
<dt><span id="sql">SQL</span></dt>
<dd>A special-purpose language for describing operations on <a href="#relational-database">relational databases</a>.
</dd>
<dt><span id="sql-injection-attack">SQL injection attack</span></dt>
<dd>An attack on a program in which the user’s input contains malicious SQL statements. If this text is copied directly into an SQL statement, it will be executed in the database.
</dd>
<dt><span id="table">table</span></dt>
<dd>A set of data in a <a href="#relational-database">relational database</a> organized into a set of <a href="#record">records</a>, each having the same named <a href="#field">fields</a>.
</dd>
<dt><span id="wildcard">wildcard</span></dt>
<dd>A character used in pattern matching. In SQL’s <code>like</code> operator, the wildcard “%” matches zero or more characters, so that <code>%able%</code> matches “fixable” and “tablets”.
</dd>
</dl>
</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:[email protected]">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>